Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Resolving could not open a connection to SQL Server errors

MSSQLTips author Jugal Shah By:   |   Read Comments (42)   |   Related Tips: More > SQL Server Configurations
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.

resolving issues connecting to sql server

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.

try to connect to the sql server using an ip address



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 sql server configuration manager


Using SC command

using the sc command to check that sql services are running

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.

check that the sql browser service is running



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.

use the sqlcmd-L to check that sql server is in the network


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.

open ssms and check the sql server network configuration protocols

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.

check the tcp/ip communication port



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.

in ssms select properties and go to the connections tab

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.

locally connect to sql server and check the error log


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.

configure the windows firewall for the sql server port and sql browser service

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

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.

add the sql browser service


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.

Next Steps


Last Update: 3/21/2011


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, March 21, 2011 - 4:22:53 PM - DeWitte Read The Tip

I always like to use TELNET to help diagnose connectivity problems with SQL server.  I've seen cases where the SQL server was properly listening on port 1433 and the client machine could ping the IP address, but I was unable to connect to to SQL server.  Using a quick:

TELNET <IP ADDRESS> 1433

I was able to determine that port 1433 was not open between the host and client, even though we thought the firewall was functioning properly.  The problem turned out to be a secondary firewall which had taken control and the port change had not yet migrated over.  Admittedly, this is not the most sophisticated test as the result is either NO connection or a blank screen (blank screen means success), but it does quickly point out port issues.

 


Monday, March 21, 2011 - 6:01:49 PM - David Pierson Read The Tip

Great tip - thanks Jugal. Good comment from DeWitte also.

I would like to see another tip that covers this problem but in the case where you cannot get a login onto the box itself. There are two situations where this has happened to me, and diagnosing is harder.

1) On a LAN where you don't have remote desktop access to the SQL Server box

2) In a web hosting environment, where you have even less access using the usual tools.

 


Monday, December 05, 2011 - 11:36:36 AM - Atul Read The Tip

First option resolve my error. Added a host file entry and it worked. Thanks a lot.

 


Thursday, February 16, 2012 - 3:50:05 PM - Renato Gonçalves Read The Tip
Este tutorial foi de grande ajuda para que eu pudesse resolver o problema, de [Um erro relacionadas à rede ou instância específica erro ocorreu ao estabelecer uma conexão ao SQL Server. O servidor não foi encontrado ou não estava acessível. Verifique se o nome da instância está correto e que o SQL Server está configurado para permitir conexões remotas. (Provider: TCP Provider, error:. 0 - Nenhum tal hospedar é conhecido) (Microsoft SQL Server, Error: 11001)]. Utilizei o Passo 5 para resolver o meu problema Estava colocando no Server Name apenas o nome do servidor BRSPSRVSQL, e o correto é BRSPSRVSQL\SQLEXPRESS. Faltava o nome da Instancia. Quando utilizei o comando sqlcmd -L, consegui visualizar o nome do servidor SQL que estava na rede com a instancia. Após colocar no nome completo BRSPSRVSQL\SQLEXPRESS, funcionou, consegui me conectar. Muito Obrigado, Jugal. Abraço!

Thursday, February 16, 2012 - 3:52:22 PM - Renato Gonçalves Read The Tip
This tutorial was helpful for me to solve the problem, [A bug related to the network 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)]. Step 5 used to solve my problem was putting in only the Server Name BRSPSRVSQL server name, and the right is BRSPSRVSQL \ SQLEXPRESS. Lacked the name of the Instance. When I used the command sqlcmd-L was able to view the SQL server name that was on the network with the instance. After putting in the full name BRSPSRVSQL \ SQLEXPRESS, it worked, I could connect. Thank you, Jugal. Hug!

Friday, April 20, 2012 - 5:03:48 PM - Peter Read The Tip

I noticed that IPv6 is causing massiv problems. 

I deactived it on the network stack but it did not work out. SQL / SQLExpress is still showing errors.

http://support.microsoft.com/kb/929852/

After that it worked pretty fine


Monday, June 25, 2012 - 1:10:35 PM - Mark Read The Tip

Mr. Shah,

Thank you very much for your comprehensive post!  The troubleshooting steps you outlined allowed me to fix connection issues that have been troubling our office for a couple weeks!

-Mark
Town and Country Legal Associates

 


Wednesday, June 27, 2012 - 2:00:56 PM - Shubhankara Read The Tip

Hi,

Please let me know if windows fire wall is Off, Then How can we stop this error.

--Shubhankara


Wednesday, June 27, 2012 - 4:18:48 PM - Jugal Read The Tip

There are multiple reasons for the connection failure issue, can you check each step and let us know the result.


