Resolving could not open a connection to SQL Server errors

By:   |   Updated: 2023-10-31   |   Comments (89)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Configurations


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)

Or

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 - Check that you can ping the SQL Server box

Make sure you are able to ping the physical server where SQL Server is installed from the client machine.

resolving issues connecting to sql server

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.

try to connect to the sql server using an ip address

Step 2 - Check that the 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 sql server configuration manager
sql server configuration manager

Using SC command

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

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, by replacing instancename with the actual SQL Server instance name.

sc query mssql$instancename

Step 3 - Check that the 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.

check that the sql browser service is running

Step 4 - Check that you are using the 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 - Check that you can 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.

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

Step 6 - Check that 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.

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 that allow remote connections for this server 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.

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 port number that SQL Server is using

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.

locally connect to sql server and check the error log

Step 9 - Check that the firewall is not blocking access to SQL Server

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.

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 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.

add the sql browser service

Step 10 - Check that the Service Principal Name has been 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-10-31

Comments For This Article




Wednesday, August 2, 2023 - 5:21:56 AM - Parveen Back To Top (91448)
An error occurred when attaching the database(s). click the hyperlink in the massage Column for details.

this is the error i have on the server. unable to recover the data for 2 months. Data was not backup properly. Please suggest If any way to recover the data.

Thursday, July 27, 2023 - 1:50:51 AM - Manimaran Ravi Back To Top (91432)
Thanks for the checklist :) works well

Tuesday, January 24, 2023 - 4:04:21 AM - Andre Back To Top (90846)
Super great checklist... Thx. It solved my problem.

Friday, July 22, 2022 - 5:17:11 PM - Demosthenes Back To Top (90306)
Great article! Thank you very much. Very helpful.

Wednesday, July 13, 2022 - 4:25:18 AM - Ranjith Back To Top (90251)
I am getting error :

can not connect to in_templ< -816 >, SQLSTATE = 08001 Can you please help to resolve this issue

Monday, October 4, 2021 - 7:39:25 PM - Meliandah Back To Top (89304)
So, i had troubles since 2 days but everything was nice , i didnt found problem until...
in fact : 1 problem isnt solved in your tutorial, it's the missing package from NuGet for Universal Windows Applications :)
Even adding external references from MySql DLLs don't work , for UWP , go to NuGet package management (or manually in windows selection in VS) and add MySql.Data (or any other package who manage MySql).
Then all functions will work \o/ .

Friday, October 1, 2021 - 7:17:49 PM - rob Back To Top (89295)
man... thank you so much! i was going crazy and it was the sql server had the firewall on and didn't allow any connections to it. Never thought about it until this article, thanks again!!

Wednesday, July 28, 2021 - 11:45:18 AM - Elad Back To Top (89067)
didnt solve my problem at all
What can I do ?
https://docs.microsoft.com/en-us/answers/questions/493149/error-40-could-not-open-a-connection-to-sql-server-1.html

Wednesday, July 28, 2021 - 11:44:32 AM - Elad Back To Top (89066)
Sorry but that didnt help me, in my windows the SQL SERVER looks different.
I have the same error- Error: 40- could not open a connection to SQL Server - in Visual Studio 2019

Tuesday, June 15, 2021 - 12:18:04 PM - Derek Martindale Back To Top (88857)
Fixed my problem!
Very clear, very detailed instructions.
Thank you

Tuesday, February 9, 2021 - 9:40:00 PM - Glenn Orlando Pringle Back To Top (88202)
Thanks!!!
You helped me figure out my issue

Tuesday, February 2, 2021 - 10:21:20 PM - Dominic Back To Top (88140)
When the browser service is turned off and you have a unique port number for the SQL Server, consider that you require an alias name when trying to connect to the hostname only in SSMS. Within the SQL Configuration Manager under the SQL Native Client >> Aliases section for both native client sections add the alias name the same as the hostname along with the unique port number.

Saturday, January 2, 2021 - 6:12:53 PM - Ben Z. Back To Top (87994)
Awesome! You resolved my issue!

