Selva Arun
Mega Sage
Mega Sage

Summary:

This knowledge article documents my complex ServiceNow SQL Server discovery troubleshooting experience that initially appeared to be a WMI namespace compatibility issue but was actually caused

by SQL Server Express using dynamic ports instead of the standard port 1433. The discovery had previously worked but suddenly began failing, leading to hours of misdirected troubleshooting efforts.

Note: I will be uploading a detailed video demonstration of this entire troubleshooting process on NowDivas - please watch out for it!

Problem Statement

Initial Symptoms:

  • SQL Server discovery pattern failing at step 70.3: "No MSSQL Databases returned from the WMI query, terminating the pattern"
  • Later failing at step 11: "Failed to identify the MSSQL version specific WMI namespace"
  • Discovery logs showing: ProcessDTO [executable='sqlservr.exe', commandLine='', executablePath='', ...]
  • Pattern terminating with graceful termination messages

Environment:

  • ServiceNow: Vancouver release
  • SQL Server: Initially 2022, later 2019 Express
  • MID Server: Windows-based
  • OS: Windows Server 2022

Prerequisites for SQL Server Discovery

Based on ServiceNow official documentation and real-world troubleshooting experience, ensure these prerequisites are met:

ServiceNow Application Requirements (Official)

  • Discovery and Service Mapping Patterns: Version 1.7.0 or later (August 2023)
  • Visibility Content: Version 6.12.1 or later (August 2023)

Credentials Configuration (Official)

  • Windows Credentials: Verify MID Server connection to target Windows server
  • Applicative Credentials: Required when using SQL authentication (JDBC credentials)

MID Server Requirements (Official)

  • Registry Permissions: MID Server needs permissions to read Windows Registry remotely (for SQL Cluster discovery)
  • WMI Query Permissions: Remote read-only access to:
    • Root\CIMv2
    • Root\Microsoft\SqlServer\ComputerManagement*
  • Performance Monitor Users: MID Server account must be in this local group
  • Service Logon Rights: "Log on as a service" right for MID Server account

SQL Server System Permissions (Official)

Read-only permissions to these SQL Server system objects:

  • @@version
  • sys.configurations
  • sys.dm_exec_sessions
  • sys.dm_os_schedulers
  • SERVERPROPERTY

Critical Prerequisites (Often Missed - From Experience)

  • TCP/IP Protocol: Must be enabled in SQL Server Configuration Manager
  • Static TCP Port: Configure SQL Server to listen on port 1433 (SQL Server Express defaults to dynamic ports!)
  • SQL Server Browser: Should be running for instance detection
  • Mixed Mode Authentication: Required for SQL Server login accounts
  • sqlservr.exe Process: Must be running to trigger the MSSQL DB on Windows pattern

ServiceNow Configuration (Critical)

  • JDBC Credentials: Use correct database type selection (MSSQL, not MySQL)
  • JDBC Connection String: Format must be jdbc:sqlserver://hostname:1433;databaseName=master;instanceName=INSTANCENAME
  • Credential Affinity: Assign credentials to appropriate IP ranges/MID servers
  • Network Connectivity: Verify port 1433 connectivity from MID Server to SQL Server using netstat -an | findstr "1433"

The Misleading Journey: Why I Got Sidetracked

Initial Misdiagnosis: WMI Namespace Issues

The discovery logs clearly showed SQL Server 2022 using ComputerManagement16 namespace:

Query result:
internal_namespace=ROOT/Microsoft/SqlServer internal_classname=__NAMESPACE Name=ComputerManagement16

This led to my (incorrect) assumption that the primary issue was ServiceNow's pattern not recognizing the newer SQL Server 2022 WMI namespace.

Hours of Misdirected Troubleshooting

