Monitor Microsoft SQL Server

You can monitor Microsoft SQL Server by using an existing database account or by creating a dedicated monitoring account with the required privileges. The supported authentication methods and setup steps depend on the database platform and the operating system on which the Database Agent runs.

Monitor the Microsoft SQL Server Cluster

Database Visibility supports Always On cluster discovery for Microsoft SQL Server 2012 and later. You can monitor the nodes in an availability group by using the availability group listener.

  • Enable the dbagent.mssql.cluster.discovery.enabled property at the Controller or agent level. This property is disabled by default.

  • Specify the hostname and port of the availability group listener in the collector configuration so that the cluster nodes can be discovered automatically.

Note:

Read-only routing is supported for Always On availability groups.

Authentication Methods

You can monitor the database by using one of the following authentication methods:

  • Windows authentication: Supported for Microsoft SQL Server when the Database Agent runs on Windows. Windows authentication is not supported for Azure SQL Database.

  • SQL Server authentication: Supported when the Database Agent runs on Windows or Linux. For Azure SQL Database, use the Azure SQL Database-specific configuration and permission guidance.

  • Microsoft Entra password authentication: Supported for applicable SQL Server collector configurations. This method was formerly called Azure Active Directory Password authentication.

  • Microsoft Entra integrated authentication: Supported for applicable SQL Server and Azure SQL Managed Instance configurations. This method was formerly called Azure Active Directory Integrated authentication.

Platform scope: Azure SQL Database and Azure SQL Managed Instance use different configuration and permission procedures. Do not apply the Microsoft SQL Server Windows-authentication or server-level-permission procedures in this page to Azure SQL Database. When monitoring Azure SQL Managed Instance with Microsoft Entra integrated authentication, install the Microsoft ODBC driver and SQL Server Management Studio (SSMS) on the Database Agent host. The MSSQL JDBC driver uses the adal.dll file included with SSMS.

Before you Begin

Windows authentication for Microsoft SQL Server

Use this procedure only for a Microsoft SQL Server collector when the Database Agent runs on Windows. Do not use it for Azure SQL Database.

  1. Select the Windows Authentication option when you create the collector.

  2. Do not enter a username or password in the database connection details when you use Windows authentication.

  3. Specify the path to the Database Agent authentication library when you start the agent:

    Agent platform

    Path

    Windows 64-bit

    java -Djava.library.path="C:\dbagent_install_dir\auth\x64" -jar db-agent.jar

    Windows 32-bit

    java -Djava.library.path="C:\dbagent_install_dir\auth\x86" -jar db-agent.jar

Ensure that the Windows account has the privileges required to authenticate to the database server and start the Database Agent. If the Database Agent runs as a Windows service, configure the service to log on with the Windows account that has SQL Server access.

Microsoft Entra authentication

Before you configure a collector, verify the following prerequisites:

  • The Microsoft Entra user or group has the permissions required for the target database platform.

  • The Database Agent host can reach the target database and the Microsoft Entra endpoints through the required firewall or proxy path.

  • Your organization's Microsoft Entra Conditional Access policies allow the authentication flow used by the collector. Work with your tenant administrator when a policy blocks sign-in; do not bypass security controls or add a blanket exclusion.

Configure Microsoft Entra password authentication

Microsoft Entra password authentication uses the Microsoft Entra username and password supplied in the collector connection details. Use an account that is approved for this noninteractive monitoring use case and that has the required database permissions.

  1. Select the applicable Microsoft SQL Server database type and Database Agent.

  2. Enter the collector name, hostname or IP address, listener port, and Microsoft Entra username and password in the standard Connection Details fields.

  3. In Advanced Options > Connection Properties, add the following property:

    Property

    Value

    authentication

    ActiveDirectoryPassword

Depending on the Database Agent version and collector form, you can provide the same JDBC property in Connection Details > Custom JDBC Connection String instead:

JDBC 
jdbc:sqlserver://<hostname>:1433;databaseName=<database>;encrypt=true;authentication=ActiveDirectoryPassword;

