Resolving could not open a connection to SQL Server errors

Problem

Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:

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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

ERROR: (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

A network-related error 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. (provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)

The errors could be for Named Pipes connections or TCP/IP connections and in this tip, we look at what may be causes to these errors and how to resolve.

Solution

There could be several reasons you get these error messages. Follow the below steps to see if you can resolve the issue.

Step 1 – Ping the SQL Server

Ping the physical server where SQL Server is installed from the client machine to make sure it is on the network.

If this does not work, you can try to connect to the SQL Server using just the IP Address (for the default instance) or the IP Address\Instance Name for a named instance.

If you can connect using the IP address, you can add the SQL Server machine into the hosts file. To add the entry in the hosts file open the file located in %SystemRoot%\system32\drivers\etc\ and add the info using Notepad.

For example, let’s say my server SQLDBPool uses IP address 74.200.243.253, I can add this to the hosts file with the machine name of SQLDBPool. Now I should be able to use the machine name instead of the IP address to connect to the SQL Server.

Step 2 – Validate SQL Services are Running

Make sure the SQL services are running. You can check the SQL Server services by using the SC command opening SQL Server Configuration Manager. Many times you may find that the SQL Server instance is not running.

Using SQL Server Configuration Manager

You can use SQL Server Configuration Manager to make sure the services are running. If for some reason you cannot find SQL Server Configuration Manager check out this article.

Using SC command

From a Windows command line you can issue the following command to see the status of the services.

Please note for a named instance you have to write the command as follows using the correct instance name, by replacing instancename with the actual SQL Server instance name.

sc query mssql$instancename

Step 3 – Validate SQL Server Browser Service is Running

Check that the SQL Server Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Server Browser service enabled and running. You can check the browser service status using either SQL Server Configuration Manager (see step 2) or the SC command as follows.

Step 4 – Check Using Correct SQL Server Instance Name

Make sure you are using the correct instance name. When you connect to a default instance, machinename is the best representative for the instance name and when you connect to a named instance such as sqlexpress, you need to specify the instancename as follows: machinename\instancename where you enter the SQL Server instance name for instancename.

Step 5 – Find the SQL Server

Check that SQL Server is in the network. You can use the SQLCMD -L command to retrieve the list of SQL Servers installed in the network. Note that this will only return SQL Servers if the SQL Server Browser service is running.

Step 6 – TCP/IP and Named Pipes are Enabled

Check the TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes and TCP/IP protocol.

For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

Step 7 – Allow Remote Connections is Enabled

Check to see if allow remote connections for this server is enabled. In SSMS, right click on the instance name and select Properties. Go to the Connections tab and make sure Allow remote connections to this server is checked. If you need to make a change, you must restart the SQL Server instance to apply the change.

You can also configure the remote server connections using the below commands. If you make changes you will need to restart SQL Server for these to take affect.

The settings below are equivalent to the settings in the image above.

exec sp_configure "remote access", 1          -- 0 on, 1 off
exec sp_configure "remote query timeout", 600 -- seconds
exec sp_configure "remote proc trans", 0      -- 0 on, 1 off

Step 8 – Check SQL Server Port Number

Locally connect to SQL Server and check the error log for the port entry. You can execute XP_READERRORLOG procedure to read the errors or use SSMS by going to Management > SQL Server Logs and select the Current log. Scroll to the bottom for the first entries in the error log and look for entries similar to below that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433 in this case.

Step 9 – Validate Firewall is Not Blocking SQL Server Access

Configure the Windows Firewall for the SQL Server port and SQL Server Browser service. Go to Control Panel and click on Windows Firewall. Go to exceptions tab as shown below. You can read this tip Configure Windows Firewall to Work with SQL Server for more information.

Click on Add Port… and enter the port number and name.

Click on Add Program… to add the SQL Server Browser service. Here you need to get the browser service executable path, normally it is located at C:\Program Files\Microsoft SQL Server\90\Shared location for SQL 2005 or similar for other versions of SQL Server. Browse the location and add the SQLBrowser.exe in the exception list.

Step 10 – Check Service Principal Name is Registered

If you are able to connect to SQL Server by physically logging on to the server, but unable to connect from a client computer then execute the below query in a query window to check the SPN.

-- run this command to see if SPN is not found
EXEC xp_readerrorlog 0, 1, "could not register the Service Principal Name", Null

If the SPN is not found, read this Register a SPN for SQL Server Authentication with Kerberos and this How to Configure an SPN for SQL Server Site Database Servers for more details about how to setup and register an SPN.

Next Steps

• Next time you have issues connecting, check these steps to resolve the issue.
• Related tips: Understanding SQL Server Net-Libraries

Jugal Shah

Biography

Jugal Shah is the founder of SQLDBPool.com, where he is writes articles on SQL Server Administration and Development. Jugal has 8+ years of extensive experience in SQL Server Database administration and development. Jugal has worked on SQL Server 2000, 2005, 2008 and 2008 R2. In his current assignment, Jugal is managing large critical databases. His expertise is in database security, performance tuning and implementing high availability solutions. He can be contacted at jugal.shah@sqldbpool.com. He has been recognized as a SQL Server MVP since 2010.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2013