ServiceNow SQL Server Discovery Troubleshooting: The Hidden TCP Port Issue!!

Selva Arun · 3 weeks ago

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):

Windows Permissions: Added MID Server account to Performance Monitor Users, Performance Log Users
WMI Repository Reset: winmgmt /resetrepository - This actually made things worse by removing working WMI providers
User Rights Assignment: Granted "Debug programs" and "Profile system performance" rights
Service Account Changes: Modified MID Server service permissions
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

Open SQL Server Configuration Manager:
```
SQLServerManager15.msc
```
Navigate to:
- SQL Server Network Configuration → Protocols for SQLEXPRESS → TCP/IP
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)

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:

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

ServiceNow Configuration

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

Troubleshooting Approach

Start with network connectivity (ports, protocols)
Verify basic authentication (can you connect manually?)
Then investigate WMI/permissions issues
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