Regular readers of my blog are aware of the fact that I have written about this subject umpteen times earlier, and every time I have spoken about a new issue related to it. Few days ago, I had redone my local home network. I have LAN setup with wireless router connected with my four computers, two mobile devices, one printer and one VOIP solution. I had also formatted my primary computer and clean installed SQL Server 2008 into it. Yesterday, incidentally, I was sitting in my yard trying to connect SQL Server located in home office and suddenly I stumbled upon the following error. Finding the solution was the most infuriating part as it consumed my precious 10 minutes.
Let us look at few of the common errors received:
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)
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)
I eventually found the resolution to this error on this blog itself; so I was saved from hunting for the solution. I am sure there are a number of developers who had previously fixed this error while installing SQL Server 2008 or SQL Server 2005 but in due course forgot the right solution to this error. This is just a quick refresher. Make sure to bookmark this as you never know when you would need this solution.
Let us check into the steps to resolve this error.
1) SQL Server should be up and running.
Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.
In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network.
I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.
2) Enable TCP/IP in SQL Server Configuration
When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.
Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP
Right Click on TCP/IP >> Click on Enable
You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.
3) Open Port in Windows Firewall
Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running by firewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser should also be added to the exception, as described in Step 7.
Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions >> Add Port
Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP
4) Enable Remote Connection
Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.
Right click on the server node and select Properties.
Go to Left Tab of Connections and check “Allow remote connections to this server”
5) Enable SQL Server Browser Service
If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.
Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser
Right Click on SQL Server Browser >> Click on Enable
6) Create exception of sqlbrowser.exe in Firewall
As elucidated in Step 6, sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to add exception for the same in windows firewall.
Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and create the exception of the file in Firewall, as delineated in Step 3.
7) Recreate Alias
It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply create alias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.
Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases
Delete the alias that is giving problem and recreate it with identical parameters.
I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.
Watch SQL in Sixty Seconds video to Resolve Connection Error:
[youtube=http://www.youtube.com/watch?v=1k00z82u4OI]
Reference : Pinal Dave (https://blog.sqlauthority.com)
658 Comments. Leave new
Thanks, Dave….You bailed me out again!!!
Keep up the great work!!
Thanks a lot! I solved the problem and I can make a practice while watch Sudafrica 2010 at home =D
my SQL Server Browser was not running and I had to start it!!
I found this error and got your post as first result in search and solved the error. Thanks for sharing…
Also worth checking is that you have enabled both SQL Server and Windows Authentication.
A daft one that got me for about 4 hours !
Hi,
Thank you so much for this perfect article..Because this article save my time..
Best Regards, Ugur
Dave,
You’re blogging on SQL Server is great. This article, like many before it, has really helped me out. Thank you!
ArizonaRunner
Thanks For Above Setting I done it
Thanks For Above Setting I done it
But Currently problem is occurring that Every one on network can access my sql server but i can’t access other servers so if there any clues to setting then please reply
I am using SQL server 2008 and microsoft server 2008
Thanks…
Is there anyone who tell me:
How to connect two systems using sqlserver2005 for the purpose of transfering records from one database to another database….
Please tell me…..
Hi Pinal,
I am new to SQL, but using SQL server 2005. What if when i do the command prompt, I get no servers. What do I do please?
Bravo! Very well done sir. Adding the sqlbrowser exception for named instances is what I needed and is what other tutorials had left out. Thank you.
this is quite tricky.
after i have done the procedures, the connection works only if i specify the IP adrress of the server in the server name.
if i use the , i got the same error..
the same happens if i use telnet..
what seems to be the problem?
*** SOLUTION *** IT WORKED !!!!!
Just add Comma ( , ) plus Port # in Linked server Name field while adding a linked server. No space in between
Example: SQLServer,1531
Note 1531 may NOT the default port for your SQL Server, so check with DBA first.
I just found that Colon ( : ) does work for port # in SQL server 2005 but comma does.
hi!
what’s with the Linked server name?
can you explain how to do this?
thank you very muchhhhhhhhhh :)
i can not sql server over network. i was read your document and i was close firewall and it run :)
I am trying to install BCM 2010. It fails. Bottom of log says “Void DetachV1V2Databases(): [BCMHook] SqlException thrown: 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)” I am not a pro. I checked and have SQL servers 2005 & 2008 on my XP machine (32 bit). On both if i try to run configuration manager i get the message, “Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration manager. Invalid class [0x80041010]” I have tried many solutions from internet searches. none work. exceptions on firewall, mofcomp, winmgmt /resyncperf, winmgmt /clearadap, etc. I am lost and need a fix in layman’s terms.
Hi Everyone
Watch out for the 64bit operating systems!
If your ASP.net application pool is set to “enable 32bit applications” (under advanced settings) then your SQL Server Alias under SQL Server Configuration Manager must also be setup for the 32bit instance (SQL Native Client 10.0 Configuration 32 bit).
Your TCP/IP and Named Pipes protocols must also be enabled here.
I hope this helps.
You forget a very common cause:
Using normal slash instead of back slash in the connection string.
localhost/SQLEXPRESS != localhost\SQLEXPRESS
The number of times I found the solution to my problems on your blog is countless. You are the boss.
just wanted to help someone out that might be ready to pull their hair out over this error at top of this page and others like it, if you have went through and completed all of the above steps and sifted through other websites but still getting this error, and you have checked, re checked, and checked your installation and settings again and again, get ready, here is the answer for probably 90% of you out there:
pull up sql server management studio once more, (i know, i know, this time it will work, i promise), in the Server name: field, it probably says SQLEXPRESS, and thats it, but this time type the name of your computer then a backslash in front of sqlexpress, so that it reads something like JOHNDOEPC\SQLEXPRESS, then with everything else filled in appropriately click Connect, and, Voila!
i do not know why M$ did not make this more apparent for its end users, but they should have, this took me a long time to figure this out for myself after having gone through more than a dozen websites offering remedies none of which worked…
oh well, enjoy!
Thanks yaar!