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 very much, this articles help me a lot….thanx a lot again..:)
Hi,
This is really great help me to solve the problem when This error occur.
But I have One More problems that
Other Network SQL server can access me but i cannot access their server AND I have been Set all the settings that are given above.
So Please Help me out for this problem
Thanks You.
Amit Tank
Problem: A connection was successfully established with the server, but then an error occured during pre-login handshake. 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: 0 – No process is on the other end of the pipe.)
Solution: By Default SQL Server uses Named Pipes. But if that is not enabled then the Client Utility such as Visual Studio OR SQL Server Management Studio will fail to connect.
To resolve this follow the steps for configuring SQL Client Configuration Utility correctly
1) Go to Start-> Run
2) Type the command -> cliconfg
3) Go to the Tab ‘General’ and left Nav to Disabled Protocols
4) Select the ones that are supported mostly TCP/IP and move it to Right Side under Enabled Protocols
5) Then go to Alias and Select Add
6) Select TCP/IP under Network Libraries.
7) Under Server Name put Server IP e.g. 172.21.40.104
7) Uncheck ‘Dynamically Determine Port’ and supply the port explicitly
8) Supply the Server Name in the Alias
9) Apply the changes.
10) Restart Visual Studio or SQL Server management Studio
That should solve the problem
Hi Thanks for above post.
Actually i have tried all solutions above but problem with my case is very strange
i am trying to connect to our website live database (ms sql 2005 server) but the strange thing is that this is connecting with all other database servers (different servers with different database)
I am getting problem with only this database server.
i can even remote desktop this database server but can not connect through management studio.
Any solution on this would be highly appreciated.
Regards,
Qasim Raza
Pinal – you always amaze me with your thoroughness and attention to detail. Exactly what I need, and laid out so simple that even I can understand it, do it and explain it.
Thanks so much!
Hi,
Please guide where I am doing wrong:
My database reside on a separate server and my online website need to access that server to fatch some data. The server is in my office and connected with Internet.
I have configure the SQL server 2005 on the server to allow remote connections. Enable TCP/IP and named pipeline. The port is 1433. And done everything as stated in this post.
I have tried following connectionstrings.
1. add name=”DatabaseConnectionString” connectionString=”Data Source=nt1111;Initial Catalog=testdatabase;Persist Security Info=True;User ID=test;Password=***” providerName=”System.Data.SqlClient”
2. add name=”DatabaseConnectionString” connectionString=”Data Source=nt1111;Initial Catalog=testdatabase; User ID=test;Password= “***”; User Instance=False” providerName=”System.Data.SqlClient”
3. The same as 2 but with IP add name=”DatabaseConnectionString” connectionString=”Data Source=999.99.9.99;Initial Catalog=testdatabase; User ID=test;Password= “***”; User Instance=False” providerName=”System.Data.SqlClient”
But, I get following 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: TCP Provider, error: 0 – The requested name is valid, but no data of the requested type was found.)
Please guide, I have already spent many hours on it :(
/Danish
Thanks Pinal. your article helped me in resolving my issue!
on windows server 2008 64 bit version ,i instal 32 bit Sql server 2005,after that i instal 32 bit vs 2008 ,now can i configure vs 32-bit to server 32-bit ,and i didnot found sql instance in the firewall settings ,can anybody help ……me
Hello to all,,,,
Is there anyone who can tell me how to generate a alert message from sql server2005 whenever i insert a new record in my table…..
here i m not saying about the error message…..
Here i m inserting the correct data and my that data is inserted in my table also now after insertion i want a message that one record is inserted in your table in a message box thru sql server….
but not by vs..
Hello,
I had 2 bog problems and i just read 2 posts of yours and both my big problems that I could never solved myself, were solved.
Thanks a lot
Thanks!
This did the trick!
Sir, you saved my life. God bless you. I did the step 3, open port in windows firewall and it worked.
Thank you
Thanks, very usefull
Thanks a lot. You are mi hero.
my firewall and networking icon disable sometime after installing sqlserver2008 express on my windowsxp pro sp3.
Provide a solution to thsi problem.my network icon diaplay after sometime.
Hi pinaldave,
Your explanation is wonderful for a lame user like me. Please answer my question as I’ve been trying for several days to install MS SQL Server 2008 on windows 7 home premium. I have downloaded the MS SQL Server 2008 & visual studios SP1 from
http://www.microsoft.com/en-us/download/details.aspx?id=7593
and installed it on my laptop. I could not connect as I kept receiving the Network error 26.
I noticed that the instance name and ID is not created after the installation.
I follwed some instructions on the page and enabled the ports on the firewall.
The protocol list is empty in the SQl server configuration manager.
Please guide me from here. Waiting for your answers.
Regards,
Nithya
It’s me again..
I tried to open the SQl server configuration manager again and can now see the protocols listed and the TCP/IP is enabled.
I cannot find any servers listed in the command prompt. How do I start from here??
regards,
Nithya
hello every body
i did an application with c# to connect to sql server and it worked well on my local machine (to connect to my sql server 2005 on my computer),
but now i want to connect to a remote sql server 2005 which is on another computer, but it failed to connect through the application and even the sql server management studio express 2005 and i failed to connect through visual studio 2008 servers part.
oh i went banana, i did every thing here and i did what i found in all posts i read.
-i made sure that the firewall give authority to my application & sql server to connect without blocking,
-added the port to the firewall
-added the /sqlexpress to server string
-added /sqlexpress,port no to the server string
-used the connection string the provider gave it to me it’s like
SqlConnection (.NET):
Data Source=db_name.db.6236289.hostedresource.com; Initial Catalog=db_name; User ID=user_id; Password=’your password’;
But also it is useless
-quite sure that my sql server tcp/ip and named pipeare enabled.
oh nooo, i don’t know what can i do???? please any one help me.
hint : the only way allowed me to connect to that sql server was the “database publishing wizard” when i tried to script my local db and to put it on the remote server, it worked well and created the tables on the remote server,
But when i tried to make the oppsite (to script the db that is remotely it failed and i get the same error)
Error details:
“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 – No such host is known.) (.Net SqlClient Data Provider)”
again me
there is something i made now
i made a web page connect to sql server with the same connection string and it worked when i uploaded to my website,
But
locally it doesn’t work, may be that means that the problem in my computer especially that my sql management studio couldn’t connect also to that remote server?!!!,
HINT : my application is a “windows forms application”
please please help me, it’s urgently.
I rebooted the server and it worked. I think it was caused by an OS update or patch.