Pre-requisites for Monitoring Microsoft SQL server

A set of pre-requisites is needed to be fulfilled to configure the Microsoft SQL server/SQL Managed Instances hosted on Microsoft Azure and AWS to work with the eG agent. These requirements are explained in the following sections.

User Privileges Required for Monitoring Microsoft SQL server

  1. To monitor a Microsoft SQL server 7.0/2000, all tests should be configured with the credentials of an SQL user with Sysadmin role.

  2. To monitor a Microsoft SQL server of any other version, all tests should be configured with the credentials of an SQL user with the following privileges:

    • connect sql
    • view any database
    • view any definition
    • view server state
    • PUBLIC role
    • select privilege on the MSDB.DBO.SYSJOBS table
    • select privilege on the MSDB.DBO.SYSJOBSCHEDULES table
    • select privilege on the MSDB.DBO.SYSJOBHISTORY table

    • For the SQL Database Space test to run, the user configured for that test should additionally possess one of the following privileges:

      • The db_datareader privilege on each of the databases to be monitored; (OR)
      • execute permissions for the sp_spaceused stored procedure on each of the databases to be monitored.

    • For the SQL Job Status and SQL Job Details tests to run, the SQL user should be configured with an additional privilege as mentioned below:

      • select privilege on the MSDB.DBO.SYSJOBACTIVITY table
      • select privilege on the MSDB.DBO.SYSSESSIONS table

    • For the SQL Database Space By File Groups test to run, the user configured for that test should additionally possess one of the following privileges:

      • The db_datareader privilege on each of the databases to be monitored; (OR)
      • execute permissions for the sp_spaceused stored procedure on each of the databases to be monitored.

    • For the SQL Log Shipping Status test, the user should additionally possess the following privileges:

      • select privilege on the log_shipping_secondary_databases table

      • select privilege on the log_shipping_primary_database table

    • For the SQL Network test, the user should additionally possess the right to run the query specified against the query parameter of this test.

    • For the SQL Error Log test, the user should additionally possess the following privilege:

      execute permissions for the xp_readerrorlog stored procedure on each of the databases to be monitored.

Configuring the Microsoft SQL Server to Communicate with the eG Manager via HTTP/HTTPS

The eG agent on the Microsoft SQL server communicates with the eG manager via HTTP/HTTPS. However, some Microsoft SQL server installations could, by default, support only TCP/IP connections. Under such circumstances, you might have to reconfigure the SQL installation to additionally support HTTP/HTTPS protocols, so that the eG agent-manager communication is not affected. To ensure this, do the following:

  1. On the Microsoft SQL server host, follow the menu sequence, Start -> Programs -> Microsoft SQL Server -> Client Network Utility, to open the Microsoft SQL server's Client Network Utility (see Figure 1).

    1-final

    Figure 1 : Opening the Client Network Utility

  2. In the General tab of Figure 2 that appears next, check whether the Multiprotocol option is available in the Enable protocols by order list. If not, then select it from the Disabled protocols list and select the Enable button to enable it.

    2-final

    Figure 2 : Enabling Multiprotocol support using the SQL Client Network Utility

  3. Finally, click the Apply and OK buttons in Figure 2 to register the changes.

  4. Next, follow the menu sequence depicted by Figure 3 to open the Microsoft SQL server's Server Network Utility.

    3-final

    Figure 3 : Opening the Server Network Utility

  5. When Figure 4 appears, check whether the Multiprotocol option is available in the Enable protocols by order list. If not, then select it from the Disabled protocols list and select the Enable button to enable it.

    4-final

    Figure 4 : Enabling Multiprotocol support using the SQL Server Network Utility

  6. Finally, click the Apply and ok buttons in Figure 4 to register the changes.

Note:

Before attempting to monitor an instance based server, make sure that the SQL Browser Service is up and running on the Microsoft SQL server.