Resolving could not open a connection to SQL Server errors

By: Jugal Shah | Read Comments (6) | Print Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2. Related Tips: More

Problem

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

Or

Or

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

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

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

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

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.

If the SPN is not found, read this article and this article 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

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code