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 2014 SP4
- Microsoft SQL Server 2016
- Microsoft SQL Server 2016 SP1
- Microsoft SQL Server 2017
- Microsoft SQL Server 2019 (Windows only)
- Microsoft SQL Server 2022
Although AppInsight for SQL does not require named pipes, it uses TCP connections to discover SQL Server instances so you may see messages about named pipes that are triggered when SAM connects to SQL Server.
AppInsight for SQL permissions
Following are required permissions for AppInsight for SQL. See also SAM port requirements.
Credentials used for monitoring requirements include:
- 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 permissions
- Connect permission to all databases, including Master and msdb
- Execute permission on the Xp_readerrorlog stored procedure
A domain users must be a member of the SQL server's local admin group.
Note the following details about monitoring:
- AppInsight for SQL supports SNMP and WMI protocols and uses SQL to gather application data. Additional data is available for nodes managed via WMI.
- Administrator rights to SQL servers are needed for configuration, but are not required for polling if you use the optional SolarWinds Platform Agent for Windows. When you deploy agents to target servers, they are automatically registered with the SolarWinds Platform server as managed nodes, polling occurs directly on the target server, and agents transmit encrypted data to the SolarWinds Platform server.
SQL servers in clusters do not support SolarWinds Platform agent monitoring.
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 updates the SolarWinds Platform database. Create a database backup before running it.
Scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.
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 updates the SolarWinds Platform database. Create a database backup before running it.
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 SolarWinds Platform agent
To use a domain account with an SolarWinds Platform 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.
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.