Submit a ticketCall us
Home > Success Center > Server & Application Monitor (SAM) > SAM 6.4 Administrator Guide > Additional SAM technology requirements > AppInsight for SQL requirements and permissions

AppInsight for SQL requirements and permissions

 

Updated: 3-9-2017

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

Microsoft SQL Server Version Versions Supported

Microsoft SQL Server 2008

Without SP, SP1, SP2, SP3

Microsoft SQL Server 2008 R2

Without SP, SP1, SP2 SP3

Microsoft SQL Server 2012

Without SP, SP1

Microsoft SQL Server 2014  
Microsoft SQL Server 2016  

AppInsight for SQL data is collected at the same default five minute polling interval as traditional application templates. Following are the requirements and permissions needed for AppInsight for SQL.

Important: 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

The minimum SQL permissions required to use AppInsight for SQL are as follows:

  • Must have administrator permission at the host level.
  • Must be a member of the db_datareader role on the msdb system database.
  • Must have VIEW SERVER STATE permissions.
  • View any definition.
  • Connect permission to Master database.
  • Execute permission on the Xp_readerrorlog stored procedure.
  • Connect permission to the Msdb database.
  • Must be member of db_datareader role in the MSDB database.
  • Connect permission to all databases.

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

  • AppInsight for SQL supports both the SNMP and WMI protocols and uses SQL to gather information about the application. Additional information is available for nodes managed via WMI.
  • Agents do not work with AppInsight for SQL when the SQL server being monitored is in a cluster.
  • SQL clusters cannot be polled with domain credentials via the Orion Agent because Agents do not work with AppInsight for SQL when the SQL server being monitored is in a cluster.

SQL account permissions

Important: This following scripts make changes directly to the database. You should create a database backup before running either of these scripts.

The following script configures permissions for a SQL account:

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

Important: This following scripts make changes directly to the database. You should create a database backup before running either of these scripts.

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

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 this batch mode, see this Microsoft Technet article: https://technet.microsoft.com/en-us/library/cc957131.aspx

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.

If you have issues, see Use a domain user to monitor AppInsight for SQL through an agent.

 
Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.
 
Last modified
15:02, 8 May 2017

Tags

Classifications

Public