Monday, November 16, 2020 - 7:54:13 AM - Vanitha Sundaram Back To Top (87799)
Hi All,
I tried all the above remedies. But none worked. Please help on it.
Connect to Server ------------------------------ Cannot connect to DESKTOP-JQ2TJQV. ------------------------------ ADDITIONAL INFORMATION: 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) (Microsoft SQL Server, Error: 2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 ------------------------------ The system cannot find the file specified ------------------------------ BUTTONS: OK -----------------------------

Tuesday, September 15, 2020 - 2:01:12 PM - Emmanuel Ikonne Back To Top (86481)
SQL Server Browser was the problem for me.

Saturday, July 18, 2020 - 3:51:53 PM - Bjorgen Eatinger Back To Top (86153)

Fantastic article, but you forgot a really important item to check, especially if the database was just copied from one server to another.

The PASSWORD.  I was able to resolve my problem by using your "XP_READERRORLOG" hint, and I found the following in the logs:

2020-07-18 12:18:37.630 Logon Login failed for user 'xxxx'. Reason: Password did not match that for the login provided. [CLIENT: 10.1.252.250]

No idea why Microsoft wasted hours of my time with this very stupid issue, as I did copy the logins from the old server to the new one.

Wednesday, February 26, 2020 - 8:35:07 AM - Adriano Back To Top (84812)

Hi Jugal Shah, excellent article! I’m facing a problem that perhaps you can help me. I have a SQL server registered with a non-default instance name. I’ve done everything you explained in your “Resolving could not open a connection to SQL Server errors” article. Let’s say SERVER\INSTANCE. In the server itself I can only access, SSMS for example, using server\instance. It doesn’t work IP Adress\Instance as it does when working with another servers. It’s also curious that the clients can access only with the IP address without informing the instance name.

Regards!

Thursday, February 20, 2020 - 8:06:35 PM - William Woolmer Back To Top (84683)

Legendary sequence here, have used them repeatedly over the years.

Thank you

Tuesday, January 28, 2020 - 7:58:11 PM - Linda K. Back To Top (84022)

Dear Jugal Shah, Thank you so much for posting this. I am pretty much retired and haven't moved a database system to a new server since 2007. My last client decided to upgrade and it's been a bit of struggle remembering all the details that I once had down pat. My system administrator came across your article and resolved our problem by turning on Named Pipes. I'd completely forgotten about it. Such a fundamental thing that you never have to think about except when setting up a new system. Thanks again.

Friday, September 20, 2019 - 9:50:13 AM - Michael Back To Top (82520)

A staff member had the same error on his computer but when I logged on to his computer, my SQL connection worked fine.

That implied some sort of permissions discrepancy. I gave him the same permissions as my account and he still got the error.

After doing some more digging, I found some entries and keys in my registry's HKEY_CURRENT user that pointed to the SQL database.

I exported the settings, saved it as a .REG file and imported them into the staff member's registry under his Windows logon.

Eureka! Success!

Wednesday, February 20, 2019 - 9:43:55 PM - SI Back To Top (79076)

Thank you. Manage to resolve issues on SQL server using your website guide.


Thursday, December 6, 2018 - 4:16:42 PM - Allan Back To Top (78417)

 Great solutions. it worked for me, in my case open port 1433 on firewall

Thursday, January 4, 2018 - 4:57:29 AM - Raghu Back To Top (74752)

 Thanks for the detailed steps issue resolved after enabling SQL browser

 

Friday, November 10, 2017 - 10:28:45 PM - mrduncle Back To Top (69555)

For me, take your pick:

- SQL Server Browser not running

- firewall not set up correctly

but it doesn't matter due to breadth of the issues covered in this comprehensive guide because I can now connect.  Only comment is that the Firewall section may need an update because current versions of windows (in my case Windows 10) doesn't look much like those dialogs. Thanks for taking the time to compile this thorough troubleshooting guide.

Wednesday, September 6, 2017 - 4:56:04 PM - Franklin Medina Back To Top (65953)

 

Clear, understandable

Thanks!

Monday, July 17, 2017 - 8:20:17 AM - Sandeep Back To Top (59467)

 Step 5 worked, thank you so much

 

Wednesday, May 3, 2017 - 11:23:20 AM - Josko Zlatar Back To Top (55414)

 

 Thank you for this really helpful cookbook!

 

Monday, August 1, 2016 - 9:39:43 AM - Munish Gumber Back To Top (43019)

thanks man. you saved my time..great!!

Sunday, June 19, 2016 - 7:36:21 AM - Gemore Back To Top (41721)

Thank you man, you saved my night.

 

 

Thursday, June 9, 2016 - 8:20:59 AM - Atul Back To Top (41651)

 I also faced the same issue.

Root Cause: I found that SQL servr agent was not running.

After investigation I found that SQL server Agent process was not running due to password mismatch.

Resoltion : I update the my current password for all the process of SQL Server.

Steps : Control Panel > Adminstrative Tool > Services > Navigate to all SQL Processes and Update the password under properties window > logon tab.

 

Tuesday, June 7, 2016 - 11:09:54 PM - Jamal Afroz Back To Top (41633)

 I get Idea from this Article. I Simply changed IP address in place of name instance for data Source.

e.g. "SQLConnString" value="Data Source= IPAddress"

It work for me.

Thanks.

Jamal

 

Tuesday, March 1, 2016 - 2:01:32 AM - Ashish Rohit Back To Top (40824)

 Thanks for this article, It solved my connection problem

 

Friday, February 12, 2016 - 2:52:04 AM - Narender Gorige Back To Top (40659)

Bull's eye. Spot on. Resolved my issue

 

Friday, February 5, 2016 - 7:01:02 AM - ramraj Back To Top (40605)

 

 

 

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) (.Net SqlClient Data Provider)

