Register Log in

Common SQL Server Problems: Network Related or Instance Specific Error Occurred

Comments 0

Share to social media

This is the first in a series of articles meant to provide practical solutions to common issues. In this post, we’ll talk about one of the most pervasive error messages out there:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

Followed by either:

(provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

or

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

One thing I’ll say right off the bat: This is not a permissions issue.

Debugging Steps

People often come to my team when they encounter this error, but we can rarely offer a simple, one-size-fits-all answer. But more often than not, the solution to their issue is one of the following:

  1. The server could actually be down. (We do verify this before we send them away!).
  2. It could be a typo in the connection string. Sometimes it really is as simple as verifying that the instance name is correct. This includes cases where you are specifying an instance name and shouldn’t be, or vice-versa. If you’re using SQL Server Express, often it means you should be using myserver\SQLEXPRESS or localhost\SQLEXPRESS. Check the instance names that are actually running in SQL Server Configuration Manager:
    1. Open Configuration Manager
    2. Expand “SQL Server Network Configuration”
    3. Review the “Protocols for” items listed there:
      1. MSSQLSERVER is the default instance. That means you specify the server name but no instance name.
      2. Any other value is a named instance. That means you specify the server name and the instance name, in the form SERVERNAME\INSTANCENAME (though some applications and libraries expect those parameters to be provided separately).
  3. It could be DNS.
    1. Make sure nslookup and/or Resolve-DNSName return the result(s) you expect when querying the name in the connection string.
    2. Try connecting by IP address instead of server name.
    3. Try using the fully-qualified domain name (FQDN) – e.g. myserver.mydomain.com instead of just myserver.
    4. Validate the results of Test-NetConnection – both without a port and with -Port 1433 (or whatever port SQL Server is running on) from the machine trying to connect, not from the database server or from your workstation.
    5. Don’t rely on ping – often ping and other ICMP traffic will be rejected by design, so an unsuccessful ping isn’t necessarily evidence that the server is unreachable on SQL Server-specific ports.
  4. Check to see if the server is configured to allow remote connections.
    1. In SSMS, right-click the server, and select Properties.
    2. On the Connection tab, make sure “Allow remote connections to this server” is checked.
    3. If necessary, check the box, and restart the SQL Server service (following established process, of course – please don’t take down production and then say, “Aaron told me to!”).
  5. Check availability group settings. If using an availability groups listener, make sure the database name is in the connection string and that MultiSubnetFailover=True is included. Otherwise, you could get directed to an IP that is not actively hosting any resources for the availability group.
  6. TCP/IP may be disabled. This is the default behavior for some versions and editions. You can enable this through SQL Server Configuration Manager:
    1. Open Configuration Manager.
    2. Expand “SQL Server Network Configuration.”
    3. Highlight “Protocols for” the instance you want to affect.
    4. In the right pane, check that “TCP/IP” is enabled.
    5. If necessary, right-click, Enable, then restart the SQL Server service (following established process, of course).
  7. Check the Port. The server could be running on a port you didn’t expect. You can check the port it’s running on via SQL Server Configuration Manager:
    1. Open Configuration Manager.
    2. Expand “SQL Server Network Configuration.”
    3. Highlight “Protocols for” the instance you are trying to reach.
    4. In the right-pane, right-click TCP/IP and select Properties.
    5. On the IP Addresses tab, scroll to IPAll and see what port is listed under either TCP Dynamic Ports or TCP Port.
      1. If using TCP Port, change your connection string to reference that port explicitly, e.g. myserver.mydomain.com,1488.
      2. If using TCP Dynamic Ports, see the section below about firewall/routing, because you won’t want to hard-code the port for a named instance, since the port will be reassigned on service restart.
  8. Check the Docker connection details. If you are using Docker, it is pretty common to expect SQL Server inside the container to run on the host’s port 1433, so people expect to connect – particularly from within another container – via localhost or 127.0.0.1.
    1. You should set your SQL Server container to expose port 1433 on a dedicated port on the host machine, e.g. docker run … -p 6000:1433, then you can connect to localhost,6000.
    2. You can also look at using host.docker.internal.
  9. It could be firewall/routing.
    1. It could be that ports 1433 (TCP) and/or 1434 (UDP) are not open. This could be due to:
      1. Windows Defender firewall – to validate this in a home lab, just turn the service off temporarily (on both the machine trying to connect and, on the server). In a corporate setting, please work with your IT and/or networking teams before playing with native firewalls.
      2. A real firewall, load balancer, or other routing devices/rules in your network – work with your IT and/or networking teams.
      3. If you’re trying to connect to database resources in Azure or some other cloud, make sure you have added your IP address/range to inbound allow rules, and that you prefix the FQDN with tcp: to force TCP/IP , e.g. for Azure SQL Database, use tcp:myserver.database.windows.net,1433.
    2. Within a cloud environment, it could be that your resource groups or environments don’t share a common network. It may very well be by design that the app running in your staging environment is not able to access the databases in your production environment. In Azure, ensure that the SQL Server firewall settings enable “Allow Azure services and resources to access this server.”
    3. If you are using a named instance, it will use dynamic ports by default. This requires the SQL Server Browser service to be enabled and running, and reachable via port 1434. If you can’t rely on this, you may have to work with your networking or reliability engineering team to agree on a specific port to use, then set that explicitly for the service using SQL Server Configuration Manager:
      1. Open Configuration Manager.
      2. Expand “SQL Server Network Configuration.”
      3. Highlight the “Protocols for” the instance you want to affect.
      4. In the right pane, right-click TCP/IP and select Properties.
      5. Remove any ports from TCP Dynamic Ports.
      6. Add the agreed-upon port to TCP Port.
      7. Click Apply/OK, then restart the SQL Server service (following established process, of course).
A screenshot of a computer AI-generated content may be incorrect.

Let me know if you know any other fixes for this problem

Those are the most common fixes I see for the “A network-related or instance-specific error…” messages. Do you have additional fixes or comments about the ones I’ve listed? Please leave a comment below!

Article tags

Load comments

Recommended

About the author

Aaron Bertrand

See Profile

Aaron Bertrand (he/him), a cheery Canadian with industry experience dating back to SQL Server 6.5, is a Staff Database Reliability Engineer for Stack Overflow. He also blogs at sqlblog.org, mssqltips.com, and thebertrandfamily.com.

Aaron's contributions
Aaron's latest contributions: