By: | Updated: 2022-09-16 | Comments (86) | Related: 1 |
2 | 3 | More > SQL Server Configurations
Please do not scroll away - stay informed.
| Dear Database Professional, Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials? Let us help you stay informed and learn something new each day. Click here to keep informed. Thank you, Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
|
ProblemSometimes 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. SolutionThere 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 boxMake 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 runningMake 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 ManagerYou 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 commandFrom 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 runningCheck 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 nameMake 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 ServerCheck 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 enabledCheck 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 enabledCheck 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 usingLocally 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 registeredIf
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
Related ArticlesPopular ArticlesAbout the author 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 Article Last Updated: 2022-09-16
Comments For This Article
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 Jugal Shah ! 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.
|
Why is my SQL Server not connecting?
The following common scenarios can cause connectivity problems: Incorrect IP address for the Server field. Make sure that the IP address matches the entry in the SQL Server error log file. Incorrect server name in the Server field.
How do I fix ODBC connection failed in SQL Server?
Resolving The Problem. When you create the ODBC connection to SQL server, select the option of With SQL Server authentication using a login ID and password entered by the user. Also, confirm that in Microsoft Access, the Machine Data Source and System Data Source has been used to create the ODBC connection.
Can't connect to SQL Server instance remotely?
Right-click on your server name and click 'Properties'. Go to the Security page for Server Authentication, and select 'SQL Server and Windows Authentication' mode. Then, go to the Connections page and ensure that "Allow remote connections to this server" is checked, and click OK.
How do I fix Cannot connect to server?
What To Do When You Cannot Connect To The Company Server. Restart Your Computer. ... . Follow the Error Messages. ... . Identify Where the Shared Drive is Hosted. ... . Permissions. ... . Look For What Might Be Different. ... . Partner With Electric..
|