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
Thank you Thank you! Need to enable Pipes and TCP/ip! THANK YOU!!!
It’s the little things that P$%$ me off. New SQL 2008 installed on the same server as the application was experiencing the same error message for sa login.
Firewall was on, which kept me from seeing another message that sa was disabled.
Thank you! You are genius! : )
This is one that really helps with my case (SQL Server 2008 SP1 on XP x64).
I verified all the steps that I did, and compare with the above steps, and figure out my problem – the tcpip protocol is OFF!
Thanks
Good points raised here. I am grateful to you for that, however you deserve more thanks than that. I suffer from color blindness (deuteranopia in my case). I mainly use Opera browser (no idea if that changes anything), and a considerabland a number of web sites are challenging to comprehend thanks to a careless range of colours used. However, here, as the range of colors is good, the design is extremely tidy and pleasant to comprehend. I don’t know whether it was a premeditated and conscious undertaking, or just the ‘luck of the draw’, but I still thank you.
Had the same problem today and wasn’t fixed by the above suggestions. My solution maybe worth adding into main text of this blog? Anyway in my connection strings, or when connecting to another server I use the server names not the ip addresses. Somehow the ip to comp name mappings were lost on my network (?) and was therefore getting this error message. The fix was to modify the hosts file and manually specify this mapping.
The hosts file can be found here:
C:\Windows\System32\drivers\etc
with windows 7/vista you will need to first remove the read-only attribute on the file (right click file – properties). Open the file with administrator privileges (right click notepad – run as administrator then navigate to file: C:\Windows\System32\drivers\etc\hosts)
then add mappings i.e.,
192.168.0.176 SQLSERVERCOMPNAME
hey presto, connection allowed!
I am sure a networking guru can explain why my mappings suddenly dissapeared? Strangely coincides with a windows update!?
thank u
u r great
Hi, I solved the problem – actually SQL Server Express has to set up locally
Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager
SQL Server 2005 Services then SQL Server Express properties on log on tab Buil-in account has to be “Local System”. Then test your page it will work.
bye
Bambabu
THANKS !
:-)
Thank You so much that was really helpful
Thank you
Hi,
I am learning Reporting Services and I downloaded the AdventureWorks Sample database and installed then on the server. I deplyed the solution and when I try out the Reports, I get the following error (for all reports):
An error occurred during client rendering.
An error has occurred during report processing.
Cannot create a connection to data source ‘AdventureWorks2008’.
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)
Please help me so that I can overcome this problem. I have tried all your above steps and still it’s not working.. Your help is really appreciated.
Note: The comment from Vince talks about the TCP/IP ports and he said that we have to change it to 1433, I am a bit confused which port has to be changed, either the loopback adapter or the machine ip. Please clarify.
Your quick response is very much appreciated..
-TK
Hi
i am not able to connect to sql 2000 remotely in win 2003 server. I am using vwd 2008 express in xp sp2. I configured everything mentioned in the above postings. Plz help. Error is like —
–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)—I am a new be. Plz hel.
thanks for the solution.
i have been searching the solution for past 2 days.
once again thanking you
Hi Pinal,
This is one of the site which gets listed 70% of the times when you do a search for specific problem in SQL Server. Thanks for all the knowledge share.
Regards,
Phani.
HALLELUJAH!!! I struggled with this off and on for months before finding these awesome directions. Step-by-step and it worked the first try.
THANK YOU!!!!
Hitting my head against the wall and found your article. THANK YOU!!!!
Many thanks to Pinal and Eric!
I had installed Visual Studio 2010 and SqlServer 2008 R2 on to a Windows 7 machine. Management Studio 2008 could connect, but some apps could not (e.g. aspnet_regsql).
My problems were solved by combination of running through Pinal’s check list, AND as inspired by Eric’s post, changing the credentials of the SQL Server Browser Service to be NETWORK SERVICE (rather than LOCAL SERVICE)
I did not run across this problem on a similar setup under Windows Server 2010.
Regards,
Art
Great job. works for me. thank you very much…
@Vince: Thank you, sir. After 5 hours of running down all the official MS advice and coming up with nothing, it turned out to be IPAll was set for dynamic ports. After setting it to listen on 1433, all is well.
I only wish I’d gone to the blogs earlier.
Its so helpful.
Thank you very much for this blog.
Thanks
This worked for me
“To assign a TCP/IP port number to the SQL Server Database Engine
In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for , and then double-click TCP/IP.
In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.
If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.”