This error  comming what can i do? please halp me?

 

Thursday, January 28, 2016 - 10:26:31 AM - Ramiro Back To Top (40531)

 Hi  !

Great information !! Now i could conect perfect to my sqlserver ! Thanks !!

 

Friday, September 11, 2015 - 11:26:56 AM - Paulo Back To Top (38653)

Connection was forced to stop by an automatic Windows update requiring restart - usually Windows restore interrupted sessions but that was not the case with SQL Server - thanks again.

Friday, September 11, 2015 - 11:22:30 AM - Paulo Back To Top (38652)

Clear and straight to the point. Thanks for your help...

Wednesday, August 19, 2015 - 7:03:02 AM - Dave Johnson Back To Top (38473)

This is so good! Very clear, and very helpful. Thank you very much.

Thursday, June 18, 2015 - 9:32:56 PM - immortal Back To Top (37956)

You save the day for me. I am so happy i found this post.

Thursday, May 7, 2015 - 3:28:00 AM - MeenashiSundaram Back To Top (37114)

 

Thank you very much,

 Instead ip address i changed to localhost;its working, but why cant use ip address?

Tuesday, April 28, 2015 - 6:10:45 AM - Nektarios Back To Top (37048)

Man you just saved my life !

Thursday, March 26, 2015 - 9:41:52 AM - Mogale Back To Top (36711)

Im trying to connect to sql machine remotely , and store data created in  a different server into my Sql server machine 

Sunday, December 21, 2014 - 5:17:00 AM - Priyanka Back To Top (35712)

Thank you so much. I was struggling to connect to SQL server for more than 3 hours. Your steps helped me to connect. Thanks..

Friday, December 19, 2014 - 1:51:08 PM - balukammri Back To Top (35691)

in my case, i had a standalone server, i changed the sql server port default port 1433 in configuration manager to some number and restarted the sql serve service to take effect,i was able to connect to the sql server through management studio if i login to the server. but i was not able to connect from my local machine through sql server, i was getting the error:

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) (Microsoft SQL Server, Error: 5)

I checked and verified all the below

-Named pipes/TCP is enabled.
-Remote connections are allowed.
-Windows Firewall is off
-Created an exception for portin Windows Firewall( this was not necessary in my case as the server is in same subnet network).
-Enabled everything in SQL Server Configuration Manager.


then i chnaged back the port number to default 1433 and restarted the sql server service, and the issue got resolved and i am able to connect the sql server from my local management studio.

Wednesday, December 10, 2014 - 5:59:59 PM - goodyoneloves Back To Top (35563)

I have linked one of my SQL server, it was initially giving me errors named pipes provider could not open a connection to sql server 1326. Server is not accepting remote connections .... I just had a to add a firewall rule to allow inbound connections. 

Note: SQL browser service and named pipes might not be required. I am still able to connect to the server using local SQL authentication. Better to be secure than be sorry....:) so turn off the services you dont need.

Cheers....

Wednesday, December 3, 2014 - 11:31:58 AM - AJH Back To Top (35480)

Thank You!  I went through every step finding that step 6 was the issue.

Wednesday, October 15, 2014 - 4:15:04 AM - JLo Back To Top (34965)

THanks a lot !!

Good job ! :-)

Thursday, September 18, 2014 - 8:15:09 AM - Hari Back To Top (34588)

 In my .net web application iam using 127.0.0.1 to connect to sql server and it works fine for some time. After some time i keep noticiing errors like the below

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)

After that i noticed that the browser service is disabled. Is it possible that this is causing this error to happen. 

