AppInsight for SQL requirements and permissions

AppInsight for SQL supports the following versions of Microsoft SQL Server:

  • Microsoft SQL Server 2008, SP1, SP2, SP3
  • Microsoft SQL Server 2008 R2, SP1, SP2 SP3
  • Microsoft SQL Server 2012 SP1
  • Microsoft SQL Server 2014
  • Microsoft SQL Server 2014 SP1
  • Microsoft SQL Server 2014 SP2
  • Microsoft SQL Server 2016
  • Microsoft SQL Server 2016 SP1
  • Microsoft SQL Server 2017 (Windows only)

AppInsight for SQL does not require named-pipes. However, it does require TCP. For example, SAM uses TCP detection during discovery. You may receive an error message pertaining to "named-pipes." This is the result of the last client protocol that is tried during connection to the SQL server.

AppInsight for SQL permissions

Following are required permissions needed for AppInsight for SQL. See also SAM port requirements.

  • Administrator permission at the host level.
  • Be a member of the db_datareader role in the msdb database
  • VIEW SERVER STATE permissions
  • View any definition
  • Connect permission to all databases, including Master and msdb
  • Execute permission on the Xp_readerrorlog stored procedure

If utilizing a domain user for AppInsight for SQL, the domain user must be a member of the SQL server's local admin group.

Review the following information regarding monitoring SQL servers with AppInsight for SQL:

  • AppInsight for SQL supports SNMP and WMI protocols and uses SQL to gather application data. Additional data is available for nodes managed via WMI.
  • Agents do not work with AppInsight for SQL if the SQL server is monitored in a cluster.
  • SQL clusters cannot be polled with domain credentials via the Orion agent because agents do not work with AppInsight for SQL if the SQL server is monitored in a cluster.

SQL account permissions

The following script configures permissions for a SQL account. You must connect to the SQL database server as "sa" or equivalent to create an account.

This script makes changes directly to the database. Create a database backup before running either script.

USE master
GRANT VIEW SERVER STATE TO AppInsightUser
GRANT VIEW ANY DEFINITION TO AppInsightUser
GRANT VIEW ANY DATABASE TO AppInsightUser
EXEC sp_adduser @loginame = 'AppInsightUser' ,@name_in_db = 'AppInsightUser'
GRANT EXECUTE ON xp_readerrorlog TO AppInsightUser
USE msdb
EXEC sp_adduser @loginame = 'AppInsightUser' ,@name_in_db = 'AppInsightUser'
EXEC sp_addrolemember N'db_datareader', N'AppInsightUser'

Windows Authentication

The following script configures permissions for a SQL account with Windows Authentication:

This script makes changes directly to the database. Create a database backup before running either script.

USE master
GRANT VIEW SERVER STATE TO "Domain\AppInsightUser"
GRANT VIEW ANY DEFINITION TO "Domain\AppInsightUser"
EXEC sp_adduser @loginame = 'Domain\AppInsightUser' ,@name_in_db = 'Domain\AppInsightUser'
GRANT EXECUTE ON xp_readerrorlog TO "Domain\AppInsightUser"
USE msdb
EXEC sp_adduser @loginame = 'Domain\AppInsightUser' ,@name_in_db = 'Domain\AppInsightUser'
EXEC sp_addrolemember N'db_datareader', N'Domain\AppInsightUser'
EXECUTE sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame  = ''Domain\AppInsightUser'', @name_in_db = ''Domain\AppInsightUser''' 

Domain account with Orion agent

To use a domain account with an Orion agent, the domain account needs to have “Log on as a batch job” policy enabled for the default batch execution mode. Set this permission either locally on the monitored SQL server or as a domain policy (which enforces the policy to all machines within the domain). For details on batch mode, see https://technet.microsoft.com/en-us/library/cc957131.aspx (© 2018 Microsoft Corp.)

This policy is only enabled for a LocalSystem account by default and explicitly needs to be added for the domain account.

This user right is defined in the Default Domain Controller Group Policy object (GPO) and in the local security policy of workstations and servers. The location for the policy is Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment.

See also Use a domain user to monitor AppInsight for SQL through an agent.