Prerequisites 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, all tests should be configured with the credentials of an SQL user with Sysadmin role.

  2. Administrators of some environments may not wish to provide Sysadmin role for an SQL user who is created for monitoring the Microsoft SQL server. In such environments, to monitor a Microsoft SQL server, 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

    • 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
      • 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 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 MSDB.DBO. log_shipping_secondary_databases table

      • select privilege on the MSDB.DBO. 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.

      The above mentioned privilege is not required for Amazon RDS for SQL server.

Enabling SSL Communication on Microsoft SQL Server

By default, eG agent can communicate with Microsoft SQL Server through both encrypted and unencrypted network connections. If you wish to enable Microsoft SQL server to communicate through secure connections, you can configure the SQL server to accept the communications through the encrypted connections. This configuration ensures that all data exchanged between the agent and the SQl server is transmitted through secure, encrypted channels. Follow the steps below to enable SSL encryption on the SQL server.

  • For example, let us discuss the steps for enabling SSL on the Microsoft SQL Server 2019. On the Microsoft SQL 2019 server host, open the Sql Server Configuration Manager by navigating through the Start menu (see Figure 1).

    Figure 1 : Opening the Sql Server Configuration Manager on Microsoft SQL Server

  • The Sql Server Configuration Manager window will then appear as shown in Figure 2.

    Sql Server Configuration Manager Window

    Figure 2 : The Sql Server Configuration Manager window

  • In Figure 2, expand the SQL Server Network Configuration node in the left panel tree of Figure 2 and right-click on the Protocols for MSSQL SERVER sub-node. Then, select the Properties option from the right-click menu that appears (see Figure 3).

    Properties of Protocols of MSSQL SERVER

    Figure 3 : Selecting the properties of Protocols of MSSQL SERVER from right-click menu

  • The Protocols for MSSQLSERVER Properties window will then appear (see Figure 4). To instruct the SQL server to allow only encrypted connections using SSL, you need to set the Force Encryption flag in the Flags tab of this window to Yes. By default, this is set to No.

    Protocols for MSSQLSERVER Properties

    Figure 4 : The Protocols for MSSQLSERVER Properties window

    Setting the Force Encryption flag to Yes enforces that all incoming client connections to the SQL server must use SSL encryption. In this case, the SQL server will automatically negotiate SSL with the client using a certificate installed on the server. If a proper certificate is not available, the SQL server will use a self-signed certificate.

    Protocols for MSSQLSERVER Properties

    Figure 5 : Enabling Force Encryption option

  • Once you enabled the encryption, switch to the Certificates tab that allows you to assign a specific SSL certificate to the SQL Server instance for encrypting client-server communications.

    Certificates tab of Properties window

    Figure 6 : The Certificates tab of the Properties window

The Certificate drop-down lists all certificates installed on the host on which the SQL server is installed. By default, it displays the name of certificate that is currently used for the SQL server. This certificate will be used for encrypting incoming client connections when the Force Encryption flag is enabled. However, you can select another certificate that can be assigned to the SQL server if needed. Once you selected a certificate, restart the SQL Server service for changes to take effect. If no certificate is selected, SQL Server can still encrypt traffic using a self-generated certificate when the Force Encryption flag is enabled.

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.