Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server 'A network-related or instance-specific error occurred' (error 26, 40, 53): find where the connection stops

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

This message means the client did not complete a connection to the SQL Server instance. Microsoft's wording is "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections." It is followed by a provider line such as provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.

Because the cause can be the name, the service, the protocol, the port, the Browser service or a firewall, test in a fixed order from the server outward. Each test removes one layer, and the first one that fails names the cause.

What the error means

Microsoft's page lists the variants. Each begins with the same sentence, then ends differently:

  • provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
  • provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server, with (Microsoft SQL Server, Error: 53) and "The network path was not found"
  • provider: TCP Provider, error: 0 - No such host is known. with Error: 11001
  • Error 10060: "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond."
  • "No connection could be made because the target machine actively refused it"
  • "Login timeout expired"

Error 233, "No process is on the other end of the pipe", is different: Microsoft says it means SQL Server is not listening on Shared Memory or Named Pipes.

A refused connection and a timeout point to different places. A refusal means something answered and said no, which fits a stopped service or a port with nothing listening. A timeout fits packets being dropped, which fits a firewall or a wrong address. This is a reading of the messages, not a statement from Microsoft.

For a named instance, the client library asks the SQL Server Browser service on UDP port 1434 which port the instance uses. Microsoft states that this happens whether the instance uses a dynamic or a static port, and that if a firewall blocks the UDP port the client cannot find the port and the connection fails. A default instance does not need the Browser service and uses TCP 1433 unless changed.

Likely causes

Cause What you see How to confirm
Instance not running Fails locally and remotely; error 53 or refusal Services list or SQL Server Configuration Manager shows stopped
Wrong server or instance name, or an old alias on the client Error 26 or "No such host is known" Name in the connection string differs from the name in Configuration Manager; check client aliases
TCP/IP disabled on the server Local shared-memory connection works, remote fails SQL Server Network Configuration shows TCP/IP disabled
Browser service stopped or UDP 1434 blocked (named instance) name\instance fails, name,port works Connect with the port number appended
Port blocked by a firewall Timeout 10060; connects locally Connect by IP and port from the client
Default instance on a non-default port Plain name fails Port in the error log "Server is listening on" line differs from 1433
Name resolution fails Works by IP, fails by name Ping by IP then by name

Prove the cause before changing anything

Steps 1 to 6 only read. Step 7 changes a client alias and has its own back-out.

  1. Is the instance running? In SQL Server Configuration Manager, select SQL Server Services. A green arrow means running and a red square means stopped. In PowerShell Microsoft gives Get-Service | Where {$_.status -eq 'running' -and $_.DisplayName -like "sql server*"}. For a named instance, also look for the SQL Server Browser service.

  2. What name and port does the server use? In SQL Server Management Studio on the server, open Management, SQL Server Logs, the current log, and filter for "server is listening on". Microsoft's example is Server is listening on [ 'any' <ipv4> 1433]. Note the port. SQL Server Configuration Manager names a default instance as SQL SERVER (MSSQLSERVER) and a named instance as SQL SERVER (instance name).

  3. Does a local connection work? On the server, connect in SSMS or with sqlcmd using Windows Authentication. Then add tcp: before the name, such as tcp:ACCNT27 or tcp:ACCNT27\PAYROLL, which forces TCP. Microsoft says that if shared memory works and TCP does not, TCP is probably disabled.

  4. Does the network path work? From the client, ping the server IP address, then the name. Microsoft also names Test-NetConnection and Test-Connection as ways to test TCP connectivity. If ping works by IP and fails by name, name resolution is the cause. Microsoft states that a name that cannot be resolved does not by itself prevent a connection, because you can connect by IP address and fix name resolution later.

  5. Connect by IP and port. From the client, in SSMS, connect to <IP address>,<port number>, for example 192.168.1.101,1433. For a named instance, then try <IP address>\<instance name>. If the port form works and the instance form fails, the Browser service or UDP 1434 is the problem, or the instance is hidden from the Browser. Microsoft also names Portqry and PortQryUI for telling a blocked UDP 1434 from a blocked static port.

  6. List client aliases. In SQL Server Configuration Manager on the client, expand SQL Server Native Client Configuration, then select Aliases, and note any alias defined for the server you are trying to reach. Open its Properties and check that the server name and port are correct.

  7. Test the alias. Microsoft's test is to rename the value in the Alias Name field (for example MySQL to MySQL_test) and retry the connection. If the connection works, the alias is incorrect.

    Risk: renaming the alias changes the client's configuration, and any application that uses that alias name will fail to find it while it is renamed. If the connection still fails, rename the alias back to its original name, as Microsoft directs. If it works, the alias was the problem, so correct or remove it as an intentional change.

