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
Found the issue. We were use the 64 bit version of SQL Native Client 10.0 Configuration; works with 32 bit.
Hi pinal,
I have VPS of window 2008 and have installed sql server 2008 express r2 on it. I got following error many times a day “|80004005|Cannot_open_database__sds__requested_by_the_login._The_login_failed.” in my iis log. i am using classic asp for my website and i generated new db user to connect.
After getting above error i modified connectionstring in ASP script to connect database using “sa”. after this modification i was getting “|80004005|Shared_Memory_Provider:_Timeout_error”. I was enabled only Shared Memory Protocol.
After getting above error i was enabled all protocols (Named Pipe and TCP) but still on same. then i disabled Shared Memory Protocol now i’m getting this error. “80004005|Named_Pipes_Provider:_Timeout_error_” as well as “|80004005|Cannot_open_database__sds__requested_by_the_login._The_login_failed.”
can you please help me to resolve this error.
Thanks & Regards
Thanks ,pinal…
Very small but very important thing….
I connect SQL server 2008 with Microsoft SQL server Management studio it was connect with using database engine server authentication it was connect. I was use this database connection in local site it was working perfect but when we use this same site online it was some time database connected and sometime not connected.
Please help me to solve this problem..
Hi,
I am working on windows service (Vb.net), service get started at machine start up. But SQLserver takes some time to start. My services using SQL server connections and failed at start up. After some time it is working fine.
I used ServiceController for checking SQL server is running or not, but new problem arised …machine is not responsding..
give ur comments
This post has been very useful, but anyway, doing all that, couldn’t get to a solution.
The problem was that I had 2 instances, a named one first, a default then. I removed the named one, and could not access to the default by TCP.
The problem was that, somehow, sql was not listening on port 1433 (even that in the configuration said so, netstat -a made me realise). Never believe in Windows.
So the solution was change the port to something, restart service, then back to 1433, and restart service again.
Now it’s working again, after trying all.
All the best.
Thanks for this Pinal.
hey buddy..
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)
the above error has been solved by the first method. i hav followed it and it worked 4 me. However when i acess my database on the server machine it works but when i access it on the client it gives the same error as above.. pleaasssssssssssssssee help me out ASAP.. tired of being tortured by my teacher.. plleeeeeeasse reply asap..
hi, did u find the solution of this type of error?
i am also having the same issue.
Is your error solved?
if yes means please help me out
hello sentil,
yes i solved.
First of all run ur application through local database and tel me the status! if its working/not.
Thank for this
Mr Dave,
I had errors in trying to make my SQL work too. My problem is that I couldn’t even do step 1 / run my SQL Server (MSSQLSERVER). I get this error: The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
need your help. thanks!
This solution worked for me on a fresh Windows Server 2008 R2 x64 + Sql Server 2008 installation:
netsh advfirewall firewall add rule name=”SQLPort” dir=in act
ion=allow protocol=TCP localport=1433
After this I could connect from XP with the SQL Server Management Studio to the database.
I had an issue with connecting to the SQL server on my local machine and after reading the solution at the top of this page I got to the Alias part I didnt delete and replace mine but I did look at the Alias Port and it was blank so I typed in 1433 and YeeeeeeeeeeHaaaaaaaaaa!!!!!
I’m in! thanx to all on here keep posting to help others..
Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases
then Right click on the Alias name in right panel select properties and enter 1433 in the Port
Simplesss when you know how…lol
Cheers,
Tex
Thank you much for posting this. Turns out that I could not log in because SQL Server services was not running and I had just started up my computer.
Thank you once again and I’m surely going to bookmark this.
Great Tutorial,
just remark as “Vince” says that is important to check each time on TCP/IP protocol -> tab IP address
if in the sections IP1 /IP2 … IPall the TCP Port is setted to 1433 (default) or containt the right value that you are going to use
Pulsarstar
Another very common issue people have is when the default port has been changed for the named instance. You must connect to the named instance and define the port ie. SERVERNAME\INSTANCENAME,PORT.
Happy hunting.
You can try with following solution also:
Just Turn Off the Firewall of the Server machine.
Hi: I also would add case when SQL Server were installed in windows Authentication mode then following steps should applied:
1. Login with windows authentication.
2. Right click on the Instance name – Server Properties
3. Security page
4. Set the Server Authentication – ‘SQL Server and Windows Authentication mode’
5. Restart the SQL Service.
Regards,
Thank you so much..your blogs is really very helpful. Actually i had a trouble in my sql server but to read your blog i find out my solutions..thanks a ton..:)
Dude this help me, thanks a lot man
That so great, Thanks so much.
Regard!