Thursday, June 28, 2012 - 8:41:05 AM - Shubhankara Read The Tip
  • It’s a cluster server, In which we are getting error while connecting to one of the Named instance, Its being used by dynamic port.
  • We are using SQL Server 2005+Sp3.
  • Windows Fire-Wall is disabled across the environment
  • SQL Server browser is always up and running
  • IP address and Hostname entry has been made in "/etc/host" file
  • Allow Remote connections to server is enabled
  • I'm able to ping and telnet from client machine
  • Namedpipes and TCP/IP is enabled

 

In spite of all I'm getting the error, Please help me out.

 


Thursday, June 28, 2012 - 9:36:48 AM - Jugal Read The Tip

Can you check for the authenctication and SPN?, also copy the error message here.

setspn -L (To check the SPN)

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

 


Wednesday, July 11, 2012 - 12:37:36 AM - lakshmi Read The Tip

in my server mechine sql server 2005 installed.the sql server shared through lan to client mechine.if i run the vb with sql server software means it shows the named pipe provider error :40.could open the connection.


Friday, July 20, 2012 - 4:13:17 PM - Shubhankara Read The Tip

Please find the below error message.

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)

Domain account is configured in SPN with FQDN

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

Result set is -------------------TCP NTLM


Monday, July 23, 2012 - 11:17:49 AM - Jugal Read The Tip

Have you checked the SPN by using SETSPN -L,

Can you check error log and make sure there shouldn't be any SPN failure error. Please test all the checklist mentioned above.

 

 

 


Monday, July 30, 2012 - 11:41:03 AM - Shubhankara Read The Tip

There is no SPN errors everything is fine


Friday, September 21, 2012 - 6:10:11 AM - Vaishali Read The Tip

Very useful post... Thanks a lot...


Thursday, October 18, 2012 - 3:06:01 AM - sourabh Read The Tip

I execute the above 10 steps , still that i got error when i run the website "Startup Error: Unable to connect to the Database. Exception Message: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (-1)"   and when i visit the url http://yaf/install/default.aspx  and test database connection the error comes "Failed to connect: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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"  

 

What is the problem ? Please help me ? provide me the solution ?


Thursday, October 18, 2012 - 8:26:25 AM - Jugal Read The Tip

Sorabh,

First you have to let me know the output of all the above steps. You can do the TELNET <IP ADDRESS> 1433 and check whether you are able to connect it from there or not, check your connection string, try to connect from SSMS


Thursday, October 25, 2012 - 8:51:17 AM - Sharon Read The Tip

Sourabh,

Are you running SQL 2008 or SQL 2008R2? Please read this MS article if you are to see if it applies to you. KB was last updated couple of days ago.  We have not confirmed the fix but we were able to implement the workaround until our next patch cycle.   Good luck.

http://support.microsoft.com/kb/2526552

Sharon


Thursday, December 06, 2012 - 1:13:40 AM - vikas Read The Tip

 

 

Realy a great quality solution thanks


Wednesday, February 06, 2013 - 12:36:44 PM - Dinesh Read The Tip

 

Thanks for sharing these type of information, it is really helpful and gives clear idea what to do next when you run into issues. I appericate it.


Monday, February 25, 2013 - 5:52:23 AM - cadjinacou Read The Tip

Great article ! Thanks a lot for sharing this with us.


Monday, March 04, 2013 - 11:31:28 AM - Tom Read The Tip

http://answerforu.com/2011/07/27/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-s/


Wednesday, March 27, 2013 - 8:11:41 PM - Amit Read The Tip

Can I link 2 different servers on the same network using the IP address instead of server name.

FOr example,

USE master;
GO
EXEC sp_addlinkedserver
   N'other server IP',
   N'SQL Server';
GO

 

My eventual goal is to be able to access data in databases on 2 differrent servers.


Thursday, April 25, 2013 - 5:06:07 AM - Pete Read The Tip

Thank you, the SPN error caused the malfunction. Restarting the instance solved the Problem

Pete


Thursday, May 09, 2013 - 2:24:09 PM - Sharma Read The Tip

Thanks Jugal - this is a great post that allows systematic troubleshooting.

Step 6 identified the problem for me.

I know that I'll be referring back to this in the future.

 

10 out of 10 for accuracy.


Tuesday, May 28, 2013 - 10:40:25 AM - DBSQL Read The Tip

Hi Jugal,

We need immedaite help on this!!!!

We have the same issue in one of our Windows 2003 Cluster enterprsie 64 bit. There are few instances to which we are able to connect to SQL Server by physically logging on to the server, but unable to connect from a client computer sometimes. After two thre attempts it connects. The error is same as emntioned abaove Error 26. The SQL server is 2005 enterprise edition.

