Microsoft SQL Server sensor

The Microsoft SQL Server sensor discovers Microsoft SQL Servers. You can use the sensor in the script-based mode.

Sensor name that is used in the GUI and logs

SqlServerSensor

Model objects created

The sensor creates the following model objects:

  • db.mssql.SqlServer
  • db.mssql.SqlServerConfig
  • db.mssql.SqlServerDatabase
  • db.mssql.SqlServerDataFile
  • db.mssql.SqlServerModule
  • db.mssql.SqlServerProcess

Prerequisites

You must complete the following prerequisite tasks to successfully discover Microsoft SQL Servers.
Note: The following prerequisites are the same for regular and script-based discovery.
Account configuration
You can run the discovery in the Windows or SQL authentication mode.
Windows authentication mode
  • Create a new login on SQL Server for Windows domain account, which is used for the discovery of Windows operating system. The discovery is then run in the Windows authentication mode.
  • Map the Windows domain account to the login that you created in the previous step.
  • On the SQL master database, assign the following roles and permissions to the login that you created for Windows domain account:
    • public - open the Login Properties window, go to the User Mapping page, and select public database role.
    • db_datareader - open the Login Properties window, go to the User Mapping page, and select db_datareader database role.
    • Connect SQL - open the Login Properties window, go to the Securables page, and grant the Connect SQL permission.
    • View any definition - open the Login Properties window, go to the Securables page, and grant the View any definition permission.
    These roles and permissions are required to access the following tables:
    • sysdatabases
    • syscurconfigs
    • sysprocesses
    • sysobjects
    • syscolumns
  • Open the Login Properties window and go to the Status page. In the setting section, for the Permission to connect to database engine setting, select Grant, and for the Login setting, select Enabled.
  • Ensure that the Local Administrators group has SQL access (part of the SQL authorization and configuration).
SQL authentication mode
  • Create a new login on the SQL Server. Select the SQL Server authentication option. The discovery is then run in the SQL authentication mode.
  • On the SQL master database, assign the following roles and permissions to the login that you created for SQL domain account:
    • public - open the Login Properties window, go to the User Mapping page, and select public database role.
    • db_datareader - open the Login Properties window, go to the User Mapping page, and select db_datareader database role.
    • Connect SQL - open the Login Properties window, go to the Securables page, and grant the Connect SQL permission.
  • Open the Login Properties window and go to the Status page. In the setting section, for the Permission to connect to database engine setting, select Grant, and for the Login setting, select Enabled.
Network requirements
  • Depending on the operating system, Level 2 Network Requirements must be met. The application is discovered by using an operating system account, therefore Agile Service Manager Level 2 discovery of the server, where the application is installed must be successful.
  • Microsoft SQL listening ports must be opened on firewalls between Agile Service Manager Windows gateways and the servers, where Microsoft SQL is installed.
Script-based discovery requirements
In the script-based discovery mode, install either the sqlps Windows PowerShell module, or SqlServerProviderSnapin100 and SqlServerCmdletSnapin100 Windows PowerShell snap-ins.

Limitations

The transactional dependencies between supported application servers, IBM® WebSphere®, JBoss, Oracle Weblogic, and the SQL Server are only created for the listen port that is stored in the SQL Server's primarySap attribute.

If the SQL Server uses general TCP/IP configuration, the ListenAll flag is set to true, then the first static port is taken as its primarySAP. The rest of the ports are not captured and thus some of the dependencies cannot be created.

If the SQL Server uses specific TCP/IP configuration for each IP interface, the ListenAll flag is set to false, then the first non-loopback, Active, and Enabled IP's first static port is taken as the SQL Server primarySAP. The rest of the ports and the ports that are configured for other IP interfaces are not captured. Thus some of the dependencies cannot be created.

If the SQL Server uses only dynamic port configuration, the current runtime listen port, which is subject to change, is not stored in the primarySAP attribute. Instead, a dynamicPortAllocation flag is set to true to indicate it.

The dependencies that are based on the SQL Server instance name, instead of its listening port, are always created.

The script-based discovery mode of the Microsoft SQL Server sensor relies on the sqlps module, which is available in Microsoft SQL Server 2008, and later. Therefore, if you want to discover Microsoft SQL Server 2005, you must have other instances like Microsoft SQL Server 2008, 2008 R2, or 2012 installed as well.