SQL Server | Error: 40 - Could not open a connection to SQL Server

Overview 💥

Error 40 is a generic connectivity failure that means the client (for example, the Liongard Inspector) could not establish a network connection to the SQL Server instance.

The full message commonly looks like:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible... (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This article explains why this happens, how to systematically troubleshoot it, and practical commands and checks to resolve the issue.

Why this happens? 🤔

Typical root causes for Error 40:

SQL Server service is stopped or the machine is unreachable.
Incorrect instance name, port, or protocol (TCP/IP vs Named Pipes).
SQL Server Browser service not running for named instances.
Firewall blocking SQL Server port (default TCP 1433; UDP 1434 for SQL Browser).
SQL configured to disallow remote connections.
Incorrect credentials or authentication mode mismatch (Windows vs SQL auth).
Network routing, NAT or proxy issues between the Inspector host and the SQL host.
Client is attempting the wrong protocol (e.g., Named Pipes instead of TCP/IP).
Insufficient permissions for the account used by the Inspector (some endpoints require additional rights).

Common scenarios 🧑‍🏫

Symptom	Likely cause	Quick check
`error: 40` from a remote agent	TCP blocked, instance unreachable	`Test-NetConnection -ComputerName <sqlhost> -Port 1433`
Named instance fails but default instance OK	SQL Browser not running or dynamic port used	Check SQL Browser service & inspect ports
Only local connections work	Remote connections disabled	SQL Server → Properties → Connections → Allow remote connections
Authentication errors after connection	Wrong auth mode or creds	Check SQL auth mode and test with `sqlcmd`

Steps To Resolve 👨‍💻

Before you start: run these checks from the machine that runs the Liongard agent (not from your workstation), so you validate the same network path the Inspector uses.

1️⃣ Verify SQL Server service & instance

On the SQL host, confirm SQL Server service is running:
- Windows Services: SQL Server (MSSQLSERVER) for default instance, or SQL Server (<InstanceName>) for named instance.
If using a named instance, either:
- Ensure SQL Server Browser service is running (so clients can resolve instance → port); or
- Use the explicit port number in the Inspector configuration (recommended for predictable behaviour).

2️⃣ Confirm instance name and port

Default instance uses port 1433; named instances often use dynamic ports.
To find the port:
- Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for <Instance> → TCP/IP → IPAll → TCP Dynamic Ports / TCP Port.
If dynamic ports are used, either set a static TCP port or supply the explicit host,port in the Inspectors instance/host field.

3️⃣ Test basic network connectivity (from agent host)

PowerShell (Windows agent):

Test-NetConnection -ComputerName <SQL_HOST> -Port 1433 -InformationLevel Detailed

Linux agent:

nc -vz <SQL_HOST> 1433
# or
telnet <SQL_HOST> 1433

If these fail, fix network/firewall/routing before continuing.

4️⃣ Validate SQL protocols & remote connections

Open SQL Server Configuration Manager:
- Ensure TCP/IP is Enabled (and Named Pipes if you rely on it).
In SQL Server Management Studio (SSMS):
- Right-click server → Properties → Connections → check Allow remote connections to this server.
Restart SQL Server service after changes.

5️⃣ Firewall and network devices

On SQL host, confirm OS firewall allows inbound on configured SQL port(s).
- Example: Windows Firewall rule for sqlservr.exe or port 1433.
Ensure any network firewalls / NAT / load balancers forward the port to the SQL host.
If the SQL host sits behind a reverse proxy or appliance, confirm TCP 1433 reaches the SQL host (no protocol translation).

6️⃣ Authentication & permissions

Verify SQL Server authentication mode:
- SSMS → Server → Properties → Security → SQL Server and Windows Authentication mode if you use SQL accounts.
Test a direct connection from the agent host:
- Using sqlcmd (Windows):
```
sqlcmd -S <sqlhost>,<port> -U <user> -P <password> -Q "select @@version"
```
- If using Windows Authentication, test with the same service account context as the agent.
Ensure the Inspector account has the required permission set (e.g., VIEW SERVER STATE if your environment/inspector requires it). If you’re unsure which permissions are needed, provide logs to Support.

7️⃣ Named Pipes provider note

The error text references the "Named Pipes Provider" when the client attempted a Named Pipes connection and failed. Recommended action:
- Prefer TCP/IP for remote inspections (enable TCP/IP on SQL, and use host,port format).
- If Named Pipes is required, confirm Named Pipes protocol is enabled and reachable from the agent host.

8️⃣ SQL Server Browser (UDP 1434)

For named instances using dynamic ports, the SQL Browser maps instance → current port via UDP 1434.
Ensure UDP 1434 is not blocked between agent and SQL Browser service or, again, use explicit TCP port to avoid depending on Browser.

9️⃣ Inspect logs

SQL Server error logs (in SQL Server Management Studio under Management → SQL Server Logs).
Windows Event Viewer (Application/System) for networking or authentication events.
Liongard Inspector logs — include the exact error, timestamp, and the host/instance string the Inspector attempted to use.

Useful commands & examples 🤩

✅ PowerShell network test

Test-NetConnection -ComputerName sql.example.local -Port 1433 -InformationLevel Detailed

✅ SQL client test (sqlcmd)

# Default port 1433
sqlcmd -S sql.example.local -U sa -P 'StrongPassword!' -Q "select name, state_desc from sys.databases"

# Explicit port
sqlcmd -S sql.example.local,1433 -U sa -P 'StrongPassword!' -Q "select @@version"

✅ Telnet / nc quick test (Linux)

nc -vz sql.example.local 1433
telnet sql.example.local 1433

✅ Check TCP listener on the SQL host

On SQL host (Windows) run:

netstat -ano | findstr 1433

Confirm process ID maps to sqlservr.exe.

Quick troubleshooting decision 🚀

Check	Command / Where	If FAILS → Do this
Can agent reach SQL host & port?	`Test-NetConnection` / `nc`	Fix network/firewall; confirm routing/NAT rules
Is SQL service running?	Services.msc / `Get-Service`	Start SQL Server service
Is TCP/IP enabled?	SQL Config Manager	Enable TCP/IP, restart instance
Are remote connections allowed?	SSMS → Server Properties → Connections	Enable and restart
Using named instance?	SQL Browser running?	Start SQL Browser or use static port
Auth fails in `sqlcmd`	`sqlcmd` test	Verify auth mode & credentials; check account lockout
Firewall blocking?	Windows Firewall / network FW	Open TCP port 1433 (or configured port)
Logs show errors?	SQL logs / Event Viewer	Investigate specific messages; share with support

When to contact Liongard Support 🦁

Open a Support ticket if all of the following are true:

You validated network connectivity from the actual agent host to the SQL host/port and it succeeds, but the Inspector still fails.
You’ve checked SQL service, protocols, SQL Browser, remote connections, firewall rules, and authentication and are still stuck.
Include with your ticket:
Exact Inspector logs (timestamped) showing the error.
The instance/host string used by the Inspector (e.g., sql.example.com,1433 or sql.example.com\INSTANCE).
Results of Test-NetConnection / nc and sqlcmd tests from the agent host.
SQL Server error log excerpts around the failure time.
SQL Server version and instance type (default vs named).
Any recent changes (firewall, network, SQL upgrades, password rotation).

Best practices & prevention 🌟

Use a static TCP port for SQL instances (avoid dynamic ports for production).
If possible, configure the Inspector to use the explicit host,port form rather than instance name→browser resolution.
Maintain a dedicated service account for Inspector access; document credential rotation procedures.
Allowlist the agent IP(s) on the SQL host firewall (and any network firewalls).
Monitor SQL service health and setup alerts for service downtime.
Keep SQL Server and Windows updates applied (test in staging before prod).

Third-party links & disclaimer ‼️

We may reference external third-party resources solely as additional guidance.

Liongard does not own, control, or guarantee the accuracy, security, or reliability of third-party sites. Please use them at your own discretion and risk.