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
Hey,
I’ve installed win2k8 R2 and MSSQL server 2k8, please be aware that you have to install SP1 to get the whole thing to work :-O
Hi,
I’m using SQL 2005 Standard Instance.
When I try to open the SQL Server Management studio it throws the below error.
———–
Cannot connect to .
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)
————
Solution I have tried:
a. Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
b. Click on Surface Area Configuration for Services and Connection
c. Select the instance that is having a problem > Database Engine > Remote Connections
d. Enable local and remote connections > ‘TCP/IP’ and ‘Named Pipes’.
Still i get the same error.
Could anyone help me out on this?
– Ramesh
Hi,
Add your server IP address & name in C:\WINDOWS\system32\drivers\etc\hosts.
Note:Open hosts with notepad & below add server IP address & name & save it.
This works for Me.
I have the SP1 already installed on the server.
Still the issue arises.
Thank you very much, this articles help me a lot.
Pinal, the screen shots are not populating, could you please fix that, Thanks
Just testing our set up on 2008 and was running in to error 40.
This was a great help.
Thanks
hi
can i connect using [servername] ONLY ? without the \SQLEXPRESS ?
im using SQL 2005
Hello Mike
Either uninstall & then reinstall the SQL server with named instance MSSQLSERVER or create an alias.
Regards,
Pinal Dave
hi,i am from china,i am poor in english,last week,i install sql 2008, when i install it ,it report some errors,but it still install success,but i connect the sql server,it report the follow(not same you ):
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 follow you :
All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.
it is can not run
it said:
the request filed or the service did not respond in a timely fashion consult then event log or other applicable error log s of deatils
i wait your apply.i in trouble……….
can you send me a mail ,thanks
Hi, have teh same problem.. accidentaly I enabled the VIA protocol and after tha refresh, the SQL Server Services was stopped.
With “SQL Server Configuration Manager”, disable then VIA in “Clients protocols” from “SQL NAtive Client” option… Its works for me.
Hi,
I want to connect to a SQL server 2008 (64 bit)
running on windows server 2008 from a windows xp professional(32 bit) machine ,which is in the same local area network.While doing that, I’m getting an 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: 53)
For help, click:
”
I’ve checked all the settings found in your post.Everything is perfect.I’ve disabled the firewall in both machines.Even then I’m getting the same error.I can see the sql server name in management studio but while connecting I’m getting the above mentioned error.
Can you please help me in resolving this?
Thanks in advance
Jithesh
With “SQL Server Configuration Manager”, disable then VIA in “Clients protocols” from “SQL NAtive Client” option… Its works for me.
ok. here’s a weird one. My code works fine in a “Website” created in Visual Studio 2008. I use C# and use LinQ to SQL to do my queries and such. Today I needed to send some emails to some clients so I created a windows app and started building it. As soon as it tries the first query I get the same error that is mentioned above (I found this page by searching on the error).
I’ve done some experimenting and I’m REALLY confused. on a web page (default.aspx) this code returns the count correctly for people who haven’t received the update email…
int iTotal = dbClientBase.NoticeAlerts.Count(n => !n.SentDate.HasValue);
But the exact same line (using the same connect string as the website app) returns the error when I run it from a windows app. In the Server Explorer, however, I can open the table and look at the data in the table.
I thought it might be an issue from switching to Windows 7, so I popped over to my Vista machine and got the same results. Works fine in a web page, errors in a windows app. I would REALLY appreciate any help or suggestions with this. It’s driving me up the wall!
Hi,
I can connect through include file in folder1 but can’t connect through other folder2 which is just aside to folder1. I’m using SQL server 2005 and connecting using IP of machine. Folder2 include file giving error “provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server”
Anyone?
Thanks in advance
Thanks that helped a lot.
I had a problem with sqlexpress.
After a lot of research i found that for sqlexpress
i need to append “localhost\sqlexpress” and not just “localhost”
Thanks a million! Was trouble-shooting a new install and didn’t realize that the browser service enabled dynamic port option. It was using 1111 instead of 1433 and causing everything to fail horribly. Noticed this issue after reading your article and it fixed my remaining problems immediately.
Thanks for the information, but can you think of any good reason an error like this would be intermittent?
Thank you, thank you, Punit and Mithun, for the reminder of the need to add /sqlexpress to the servername.
Pinal, we so appreciate all you do. Since your entry here comes up first for those searching for the error, could you consider revising your entry so that this suggestion is offered there, rather than people having to read through so many comments?
Secondarily, I would add that since I (as many, I’m sure) was interested only in connecting from Mgt Studio to SQL Server both on the same machine, some of the steps you offered (like firewall settings and enabling the other SQL services) aren’t needed–if we’re not opening the DB to outside access.
All I really needed to solve things was to add /sqlexpress, either as localhost/sqlexpress or machinename/sqlexspress. That tip, offered near the top, may be all that some need. I even went back and disabled named pipes and tcpip in config mgr, and restarted, and things still worked (running 2008 on Windows 7). That may be very helpful to note as the first thing for users only wanting to access SQL Server locally, as in development.
Keep up the great work.
Just resolved a connection problem from the client’s odbc configuration by specifying a port number in the server name:
mySQLServer\myInstance,1433
This is a strange one, making sure that I could telnet to the server port 1433, I was still not able to connect from the client. An MSDN article indicates that I should try using the above configuration string for the server name and it worked.
It appears that if one uses a named instance, the native mssql 10.x odbc client no longer assumes the default 1433 sql port for connectivity.
Alternatively,
The need to specify a default MSSQL port number at the client odbc configuration process indicates an error was made during the installation of the MSSQL 10.x client. To fix this, navigate to the following key in the registry and make sure that the REG_DWORD for the Value key is set to 1433 (the default port). If this key is not present, create one accordingly,
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SN10.x\tcp\Property1 (also see screeshot).
hello sir,
i am a newbie with data administration, and i just deployed my first application on a windows server 2008, i actually was working on my local machine as the for the development with sql server 2005 Standard edition as the datbase, to host it on the server i with sql server 2005 Enterprise edition.
i only backed up the database on my local machine and restored it on the enterprise edition of the sql 2005 on the server and evry thing seemed to be working fyne on the host , but on client systems the the application is giving the error discussed above, i av gone thru all the process on the host server, but all to no avail, please i need help urgently, can anyone help please…..
Hi,
Tyr to connect using the server ip address with port numner. For example: 11.22.33.44,1433
If multiple SQL Server instances are running on Server system then verify that SQL Browser service is running. This service dynamically assign port number to different SQL instances.
Regards,
Pinal Dave