You can monitor the SQL Server either using an existing user account or by creating a new user account, with relevant privileges.

Monitor the Microsoft SQL Server Cluster

Database Visibility supports the Always On cluster discovery for the Microsoft SQL database. You can monitor all the nodes in the availability group of an MSSQL database server >= 2012.

To enable monitoring of all the nodes, you must enable the dbagent.mssql.cluster.discovery.enabled property either at the Controller level or at the agent level.

This property is disabled by default.

Authentication Methods

You can monitor the SQL server using one of the following authentication methods:

• Windows authenticated account (if the Database Agent is running on Windows).
• SQL Server authenticated account (if the Database Agent is running on Windows or Linux).
• Azure Active Directory Password

• Azure Active Directory Integrated.

  Note: You must install the Microsoft Open Database Connectivity (ODBC) driver to use Azure Active Directory Integrated while monitoring Azure SQL Managed Instance.

Before you Begin

To connect to the SQL Server database using a Windows authenticated account, perform the following:

• Select Windows Authentication checkbox when creating the collector using "Create New Collector" dialog.
  Note: Do not specify the username and password when updating database connection details.
• Specify the path to the Database Agent authentication library as follows:

  Version Details	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 user has appropriate privileges to authenticate the database server and can start the database agent.
• Change the logon credentials of the service to the Windows account with SQL Server access, if using a Windows service to run the Database Agent.

To connect to the SQL Server database using Azure Active Directory Password, add the following property while configuring the Microsoft SQL Server collector:

• Property: authentication
• Value: ActiveDirectoryPassword

You can add the property using one of the following fields:

• Connection Details > Custom JDBC Connection String
• Advanced Options > Connection Properties

Server Level Permissions for SQL Server Logon

To create a new SQL Server user (with minimum permissions required to monitor), perform the following steps:

1. Create a new login for the Splunk AppDynamics SQL Server Database Collector, such as AppD_User , using SQL Server Management Studio (SSMS).

   

2. To map the new user to "master" and "msdb" databases, click "master" and "msdb" under Users mapped to this login.

   

   Note: User mapping to "master" and "msdb" are mandatory for monitoring. To view object information in the Object Browser screens, additional mapping to other databases is required.
3. After creating the login, grant the following privileges to the user by substituting AppD_User with the name you specified on the Login - New window:

   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;

Optional Object Permissions for SQL Server

The following object permissions are required for optional screens within the Database (DB) Visibility user interface:

GRANT execute on sp_helplogins to AppD_User;

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;

GRANT execute on sp_readErrorLog to AppD_User;

GRANT EXECUTE ON xp_readerrorlog