Actions I Took (That Didn't Fix the Core Issue):

  1. Windows Permissions: Added MID Server account to Performance Monitor Users, Performance Log Users
  2. WMI Repository Reset: winmgmt /resetrepository - This actually made things worse by removing working WMI providers
  3. User Rights Assignment: Granted "Debug programs" and "Profile system performance" rights
  4. Service Account Changes: Modified MID Server service permissions
  5. SQL Server Permissions: Extensively tested sn_discovery SQL account permissions (these were always fine)

The Red Herring - SQL Server Version: Eventually I downgraded from SQL Server 2022 to SQL Server 2019, which resolved the WMI namespace compatibility issue:

Query result:
internal_namespace=ROOT/Microsoft/SqlServer internal_classname=__NAMESPACE Name=ComputerManagement15

The discovery progressed further but then failed at a NEW step: connection check.

The Breakthrough: A Simple netstat Command

After resolving the WMI namespace issue, discovery failed with:

Pattern exit because Graceful Termination, reason: Please check cmdb_ci_db_mssql_instance credenditals and run discovery again!

The Diagnostic That Revealed Everything

PS C:\> netstat -an | findstr "1433"
PS C:\>

No output = SQL Server was not listening on port 1433!

This simple command revealed what hours of complex troubleshooting had missed - SQL Server wasn't even listening on the expected port.

Understanding SQL Server Express Default Behavior

SQL Server Express installations default to:

  • Dynamic ports (random port assignment)
  • Named pipes for local connections
  • TCP/IP disabled or using non-standard ports

ServiceNow's JDBC discovery expects SQL Server on port 1433.

The Solution

Step 1: Configure SQL Server TCP Port

  1. Open SQL Server Configuration Manager:

    SQLServerManager15.msc
    
  2. Navigate to:

    • SQL Server Network Configuration → Protocols for SQLEXPRESS → TCP/IP
  3. Configure TCP/IP Properties:

    • Protocol tab: Enabled = Yes
    • IP Addresses tab: Scroll to "IPAll" section
    • TCP Port: Set to 1433
    • TCP Dynamic Ports: Clear (leave blank)
  4. Restart SQL Server service:

    Restart-Service "MSSQL$SQLEXPRESS" -Force
    

Step 2: Verify Port Configuration

netstat -an | findstr "1433"
# Should show: TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING

Step 3: Create Proper ServiceNow Credentials

Credential Type: JDBC Credentials (not MySQL, not generic Database)

Configuration:

  • Name: SQL Server Discovery Credentials
  • User name: sn_discovery
  • Password: [your password]
  • DB type: MSSQL (critical - not MySQL)
  • JDBC URL: jdbc:sqlserver://[hostname]:1433;databaseName=master;instanceName=SQLEXPRESS

Success Indicators

After applying the fix, discovery logs showed:

Creating CI
2025-09-02 19:21:43: CIType 'cmdb_ci_db_mssql_instance': {tcp_port=1433, instance_name=SQLEXPRESS, operational_status=1, running_process_command=sqlservr.exe, edition=Express Edition (64-bit), pid=3204, engine_edition=Express, version=15.0.2000.5, version_name=2019, name=SQLEXPRESS@windows}

CIType 'cmdb_ci_db_mssql_database': {name=master, database=master}
CIType 'cmdb_ci_db_mssql_database': {name=model, database=model}  
CIType 'cmdb_ci_db_mssql_database': {name=msdb, database=msdb}

Key Lessons Learned

1. SQL Server Express != SQL Server Standard

SQL Server Express has different default configurations that can break ServiceNow discovery expectations.

2. Multiple Issues Can Compound

This case had both WMI namespace compatibility (SQL Server 2022 vs 2019) AND TCP port configuration issues.

3. Error Messages Can Be Misleading

The initial WMI namespace errors masked the underlying TCP port configuration problem.

4. Test Network Connectivity First

Before diving deep into permissions and WMI troubleshooting, verify basic network connectivity:

netstat -an | findstr "1433"
Test-NetConnection -ComputerName [hostname] -Port 1433

5. ServiceNow JDBC Credentials Are Specific

  • Database type matters: MSSQL vs MySQL vs Oracle
  • Connection string format: Must match the database type
  • Port expectations: ServiceNow patterns expect standard ports

Prevention and Best Practices

SQL Server Installation

When installing SQL Server for ServiceNow discovery:

  1. Enable TCP/IP protocol during installation
  2. Set static port 1433 rather than dynamic ports
  3. Include Management Tools to ensure WMI providers are installed
  4. Use SQL Server Standard/Enterprise instead of Express when possible

ServiceNow Configuration

  1. Test JDBC credentials independently before running discovery
  2. Use correct credential types for each database platform
  3. Verify network connectivity before assuming permission issues
  4. Check ServiceNow compatibility with database versions

Troubleshooting Approach

  1. Start with network connectivity (ports, protocols)
  2. Verify basic authentication (can you connect manually?)
  3. Then investigate WMI/permissions issues
  4. Test with minimal configuration before adding complexity

Conclusion

What appeared to be a complex WMI namespace and permissions issue was fundamentally a basic network configuration problem. SQL Server Express's default dynamic port configuration conflicted with ServiceNow's expectation of port 1433 connectivity.

The hours I spent troubleshooting WMI permissions and namespace compatibility, while educational, were ultimately addressing symptoms rather than the root cause. A simple netstat command early in the troubleshooting process would have immediately revealed the TCP port issue.

This case demonstrates the importance of systematic troubleshooting that starts with basic connectivity before diving into complex permission and compatibility issues.

Don't forget to check out my detailed video walkthrough of this entire troubleshooting process on NowDivas!


If you believe the solution provided has adequately addressed your query, could you please mark it as 'Helpful'? This will help other community members who might have the same question find the answer more easily.

 

Thank you for your consideration.

Selva Arun

Related Articles

Comments
Christopher Hub
Tera Guru

Great article; I really appreciate that you didn't just provide steps, but illustrated your learning experience.  I always enjoy seeing how people think through problems.

 

This is a particularly interesting problem because SQL Express is often deployed for a lower-scale local database to support an app's front end which may even be co-located.  In that case, using named pipes for interaction is an ideal approach given that they reduce external exposure. 

 

It's also interesting that Discovery depends so heavily on an assumption about a fixed port when the classifier picks it up via running process name.  It looks like there are four different identification strategies for the SQL Server pattern; maybe you are falling to priority #2/#3 because of a problem with #1?

 

While the workaround of configuring a fixed port is viable for smaller footprints, an enterprise that has heavy use of SQL Express probably is using the default mode widely, and converting to a fixed port could be quite an effort, as well as making that app's signature more obvious, which security may not favor. 

 

It's certainly more work, I'm wondering if a different discovery approach that doesn't depend on a fixed port but rather successfully derives it when dynamic might help.  Unfortunately, that could mean some technical debt, but if SQL Express is a critical app, it would be justified with proper governance.

Selva Arun
Mega Sage
Mega Sage

@Christopher Hub ,

 

 I agree  and thank you so much for your feedback!!! I appreciate it. 

Selva Arun
Mega Sage
Mega Sage

@tiagomacul 

 

Thank you so much for sharing the link and I had create an article about my experience not to duplicate any one's work

Version history
Last update:
Wednesday
Updated by:
Contributors