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 classifiersFor 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: 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 1. 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]. Microsoft SQL Server cluster DiscoveryDiscovery identifies Microsoft SQL Server instances that are part of a cluster in the CMDB by the cluster name rather than as individual configuration items (CI).