A good result at each step is a successful connection. Microsoft shows no sample output for most of these, so describe them as success or a named error rather than matching exact text.

Fix

Instance stopped. Start it from Configuration Manager or Services, then read the error log for why it stopped.

Disruptive step: starting and stopping the service interrupts every connection. If the instance stopped on its own, find the reason in the log before restarting, because it may stop again. To back out, stop the service again; there is nothing to restore.

TCP/IP disabled. In SQL Server Network Configuration, enable TCP/IP for the instance and restart the Database Engine, which Microsoft states is required for the change to take effect.

Disruptive step: the restart drops all connections. Do it in a maintenance window. To back out, disable TCP/IP again and restart.

Named instance, Browser blocked. Start the SQL Server Browser service and open UDP 1434. Microsoft stresses opening a UDP port, not a TCP port. If UDP cannot pass, set a static port for the instance and put the port in the connection string.

Risk: starting the Browser service and opening UDP 1434 lets any client that can reach that port ask the Browser which port an instance uses. Open the port only between the client subnets and the server, and have the network owner approve the rule. To back out, stop the Browser service and delete the firewall rule. Stopping the Browser will make name\instance connections fail again for clients that rely on it.

Disruptive step: to set a static port, Microsoft's steps are to clear a 0 in TCP Dynamic Ports, enter the port in TCP Port, and restart the SQL Server service. Clients that used the old dynamic port stop connecting until their strings or aliases change, and firewall rules must allow the new port. To back out, restore the old values and restart.

Microsoft adds that if Listen All is set to Yes, only the values under IPAll are used, and each IP n section has an Enabled setting defaulting to No.

Default instance on another port. Append the port: servername,2000 in Microsoft's example. If that connects, the firewall allows that port and the connection string should carry it permanently.

Firewall blocking the port. Allow the TCP port, and for a named instance UDP 1434, through every firewall between client and server. Microsoft points to its page on configuring Windows Firewall for Database Engine access and to the network administrator for third-party firewalls.

Risk: every allow rule widens who can reach the server. Scope each rule to the client subnets that need it (for example 10.0.20.0/24), not to any address, and have the firewall owner approve it. To back out, delete the rule you added. Microsoft's page does not give a firewall command, so use your firewall's own tool.

Stale name resolution. Microsoft suggests ipconfig /flushdns on the client when a name fails but its IP works, then ping by name again. Correct the DNS record if the name resolves to the wrong address.

Risk: ipconfig /flushdns clears the client's cached name lookups, so the next lookups go to DNS again; nothing is lost and there is nothing to back out. Editing a DNS record changes where every client sends traffic for that name, so note the old address first and put it back if the change is wrong.

Client protocol order. If connecting with tcp: works and plain does not, the client is probably using named pipes. In client SQL Native Client Configuration, enable TCP/IP and give it a smaller protocol order number than named pipes.

Risk: changing the client protocol order changes how every application on that client connects, and a client that depended on named pipes may stop connecting. To back out, restore the previous order and enabled state.

Confirm the fix

  • From the failing client, connect with the same connection string the application uses, not a test string.
  • For a named instance, confirm the name\instance form works with no port, then confirm the application connects after a restart of the client.
  • Read the SQL Server error log for the successful session if it reports one.

Example (illustrative): app03 in CONTOSO cannot connect to sql01\PAYROLL and logs error 26. On sql01 the instance is running, and the log says Server is listening on [ 'any' <ipv4> 51847]. From app03, 10.0.20.31,51847 connects but 10.0.20.31\PAYROLL does not. The Browser is running, but the firewall between the subnets allows TCP 51847 and not UDP 1434. With a client timeout of 15 seconds and three retries, users wait 15 x 3 = 45 seconds before seeing the failure. The fix is either to open UDP 1434 or to use sql01,51847 in the connection string.

What to watch afterwards

  • A dynamic port can change when the service restarts. If you rely on it, the Browser must stay reachable.
  • After adding or removing firewalls, retest both the TCP port and UDP 1434.
  • Keep a record of each instance's name, port and whether it is hidden from the Browser.
  • Check client aliases after server moves; Microsoft notes aliases can come from old configurations no longer needed.

Risks and limits

These commands have not been run or lab-tested. Error wording differs by client library. Microsoft's page describes SQL Server on Windows; Linux, Azure SQL and failover cluster listeners have their own steps, and the page directs availability group listener problems to its articles on listeners and failover cluster instances.

Do not turn the firewall off to test. Test the specific port instead. Do not enable named pipes or open the server broadly to make an error go away.

Sources