MSSQL on Windows discovery pattern - Identification sections failing (except the "lightweight" one)

[Miguel Sanchez1]

Hello everyone,

We are having issues discovering some MSSQL instances because only the "Lightweight" identification section is working, which does not pull any information about databases. There are two more identification sections which should be able to pull all the data from the instance, but we are getting the following errors:

• Section "Identification for MS SQL Server" - I'm already checking with the DB owners this one because I think it could be related to a permissions issue when trying to access the namespace "Root\Microsoft\SqlServer". Any additional thoughts for this one?

  Error during execution of Windows command: executeQuery -Namespace Root\Microsoft\SqlServer -Query "SELECT Name FROM __NAMESPACE" due to com.snc.automation_common.integration.exceptions.InvalidCommandException: executeQuery : Could not get query SELECT Name FROM __NAMESPACE on namespace=Root\Microsoft\SqlServer. Original Exception: Invalid namespace

• Section "Identification for MS SQL or TCP with port" - This section fails from the very start while checking a variable called "$isConnection", the step is called "Check Connection" but I can't find where this variable/value is coming from to troubleshoot it. As a result the logs only send the following message:

  Please check cmdb_ci_db_mssql_instance credenditals and run discovery again! Failed Condition(s): [(${isConnection} : value=) EQUALS "false"]

I'm attaching a couple of images to show both scenarios.

As additional note, we don't have an applicative credential and I'm working on also testing this but the thing is that we are successfully discovering several other instances without it. I'm assuming Discovery uses the same Windows credential used to discover the server itself (if anyone can confirm this is correct, it would be very helpful as well).

Thanks a lot in advance for the help!

[SK Chand Basha]

Hi @Miguel Sanchez1 

If it failing at the credentials phase you will need applicative credentials. 

How it working for others? 

1. Windows Authentication Mode in SQL Server:

   • SQL Server can be configured for "Windows Authentication mode" or "SQL Server and Windows Authentication mode" (Mixed Mode).

   • If it's in Windows Authentication mode (or Mixed Mode), Windows users and groups can be granted login rights directly to the SQL Server instance.

2. ServiceNow Pattern Behavior:

   • Modern ServiceNow discovery patterns are intelligent. When they attempt to connect to SQL Server after the initial WMI/port checks, they will often try to use the already-provided Windows credentials (the ones used for WMI) to authenticate to SQL Server.

   • If the Windows account (e.g., DOMAIN\discovery_account) has been added as a login to the SQL Server instance and granted sufficient permissions (like VIEW SERVER STATE, VIEW ANY DEFINITION, and access to sys.databases), ServiceNow can successfully query for database details using these Windows credentials.

   • You wouldn't need to configure a separate "Applicative credential in ServiceNow in this scenario. The "Windows Credential" serves both WMI and SQL authentication