MSSQL DB discovery Issues - Servers are discovered but No DBs getting discovered

Gokul Explorer
Tera Contributor

‎05-14-2025 11:54 AM

Executing WMI query on host: 10.1.75.XXX with namespace: root/cimv2 and query: SELECT name FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases
Error during execution of Windows command: executeQuery -Namespace root/cimv2 -Query "SELECT name FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases" due to com.snc.automation_common.integration.exceptions.InvalidCommandException: executeQuery : Could not get query SELECT name FROM Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases on namespace=root/cimv2. Original Exception: Invalid query
At line:1 char:5


Overview:

In our environment, multiple database servers share identical access credentials, and no firewall restrictions block access or connections. The WMI port (135) is open on all servers. While Windows Server and Microsoft SQL Server instances are consistently discovered and updated, certain databases on specific servers are not being discovered.

 

Initial Findings:

During investigation, we identified that the SQL Server "Auto Close" feature was enabled for some databases. When enabled, this feature causes databases to shut down when not in use, preventing discovery. Disabling "Auto Close" resolved the discovery issue for those databases.

 

Persistence Issue:

However, for a subset of servers, databases are still not being discovered despite the following:

"Auto Close" is confirmed to be disabled.

Windows Server and SQL Server instance Configuration Items (CIs) are successfully discovered and updated.

No access, credential, or firewall-related issues are present.

WMI port (135) remains open.

 

Can anyone from the Community, help me with this ?

 

Your assistance will greatly appreciated!!

 

Regards,

Gokul Subramaniam

Labels:
0 Helpfuls
  • 471 Views
2 REPLIES 2

sachin45
ServiceNow Employee
ServiceNow Employee

‎05-21-2025 05:45 AM

Hi Gokul ,  

To perform SQL discovery, you'll need both of the following:

  1. Windows Server (admin) credentials .

  2. SQL credentials that grant the Discovery probes permission to query the SQL level for catalog and instance information. A Windows admin credential alone won't be sufficient to access SQL-specific content."

Refer to below KB and docs page to get more info : 
 
Not sure if this will help but try following steps and see if this resolves the issue :— 

  • Test WMI Query: Manually run the WMI query Get-WmiObject -Namespace "root/cimv2" -Class Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases on the affected server. If it fails, the issue is with WMI or SQL Server counters.

  • Collaborate with your DB admins to see if they can Verify WMI Counters: they will be able to check it from their side by running few commands and reregister that.
  • Rebuild WMI Repository: If WMI queries fail, try rebuilding the repository on affected servers . 

  • SQL Server Permissions: Ensure that the SQL Server service account has permission to expose WMI performance data, and check that the correct instance is being queried (especially for named instances).

  • Check Memory & SQL Server Configurations: High memory usage or misconfigurations in SQL Server could affect WMI queries. Ensure sufficient resources are available and check instance-specific settings.

  • Review Discovery Logs: Examine the Discovery logs for detailed error messages related to MSSQL discovery, and ensure the correct Discovery pattern is applied.

I would also recommend creating a Case with support  ..
0 Helpfuls

Gokul Explorer
Tera Contributor
In response to sachin45

‎05-21-2025 06:15 AM

Hi @sachin45 

 

Thanks for sharing. In our environment, all SQL servers have the same permissions and passwords. While most servers and databases are successfully discovered, a few remain undiscovered. Interestingly, even for this issue, the servers and MSFT SQL instance CIs are getting updated when running discovery, but the databases alone are not being detected.

I'm receiving an "Invalid query" error. To resolve this, modifying the OOB pattern might fix the issue, but it could also introduce problems for others. I'll check with Support by creating a case.

0 Helpfuls