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
Hi,
I have the problem in connecting to my SQL server from godaddy web hosting server. My .net application is hosted in godaddy and the sql server is sitting in my office. When i try to connect my application with sql server out side godaddy, ie from debug mode or hosted in my personal computer , the connection is established and i can query the db. But when i try from godaddy (WWW.projectname.COM) the data base connection is not working. Below is the error msg :
System.Data.SqlClient.SqlException: 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: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
I tried all the available option described in the above blogs but still no luck.. Any help on this…???
Thanks
Hi,
I have the problem in connecting to my SQL server from godaddy web hosting server. My .net application is hosted in godaddy and the sql server is sitting in my office. When i try to connect my application with sql server out side godaddy, ie from debug mode or hosted in my personal computer , the connection is established and i can query the db. But when i try from godaddy (WWW.projectname.COM) the data base connection is not working. Below is the error msg :
System.Data.SqlClient.SqlException: 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: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
I tried all the available option described in the above blogs but still no luck.. Any help on this…???
Thanks
Excellent.. It worked for me. Thanks alot…
I LOVE YOU !!!
Thanks, you saved the day : )
hi..
I use windows Authentication only to connet sqlsever and my server name is
OJASWA-24031808\SQLEXPRESS .i use oledb connection and query string is “Provider=SQLOLEDB;
Data Source=OJASWA-24031808\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=databasename”.but when i debug website then error occur escape sequence is not recognised.and exception generate connection is not open.plz help me urgent.
I ran the application perfectly and sometimes not work properly(it Shows Error 40 Network pipes..).
To rectify this, I modify the steps what ever i found in different blogs. these are time consuming………………..
OMG! I loss my hair till now i didn’t get the result. watz the problem.
At-last i Found a result in another way, try this http://www. codeproject.com/KB/aspnet/LightIIS.aspx
And :-))
If u satisfied or found a result for Error 40 pls mail to me
Greetings
As always Pinal Dave is my main man, top dogg! I came here and resolved this nagging issue withing seconds.
Thank you Pinal
You are the best!
I ran asp.net application through Visual studio 2008 it doesn’t show any error. If i ran asp.net applic on IIS(http://localhost/mywebappname) it shows
“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) ”
I have only SQL server 2000 in server2003 and No sql in Client (XP), only visual studio 2008.
Please tell me whatz my mistake
Connection string, TCP , firewall settings changed but no result
Thanks In advance
finally its work ,thanks so much
regards
Dave,
Please add to your list:
An additional way to receive the error “provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:” is incorrect spelling name of the server you aretrying to connect to. I know this sounds trivial but it does produce the same error.
Hi, Please can anyone help me as I have problem connecting a cube from Microsoft visual studio 2008 to database engine.
Good Article
Hi all, we were getting a “TCP/IP Error 0:…sql server not found or host failed to respond…” and the only way to resolve it finally was to add sqlservr.exe to the server firewall from the path of the instance we were connecting to (its in the shared folder for that instance name). We spent hours trying other things suggested above but this is the one fix that finally allowed the client desktop to connect to the SQL server names instance (without having to add port explicitly to the connection string). Plus we didn’t have to make any changes to the desktop firewall. Just hope this helps others…
Many thanks for this great article, this sort of checklist saved my day. Although it must be said that microsoft didn’t exactly make this intuitive for inexperienced users..
Azure Named Pipes Problem
Got a similiar question for you, but for a very specific circumstance, most likely one to be seen more and more.
I have two SQL Azure databases, Production (Prod) and Backup (Bkup), and a Network SQL server we shall call Onsite.
I am configuring a backup strategy for my azure databases, and ultimately I am going to use sqlcmd.exe to execute a sql script file on Bkup that connects to master database and runs
DROP DATABASE DB1
GO
CREATE DATABASE DB1 AS COPY OF Prod.DB1
GO
I know the script works.
The problem I have is I get the:
“Named Pipes Provider: Could not open a connection to SQL Server [53]. Sqlcmd: Error: Microsoft SQL Server Native CLient 10.0…” when executing the Sqlcmd.exe
So this complicates things because both servers are Azure databases so I can’t configure any of the standard options you list above, other than making sure my firewall ports are open on the machine I am running the Sqlcmd.exe from, which they are.
Eventually I will have a second script that does the same thing from the Onsite server copying from the Bkup server which will allow standard maintenance plans to work against the SQL server on our network.
Any help would be appreciated.
Thanks,
Chris
Many thanks for the great article Pinal Dave.
Noah
Hi,
I am facing the follwing error because of the
ConnectionClass con=new ConnectionClass();
con.open(“Datasource=”….”,Provider=msolap;,domainname\\username,password”);
Errror: Provider could not set DATASOURCE, USERID, or PASSWORD property
please sen me ur answer to [email removed]
Godsend… thanks man
Thanks, the issue is resolved for me. Cheers !!
Hi there Pinal – we’re facing a very peculiar problem in our organization – we’re running a locally customized ERP with SQL Server 2008 as the remote database running on Windows 2008 R2 with a sonicwall firewall between the server and LAN connections – Remote connections hitting our static IP work perfectly fine but connections over LAN using the LAN IP of the server keep getting login timeout errors and runtime errors.
We’ve checked our local network to be clean of any trojans and broadcast issues – is there any SQL setting that we’re missing out on? The firewall is correctly configured with 1433 open.
Any comments?
Thanks – great blog posts!