Microsoft SQL server discovery

Discovery uses the SQL SMO object to get information about all Microsoft SQL Server instances running on the same system.

Requirements

The Microsoft SQL Server probe is triggered by a running process that has a command that contains sqlservr.exe.

MID Server Host

  • Install .Net 3.5 and 4 from Microsoft
  • Install the latest version of the Microsoft SQL Server management library (SMO).
    Note: The SMO requires the Common Language Runtime (CLR) library to be installed first. Both libraries can be downloaded from the Microsoft website.
  • Install PowerShell v2.0 and above.

Microsoft SQL Server Host

  • Install the Remote Registry Service on target computers running Microsoft SQL Server.

Credentials

  • Add valid Windows credentials (type=Windows) to the Discovery Credentials table.
  • Ensure credentials have the public access level to the following:
    • The target Windows host.
    • The Microsoft SQL Server instance on the target Windows host. You must add the user to the SQL Server configuration.
    • The MID Server host. The SMO libraries locally impersonate the credentials for the target system prior to connecting to the Microsoft SQL Server. This behavior is enforced by Active Directory. Authentication only succeeds if the domain requirements specified here are met.
    • Role: On MS SQL 2014, the sysadmin role must be assigned to the user to obtain the instance version. The public role does not have permission to retrieve the instance version.
Note: If there are no matching discovery credentials, probes may attempt to connect using the MID Server service credentials. The MID Server service credentials are only valid if the MID Server host is on same domain as the Microsoft SQL Server host.

Domains

  • Install the MID Server host and the Microsoft SQL Server host on the same domain or, if they are on different domains, enable a trust relationship between the domains such that users in the Microsoft SQL Server host domain are trusted by the MID Server host domain.
  • If a domain trust relationship is in place, do not install the MID Server on a domain controller.

Process classifiers

For Powershell version 2 or later, the MID Server must be within the same domain that it is trying to discover.

Discovery collects data from the following versions:
Table 1.
Edition Version name
Microsoft SQL Server
  • 2000
  • 2005
  • 2008
  • 2012
Microsoft SQL Express
  • 2005
  • 2008
  • 2012

Dictionary entries

The running processes of the database (the actual SQL server) is referred to as the database instance. Discovery writes the following information to the MSFT SQL instance [cmdb_ci_db_mssql_instance] table.

Table 2. Dictionary entries
Label Field name
Name Name
Instance instance_name
TCP port tcp_port
Portdynamic port_dynamic
Version name version_name
Edition edition
Version version
Engine edition engine_edition

For SQL2000 servers, Discovery can match instance names to their process ID. The database is referred to as the catalog. The following data is collected in the MSFT SQL Catalog [cmdb_ci_db_mssql_catalog] table:

Label Field name
Name name
Status status
Note: You can find the data for the actual server on which the MSSQL instance and catalogs resides in the Windows computer [cmdb_ci_win_server].