Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Server & Application Monitor (SAM) > AppInsight for SQL requirements and permissions

AppInsight for SQL requirements and permissions

Overview

This article provides information on the requirements and permissions for AppInsight for SQL.

Environment

All SAM versions

Details

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:

Requirements:
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 2008R2

Without SP

SP1

SP2

Microsoft SQL Server 2012

Without SP

SP1

Microsoft SQL Server 2014  
Microsoft SQL Server 2016 (as of SAM 6.3) Without SP

Permissions

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

 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.

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.

The following commands modify the database. Back up your database before running these commands.

The following script will configure permissions for an SQL account:

USE master
GRANT VIEW SERVER STATE TO AppInsightUser
GRANT VIEW ANY DEFINITION 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'
EXECUTE sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame  = ''AppInsightUser'', @name_in_db = ''AppInsightUser'''

 

The following script will configure permissions for an 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'''

 

See the SolarWinds AppInsight Application Guide for more information about setting up AppInsight for SQL. 

 

If the default port for SQL 1433, is not listening on the SQL server, these steps will fail. If you have followed the steps and receive "Test Failed" error, verify that the DEFAULT port for SQL has not been changed.

 

 

Last modified
13:40, 29 Mar 2017

Tags

Classifications

Public