MSSQL server discovery

Discovery identifies and classifies information about MSSQL servers on Windows machines.

A pattern performs the discovery of MSSQL installations. This pattern replaces the Powershell-Windows - MSSQL probe for both upgrades to Jakarta and for new Jakarta instances. If you want to continue to use probes, you can deactivate the Horizontal discovery probe, which launches the pattern, and then activate the necessary probes.

Credentials

Configure these credentials:
Credential type Additional notes
Applicative credentials The credentials need public access 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.

Specify the CI type on the credential as MSFT SQL Instance.

Windows credentials The credentials need access to the following:
  • A database user that can access these MSSQL tables: sys.dm_exec_sessions and sys.dm_os_schedulers, SERVERPROPERTY.
  • The CI Type MSFT SQL Instance.

Classifier, pattern, and probe

Discovery uses these classifiers, probes, and patterns for IIS discovery:
Classifier Trigger probes Pattern
Microsoft SQL Server
  • Horizontal discovery probe: launches patterns
  • Windows - MSSQL*
MSSql DB On Windows Pattern
*Probes inactive on upgrades and new instances.

Verify that the correct pattern is specified in the horizontal pattern probe on the classifier. See Add the Horizontal Pattern probe to a classifier for instructions.

Data collected

The following data is collected in the MSFT SQL Instance [cmdb_ci_db_mssql_instance] table:
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
Install directory* install_directory
CPU sockets* cpu_sockets
Service Pack* service_pack
*These fields are populated by the pattern only.
The following data is collected in the MSFT SQL Catalog [cmdb_ci_db_mssql_catalog] table:
Note: Only the Windows - MSSQL probe, not theMSSql DB On Windows pattern, populates this table.
Label Field name
Name name
Status status
Note: The

For Microsoft SQL2000 servers, Discovery can match instance names to their process ID.

When Software Asset Management is activated, all discovered software is saved in at least two places in the CMDB: in the Software Installation [cmdb_sam_sw_install] table and the tables specific to the application type. A reference field is provided on these application-specific tables to the corresponding record in the Software Installation table.