Thursday, August 28, 2014 - 2:29:20 AM - John Back To Top (34301)

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

Thursday, August 21, 2014 - 1:56:19 AM - srikanth rathod Back To Top (34215)

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

 

 

 

Monday, August 4, 2014 - 3:05:01 AM - La_F Back To Top (33988)

Thank you, it worked to my

Wednesday, June 25, 2014 - 12:54:08 PM - Dan Back To Top (32399)

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.

Tuesday, June 17, 2014 - 1:28:56 PM - FS Back To Top (32287)

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.

Friday, June 13, 2014 - 8:32:47 AM - Jagdish Back To Top (32229)

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

Monday, May 19, 2014 - 8:43:10 AM - Tony Foo Back To Top (30836)

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

Wednesday, April 23, 2014 - 9:43:38 AM - Amit Back To Top (30484)

 

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

Thursday, April 3, 2014 - 9:03:41 AM - Ruald Venter Back To Top (29960)

Thanks alot, step 6 solved my problem.

Friday, March 7, 2014 - 8:13:59 PM - Thirunavukkarasu Back To Top (29682)

Windows Server 2012, how do we do tthe above

Friday, February 21, 2014 - 5:45:11 AM - bhagyadeep Back To Top (29526)

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

Monday, January 6, 2014 - 4:03:17 PM - artur Back To Top (27970)

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

Wednesday, October 16, 2013 - 3:32:26 AM - shalini Back To Top (27162)

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..

Friday, September 6, 2013 - 4:09:50 AM - william Back To Top (26640)

 

great tutorial.thnx

Wednesday, August 7, 2013 - 8:59:54 AM - Kristina Back To Top (26132)

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

Tuesday, May 28, 2013 - 10:40:25 AM - DBSQL Back To Top (25167)

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!!

Thursday, May 9, 2013 - 2:24:09 PM - Sharma Back To Top (23845)

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.

Thursday, April 25, 2013 - 5:06:07 AM - Pete Back To Top (23553)

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

Pete

Wednesday, March 27, 2013 - 8:11:41 PM - Amit Back To Top (23042)

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.

Monday, February 25, 2013 - 5:52:23 AM - cadjinacou Back To Top (22395)

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

Wednesday, February 6, 2013 - 12:36:44 PM - Dinesh Back To Top (21951)

 

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.

Thursday, December 6, 2012 - 1:13:40 AM - vikas Back To Top (20771)

 

 

Realy a great quality solution thanks

Thursday, October 25, 2012 - 8:51:17 AM - Sharon Back To Top (20086)

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, October 18, 2012 - 8:26:25 AM - Jugal Back To Top (19974)

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 18, 2012 - 3:06:01 AM - sourabh Back To Top (19966)

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 ?

Friday, September 21, 2012 - 6:10:11 AM - Vaishali Back To Top (19606)

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

Monday, July 30, 2012 - 11:41:03 AM - Shubhankara Back To Top (18855)

There is no SPN errors everything is fine

Monday, July 23, 2012 - 11:17:49 AM - Jugal Back To Top (18751)

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.

 

 

 

Friday, July 20, 2012 - 4:13:17 PM - Shubhankara Back To Top (18719)

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

Wednesday, July 11, 2012 - 12:37:36 AM - lakshmi Back To Top (18431)

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.

Thursday, June 28, 2012 - 9:36:48 AM - Jugal Back To Top (18249)

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

 

Thursday, June 28, 2012 - 8:41:05 AM - Shubhankara Back To Top (18246)
  • 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.

 

Wednesday, June 27, 2012 - 4:18:48 PM - Jugal Back To Top (18230)

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

Wednesday, June 27, 2012 - 2:00:56 PM - Shubhankara Back To Top (18227)

Hi,

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

--Shubhankara

Monday, June 25, 2012 - 1:10:35 PM - Mark Back To Top (18190)

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

 

Friday, April 20, 2012 - 5:03:48 PM - Peter Back To Top (17026)

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

Thursday, February 16, 2012 - 3:52:22 PM - Renato Gonçalves Back To Top (16044)
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!

Thursday, February 16, 2012 - 3:50:05 PM - Renato Gonçalves Back To Top (16043)
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!

Monday, December 5, 2011 - 11:36:36 AM - Atul Back To Top (15293)

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

 

Monday, March 21, 2011 - 6:01:49 PM - David Pierson Back To Top (13265)

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, March 21, 2011 - 4:22:53 PM - DeWitte Back To Top (13264)

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.

 














get free sql tips
agree to terms