Discovering Microsoft SQL Servers

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.

See also Discoverying MSSQL and Windows Credentials.

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.