Originally posted by 09520548

Dạ cám ơn anh, sau một thời gian mò mẫm thì đã tìm ra lỗi rồi ạ. Cái này do cấu hình trong SQL Server

chưa config cho SQL nhận thêm TCP/IP protocol hả ?

Bùm, Maria Ozawa Bin Laden, chuyên gia cưa bom hàng đầu Việt Nam Bùm, Maria Ozawa Bin Laden, chuyên gia cưa bom hàng đầu Việt Nam Bùm, Maria Ozawa Bin Laden, chuyên gia cưa bom hàng đầu Việt Nam Bùm, Maria Ozawa Bin Laden, chuyên gia cưa bom hàng đầu Việt Nam ...

By: | Updated: 2023-10-31 | | 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.

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

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.

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.

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

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.

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.

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

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

About the author

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 (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 (91432) Thanks for the checklist :) works well Tuesday, January 24, 2023 - 4:04:21 AM - Andre (90846) Super great checklist... Thx. It solved my problem. Friday, July 22, 2022 - 5:17:11 PM - Demosthenes (90306) Great article! Thank you very much. Very helpful. Wednesday, July 13, 2022 - 4:25:18 AM - Ranjith (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 (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 (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 (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 (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 (88857) Fixed my problem! Very clear, very detailed instructions. Thank you

Tuesday, February 9, 2021 - 9:40:00 PM - Glenn Orlando Pringle (88202) Thanks!!! You helped me figure out my issue Tuesday, February 2, 2021 - 10:21:20 PM - Dominic (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. (87994) Awesome! You resolved my issue! Monday, November 16, 2020 - 7:54:13 AM - Vanitha Sundaram (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 (86481) SQL Server Browser was the problem for me. Saturday, July 18, 2020 - 3:51:53 PM - Bjorgen Eatinger (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 (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 (84683)

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

Thank you

Tuesday, January 28, 2020 - 7:58:11 PM - Linda K. (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 (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 (79076)

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

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

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

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

Thanks for the detailed steps issue resolved after enabling SQL browser

Friday, November 10, 2017 - 10:28:45 PM - mrduncle (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 (65953)

Clear, understandable

Thanks!

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

Step 5 worked, thank you so much

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

Thank you for this really helpful cookbook!

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

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

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

Thank you man, you saved my night.

Thursday, June 9, 2016 - 8:20:59 AM - Atul (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 (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 (40824)

Thanks for this article, It solved my connection problem

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

Bull's eye. Spot on. Resolved my issue

Friday, February 5, 2016 - 7:01:02 AM - ramraj (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 (40531)

Hi Jugal Shah !

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

Friday, September 11, 2015 - 11:26:56 AM - Paulo (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 (38652)

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

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

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

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

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

Thursday, May 7, 2015 - 3:28:00 AM - MeenashiSundaram (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 (37048)

Man you just saved my life !

Thursday, March 26, 2015 - 9:41:52 AM - Mogale (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 (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 (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 (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 (35480)

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

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

THanks a lot !!

Good job ! :-)

Thursday, September 18, 2014 - 8:15:09 AM - Hari (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 (34301)

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

Thursday, August 21, 2014 - 1:56:19 AM - srikanth rathod (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 (33988)

Thank you, it worked to my

Wednesday, June 25, 2014 - 12:54:08 PM - Dan (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 (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 (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 (30836)

Excellent list. My problem was with

6. The TCP/IP port was blank.

Wednesday, April 23, 2014 - 9:43:38 AM - Amit (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 (29960)

Thanks alot, step 6 solved my problem.

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

Windows Server 2012, how do we do tthe above

Friday, February 21, 2014 - 5:45:11 AM - bhagyadeep (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 (27970)

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

Wednesday, October 16, 2013 - 3:32:26 AM - shalini (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 (26640)

great tutorial.thnx

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

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

Tuesday, May 28, 2013 - 10:40:25 AM - DBSQL (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 (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 (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 (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 (22395)

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

Wednesday, February 6, 2013 - 12:36:44 PM - Dinesh (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 (20771)

Realy a great quality solution thanks

Thursday, October 25, 2012 - 8:51:17 AM - Sharon (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 (19974)

Sorabh,

First you have to let me know the output of all the above steps. You can do the TELNET 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 (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 (19606)

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

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

There is no SPN errors everything is fine

Monday, July 23, 2012 - 11:17:49 AM - Jugal (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 (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 (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 (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 (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 (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 (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 (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 (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 (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 (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 (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 (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 (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 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.