Use the entry point provided by your version of the collector form. Do not add conflicting values for the same property.

Configure Microsoft Entra integrated authentication

Use Microsoft Entra integrated authentication only for a database platform and Database Agent configuration that supports it. Follow the platform-specific driver and library requirements for your version. For Azure SQL Managed Instance, verify that the ODBC driver, SSMS, and adal.dll are installed on the Database Agent host and that the Database Agent service account can access the required libraries.

Database Permissions

Microsoft SQL Server permissions

The following procedure applies to a dedicated SQL-authenticated monitoring login for supported Microsoft SQL Server deployments. Replace AppD_User with the login name that you create.

  1. Create a login for the Database Visibility collector by using SQL Server Management Studio.


    Screenshot for creating a login for the Database Visibility collector.

  2. Map the login to the master and msdb databases. These mappings are required for the SQL Server monitoring views described by this procedure. Additional database mappings might be required for object information in the Object Browser.


    Screenshot for mapping the login to the master and msdb databases.

  3. Grant the following privileges:

SQL 
USE master;
GRANT VIEW ANY DATABASE TO AppD_User;
GRANT VIEW ANY DEFINITION TO AppD_User;
GRANT VIEW SERVER STATE TO AppD_User;
GRANT SELECT ON [sys].[master_files] TO AppD_User;
Important:

These server-level commands are for Microsoft SQL Server deployments. They are not Azure SQL Database Microsoft Entra setup steps. For Azure SQL Database, use the Azure-specific minimum-permissions procedure for the target database and authentication method.

Microsoft Entra users and Azure SQL Database

For Azure SQL Database, create or use the Microsoft Entra user or group on the target database and grant the permissions required by the Azure SQL Database collector. Follow the Azure SQL Database-specific minimum-permissions guidance. Do not create a SQL Server login or map msdb unless the target platform's documentation explicitly requires it.

Optional object permissions for Microsoft SQL Server

The following permissions are required only for the optional screens in the Database Visibility user interface. Grant them only when the corresponding screen is needed.

Screen

Object permissions

Object Browser > Users

GRANT EXECUTE ON sp_helplogins TO AppD_User;

Note: A security administrator role is required.

Object Browser > Storage

Map the user to the databases of interest to view object storage metadata.

Note: The public role is required.

Object Browser > Job Status
SQL 
USE msdb;
GRANT SELECT ON dbo.sysjobsteps TO AppD_User;
GRANT SELECT ON dbo.sysjobs TO AppD_User;
GRANT SELECT ON dbo.sysjobhistory TO AppD_User;

Object Browser > Error Log

For versions 2005 and earlier: GRANT EXECUTE ON sp_readErrorLog TO AppD_User;

For later versions: GRANT EXECUTE ON xp_readerrorlog TO AppD_User;

Note: A security administrator role is required.

Object Browser > Database

Map the user to the databases of interest to view object and table/view metadata.

Note: The public role is required.

Troubleshoot Microsoft Entra authentication

  • Sign-in is blocked by Conditional Access: Check the Microsoft Entra sign-in logs for the collector attempt and identify the policy or condition that blocked token issuance. Ask the tenant administrator to approve a supported flow for the monitoring account.

  • The collector cannot reach the database or Microsoft Entra: Check DNS, firewall rules, proxy settings, outbound HTTPS connectivity, and the database listener or port from the Database Agent host.

  • Authentication succeeds but monitoring fails: Verify that the Microsoft Entra user or group has the required permissions on the target database and that the permissions match the platform-specific procedure.

  • The authentication property is ignored: Verify the exact property name authentication and value ActiveDirectoryPassword, and confirm that the property was entered through the supported Connection Properties or Custom JDBC Connection String field for your version.

  • Integrated authentication fails on Azure SQL Managed Instance: Verify the ODBC driver, SSMS, adal.dll, Java library path, and Database Agent service-account access on the agent host.