MSSQL DB discovery using Probes returns incorrect information if there are multiple instances running on same host using same port - Known Error

MSSQL DB discovery using Probes returns incorrect information if there are multiple instances running on same host using same port Description MSSQL DB discovery using Probes returns incorrect database information if there are multiple instances running on same host, using same port, just different IP.

Steps to Reproduce

1) Setup a Windows Server with MSSQL DB. The Windows Server will need to have at least 2 different Network Adapters with 2 different IP's.

2) Setup at least 2 DB Instances with the following structure:

a) Instance 1 - needs to be assigned to a specific IP and setup to a static port (1433) - Make sure to create some uniquely named DB's within this Instance. b) Instance 2 - needs to be assigned to a different IP than Instance 1 and also setup to the same static port (1433) - Also create some DB's in this Instance that have a different name than in Instance 1.

This will help to distinguish these two Instances when being discovered. Some details on how this can be done can be found in the articles below: https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/2493/configuring-sql-server-to-use-multiple-ports/

3) Navigate to Discovery Defintion > Processes and open the "Microsoft SQL Server" record.

4) Under the "Triggers probes" list, check if there is a record here for triggering the "Windows - MSSQL" probe. If not, you will need to add this in this list and set this to "active = true". Also, if there is a record to run a Horizontal Pattern for this process, make sure to set this to "active = false".

5) Add the appropriate credentials to the instance.

6) Once this is setup, run a Discovery against this Server. - Make sure this should properly trigger multiple "Windows - MSSQL" probes (1 for each SQL Instance) and check the results of these probes. - You should see that you get similar results in both of these inputs, where both sets of data is coming from one SQL instance.

Workaround The issue does not occur when using Patterns to discover MSSQL, instead of Probes:

Discovery Definition > CI Classification > All > Microsoft SQL Server > under Triggers probes tab, deactivate all probes, add "Horizontal Pattern" probe, then select Pattern "MSSql DB On Windows".

If you need to stay with probes, use the workaround below:

In Discovery Definition > Probes > Windows - MSSQL > probe parameter: sqlinfo.ps1

Current OOB: ============================== if ($success -ne $true) { if (!$info.errorMessage) { $info.errorMessage = "Unable to connect to MSSQL instance " + $dbInstance } return $info }

$info.serviceName = $server.ServiceName $info.version = $server.VersionString .... ==============================

Modified: ============================== if ($success -ne $true) { if (!$info.errorMessage) { $info.errorMessage = "Unable to connect to MSSQL instance " + $dbInstance } return $info }

##### Adding workaround here if ($server.ServiceName -ne $instanceName) { if (!$info.errorMessage) { $info.errorMessage = "Not connected to correct instance " + $dbInstance } return $info } ##### End of workaround

$info.serviceName = $server.ServiceName $info.version = $server.VersionString .... ==============================

You can also modify the error message if wanted, for example to add a print out of the $server.ServiceName value as well.

NOTE: When using probes, we trigger a probe against each MSSQL instance, but sometimes we may connect to Instance 2 when trying to get information about Instance 1. This workaround is meant to check and make sure that we are connecting to Instance 1 if that is what the specific probe is targeting.