Resolving could not open a connection to SQL Server errors

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:

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)

Or

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)

Or

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)

These errors could be for either Named Pipes connections or TCP/IP connections. 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

Make sure you are able to ping the physical server where SQL Server is installed from the client machine. If not, you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Instance Name for a named instance.

If it resolves using an IP address, you can add the SQL Server machine into /etc/host file. To add the entry in the /host file type %SystemRoot%\system32\drivers\etc\ in the run window and open the host file using Notepad. In the below image I added IP address 74.200.243.253 with a 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

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

Using SQL Server Configuration Manager

Using SC command

Please note for a named instance you have to write the command as follows using the correct instance name: sc query mssql$instancename

Step 3

Check that the SQL 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 Browser service enabled and running. You can check the browser service status using Step 2 and either using SQL Server Configuration Manager or the SC command as follows.

Step 4

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 should specify machinename\instancename

Step 5

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

Step 6

Check for 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

Check to see if remote connections is enabled. Right click on the server name in SSMS and select Properties. Go to the Connections tab and make sure Allow remote connection 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 the error log for issues. 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. You should see entries similar to below that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433.

Step 9

Configure the Windows Firewall for the SQL Server port and SQL Browser service. Go to Control Panel -> Click on Windows Firewall -> Go to exception tab as shown below. You can also read this tip for more information as well.

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

Click on Add Program… to add the SQL 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. Browse the location and add the SQLBrowser.exe in exception list.

Step 10

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 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 article and this article for more details about how to setup and register an SPN.