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
A lot of thanks to you Vince and PinalDave!! Now I can connect my WinCE device to my SQLServer2005 database in my desktop PC!
:D :D :D :D
Hi sir,
lots of thanks to u…
i’m very happy when i found your site for resolved this error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: ).
Now, i can connect my sql with server very easily. the way u are using to solve this problem is very easy and under stabling to any person. spacialy by picture view & step by step solving problem that is very helpful.
Thanks & Regards
Sweta prasad
Awesome man! When I installed SQL 2008 Express locally it set the port to 4881…configuring .NET to Database is a nightmare…thanks again
Thank You so much…
It worked.. I have just used this one:
2) Enable TCP/IP in SQL Server Configuration
Hi
This post is very informative.
After following all those steps and when tried to start the service, I get the following 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.”
Hi
This is great post. Informative and with instructions easy to follow.
I got the problem after cloningto a bigger hard drive. No other problems except when I tried remoting to my SQL server. I shouldn’t have had any problems, but this post resolved it.
Thanks
Hi, thanks for all the information, it’s helpful..
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = ”;
the above may also be needed.
This is a very good and comprehensive step-by-step.
Please note the following limits with this topic and your solutions:
-Note that SSMS must be installed to go into Instance/Properties/Connections. Since I’m not installing SSMS on the client systems, I would like a way to do this via command prompt.
(Note that I DO install SQL Server Express with npenabled and tcpenabled, but sometimes it is installed with np NOT enabled, and/or tcp NOT enabled??)
-I think the port for SQL Server Express is NOT 1433. Can you verify that please? On my machine, it is 2301.
Hi there, I am having some problems getting my linked server going. I made all the correct active directory changes on the 2 servers.
I set up an SPN like so on the one server, I haven’t done anything on the other server.
C:\Program Files\Support Tools>setspn -A MSSQLSvc/CSBTESTDB:1433 LILYDALE\ServSQ
L
Registering ServicePrincipalNames for CN=ServSQL,OU=Service Accounts,OU=Users,OU
=Lilydale,DC=lilydale,DC=ads
MSSQLSvc/CSBTESTDB:1433
Updated object
and then:
C:\Program Files\Support Tools>setspn -A MSSQLSvc/dory.lilydale.ads.:1443 LILYDA
LE\ServSQL
Registering ServicePrincipalNames for CN=ServSQL,OU=Service Accounts,OU=Users,OU
=Lilydale,DC=lilydale,DC=ads
MSSQLSvc/dory.lilydale.ads.:1443
Updated object
and then here is the summary:
C:\Program Files\Support Tools>setspn -L LILYDALE\ServSQL
Registered ServicePrincipalNames for CN=ServSQL,OU=Service Accounts,OU=Users,OU=
Lilydale,DC=lilydale,DC=ads:
MSSQLSvc/dory.lilydale.ads.:1443
MSSQLSvc/CSBTESTDB:1433
MSSQLSvc/wpllilycal.lilydale.ads:1433
MSSQLSvc/CSBTestDB.lilydale.ads:1433
MSSQLSvc/894R4M1-WIN7-DK.lilydale.ads
MSSQLSvc/894R4M1-WIN7-DK.lilydale.ads:1433
MSSQLSvc/KAZOO.lilydale.ads
MSSQLSvc/KAZOO.lilydale.ads:1433
MSSQLSvc/VMWCTRSVR01.lilydale.ads:SQLEXPRESS
MSSQLSvc/Update01.lilydale.ads:1433
MSSQLSvc/lkknxfh-w7-dk.lilydale.ads:SQL2008
MSSQLSvc/lkknxfh-w7-dk.lilydale.ads:58064
MSSQLSvc/SQLSE01.lilydale.ads:51955
MSSQLSvc/SQLSE01.lilydale.ads:SQL2008
MSSQLSvc/kcna2hg-xp-dk.lilydale.ads:1433
MSSQLSvc/kcna2hg-xp-dk.lilydale.ads
MSSQLSvc/BAMBAM.lilydale.ads:1205
MSSQLSvc/BAMBAM.lilydale.ads:SQLSERVER2008
MSSQLSvc/csbtest01.lilydale.ads:1433
MSSQLSvc/sqltestcsb.lilydale.ads:1433
MSSQLSvc/sqlcsb01.lilydale.ads:1433
MSSQLSvc/SQLTEST.lilydale.ads:3178
MSSQLSvc/L3CMDA9-XP-LP.lilydale.ads:1433
I set my database to trustworthy, successfully, restarted my instance, and when I run this command I still get NTLM:
select
auth_scheme from sys.dm_exec_connections where session_id=@@SPID
When I run this command:
SELECT
* FROM OPENDATASOURCE(‘SQLNCLI’, ‘Data Source=ServerB;Integrated Security=SSPI;’ ).MASTER.dbo.syscolumns
I get this:
OLE DB provider “SQLNCLI” for linked server “(null)” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI” for linked server “(null)” returned message “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.”.
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
I followed all the steps from above and still get the same thing.
Thanks in advance!
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (.Net SqlClient Data Provider)
Server Name: HARI
Error Number: 233
Severity: 20
State: 0
For windows server 2008 r2 you have to add an inbound and outbound record to allow the port to access the server.
Thanks a lot Vince, with more people like you, this world could be better.
four computers…sitting in the backyard….precious 10 minutes…you’re trying too hard…why don’t you just drop your act and be yourself. fuckin loser.
Do you know if Windows 2008 SP1 is affecting in some way this configuration. I’m getting this error since I installed SP1 and I have checked all configurations explained here and I’m still having the problem.
Thanks.
None of the above tricked work and then….
found following at
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 JOHNDOEPCSQLEXPRESS, 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!
Step 3 did it. Thanks for all your wonderful solutions, Pinal.
System.Data.SqlClient.SqlException: 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)
gettind this error again and again i have tried all the possible things but still not working.it use to work before
plz reply me as soon as possible
Why would you need to open port 1434 to get sql database traffic? I am using my web browser as a sql browser and have the traffic going through ports 80, 5355, 137, and 138. I am also able to utilize port 22. Its because windows firewall sucks so bad that I am able to do it.
We have a firewall between our Web (Windows 2008 R2/IIS 7.5) and SQL 2008 R2 Server. The SQL server has 2 instances, default (port 1433) and named (port 1435, not dynamic). FW is configured to allow communication between both servers on tcp port 1433 and 1435. Web server can initiate communication. SQL 2008 Management Studio is installed on the Web Server. From the web server, we can connect to both instances of SQL using the Management Studio. However, a SQL alias does not work for either instance. Error code: “A network-related or instance-specific error occurred while establishing a connection to SQL Server. … Error 53.”
Windows FW is off on both servers. We are using TCP/IP; protocol is enabled on both servers; alias set to use TCP/IP. Alias is using IP of SQL server (not the server name) and for the named instance we use “\” between server name and instance name. From web server, telnet to sql server IP 1433 and 1435 works. Remote connections allowed. Does using a SQL alias require additional ports / other settings?
Thanks,
Eileen