Step1:Able to ping the physical server as well as instance,

Step 2:SQL service is up and running,

Step3:SQL Browser service enabled and running,

Step4:name is correct,as it connectes after some attempts.

Step5:SQLCMD –L returns the list of the servers and server name is there,

Step6:Named Pipes and TCP/IP protocol are enabled,checked tthrough SQL Server configuration manager

Step7:Allow remote connection to this server is checked,

Step8: error log has the entry as Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\XYZ\MSSQL$XYZ\sql\query ].

Step9:firewall is disabled;

Step10: gives the output as "The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies"

 

 

Please advice!!


Wednesday, August 07, 2013 - 8:59:54 AM - Kristina Read The Tip

Great article and saved me a great deal of time.  Thanks !


Friday, September 06, 2013 - 4:09:50 AM - william Read The Tip

 

great tutorial.thnx


Wednesday, October 16, 2013 - 3:32:26 AM - shalini Read The Tip

i am getting an error no 10061.. my sql server is also showing "stopped" value in SQL Server cofiguration manager..to resolve it i had tried to restart it but it doesn't start..


Monday, January 06, 2014 - 4:03:17 PM - artur Read The Tip

Thanks so much. Great article and solve my problem with conection


Friday, February 21, 2014 - 5:45:11 AM - bhagyadeep Read The Tip

helo sir

plz hlp me this type of error gnret in my apps

{"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)"}


Friday, March 07, 2014 - 8:13:59 PM - Thirunavukkarasu Read The Tip

Windows Server 2012, how do we do tthe above


Thursday, April 03, 2014 - 9:03:41 AM - Ruald Venter Read The Tip

Thanks alot, step 6 solved my problem.


Wednesday, April 23, 2014 - 9:43:38 AM - Amit Read The Tip

 

You rock man ! Your tips were really useful and it resolved my issue. Keep up the great work. Thanks ! :)


Monday, May 19, 2014 - 8:43:10 AM - Tony Foo Read The Tip

Excellent list. My problem was with #6. The TCP/IP port was blank.


Friday, June 13, 2014 - 8:32:47 AM - Jagdish Read The Tip

Thanks alot, step 6 solved my problem. This tutorial was helpful for me to solve the problem.


Tuesday, June 17, 2014 - 1:28:56 PM - FS Read The Tip

Step 6 solved my problem. I installed SQL Express 2014. The default of SQL Server Network TCP\IP and Named Pipe were Disabled. Thanks a lot for the excellent list.


Wednesday, June 25, 2014 - 12:54:08 PM - Dan Read The Tip

Thank you for your post Jugal, really helpful and well written.

 

In case it helps other readers, a couple of note on my case: running SBS 2011 network, website server on separate Win7 machine, and SQL 2008 R2 on another machine. Configuration was done as in steps 6 , 7 and the ports in step 9. The environment had been fine for some time, but then all of a sudden the connections from the website server to sql server started dropping on occasion.

What was strange was that it was individual website connections, not an entire loss of availability. A couple of reboots seemed to solve things for a day or so, but then we lost all the connections and found that the Sql server instance was no longer visible on the network.

A few days before, we had done a bit of configuration work on the SBS2011 server (on default website and sharepoint), and renewed licences for Kaspersky anti virus on a number of machines.

With the help of Jugal's post, we have restored visibility of the Sql server instance by adding the sqlbrowser.exe exception to the firewall. I am still a bit confused as to how the environment was running in the first place without this exception, but pleased that I found this post and seemed to have it fixed! Thanks again.


Monday, August 04, 2014 - 3:05:01 AM - La_F Read The Tip

Thank you, it worked to my


Thursday, August 21, 2014 - 1:56:19 AM - srikanth rathod Read The Tip

Hi ,

Currently i am facing a issue with accessing the webservice for SQL 2012 SP1 reporting server for SCCM 2012 R2 instance , i am not able to access the weblink from the report manager server i.e URL http://server:80/reports_mssqlinstance , i am trying to access from SQL server.

ERROR when the link goes to IE is :Unable to connect the remote server.

I have verified below steps on sql 2012 sp1

1.telnet to 1433 is working fine, browser service is running fine.

2.port 80 is accessable , SPN is registered.

3.inbound rules created for 1433, 80 port.

Log shows me error :system.cannotUnloadappdomainexception:error while unloading appdomain (Exception from HRESULT:0x80131015.

Any solution for this, i have multiple instance on SQL 2012 server.

Please help

Thanks

 

 

 


Thursday, August 28, 2014 - 2:29:20 AM - John Read The Tip

Step 6 worked for me, thank you very much!!!



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
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.