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
I am not able to reproduce step 4 Enable Remote connection.
For me its giving error as shown below. PLEASE HELP ME…
TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
——————————
Failed to retrieve data for this request. (SqlManagerUI)
For help, click:
——————————
Failed to connect to server JAGAN-HPSQLEXPRESS. (Microsoft.SqlServer.ConnectionInfo)
——————————
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.) (Microsoft SQL Server, Error: 233)
For help, click:
——————————
BUTTONS:
OK
——————————
Thanks Pinal!
Darn Windows Firewall.
I fixed the error by doing a full format on the hard-drive.
When my system crashed I reinstalled the OS and SQL Server, but I only did a quick format before reinstalling everything.
I’m guessing that some bit and pieces of the driver software were floating around because from that point on I could not get SQL Server to function.
If necessary add this solution to your blog.
Thanks for your response,
Mark McCumber
Sent from Windows Mail
Hi All,
SQL Server Name : ECARE432
SQL Instance Name : SQLEXPRESS
App.Config mentioned like this:
This is Arun from Chennai. I have developed my first application in WPF (C#) with VS 2008 & SQL Server 2008. It works fine in
my system. After deployment, it wont works in other systems. It shows the following error message.
provider named pipes provider error 40 – could not open a connection to sql server 2008
I have gone through the google and done the following steps but no use.
1. Configuration Tools->SQL Server Configuration Manager->SQL Native Client Configration
Aliases->
Alias Name -> ECARE432,1433
Port Number -> 1433
Protocol -> TCP/IP
Server Name -> ECARE432
2. TCP/IP is enabled. Protocols -> TCP/IP Properties ->
IP1 ->
Acive – Yes
Enabled – Yes
IP Address – My system IP address
TCP Dynamic Ports – Blank
TCP Port – 1433
IP2 ->
Acive – Yes
Enabled – Yes
IP Address – 127.0.0.1
TCP Dynamic Ports – Blank
TCP Port – 1433
IPALL ->
TCP Dynamic Ports – Blank
TCP Port – 1433
3. SQL Server Browser is running.
4. Server Name is correct. No typo mismatch.
5. Client machine is able to ping my machine. (PING ECARE432 is working)
6. TCP 1433 and UDP 1434 Exception added to Firewall. Also Turned off the Firewall in both my system and the client system.
7. sqlbrowser.exe is added to the Firewall Exception List.
8. Allow Remote Connections enabled under Connections in SQL Server Properties.
9. Visual Studio–>Tools–>Options–>Database Tools–>Data Connections–>
Changed “SQL Server Instance Name” from “sqlexpress” to blank.
Please guide me how to fix this issue.
all the above failed, also you can add server IP address and server name in host file which is in drivers folder, this has to be done in client system from where you are trying to connect
C:\Windows\System32\drivers
add the details like
192.168.XX.XX SERVERNAME
Hello friends,
I am using window application and i have used crystal reports in it and i have used odbc for connection, and when i developed it i have sqlserver 7 it is nt autheticating server no username password required.
now i want to use that application on sqlserver 2008r2,
i have windows server 2003 where my db is located,
but from client pc i am not able to create ODBC through
NT authenticate. i am getting error like
Connection filed :
SqlState:’28000′
sql server error:18452
login is from an untrusted domain and cannot be used with windows authentication.
In addition, if you are trying to connect to a SQL Server Instance other than default, you need to open the port used by that instance.
In case its a dynamic port (which is default), you need to add an Inbound Firewall rule for each instance’s sqlservr.exe from “Program Files\Microsoft SQL Server\MSSQL11.InstanceName\MSSQL\Binn” location.
Ensure this is inline with your organisation’s security regime as you might only open specific ports for each instance. In this case you need to assign a unique port to each SQL Server Instance.
I have seen the same error on several of my computers but i use only a local database created with SQL 2008 Express. The interesting is that it happens simply by chance and not always.
Thanks. It’s help
Hi Pinal,
I have tried all above steps but still same issue, any other tips?
I had a similar error and after applying the steps above. My system is up and runing… How cool is that..
Thanks alot you are great you solved my problem :)
Easy way is on your server name type your computer name back slash server name and it will work like PC1\MYSQLSERVER
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)
Hi,
Background:
SQL Enterprice 2008 R2, Visual Studio 2010 running Win 7 Ent 64 bit, App in Asp.Net
SQLconnStr = “Server=MYCOMPUTER\SQLEXPRESS;Initial Catalog=APPDB;Integrated Security=SSPI”
Problem:
“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) ”
Troubleshooting:
All network connections are enabled in the Server – named pipe, tcp, and shared memory.
Ran it with Win Firewall turned off – still the problem persist.
Ensure Win Firewall has port 1433 enabled – problem still persist.
Ping server – ok, sqlcmd -L – return the right server\instance
SQL Management Studio – connects with no problem
Ensure remote connection is enabled on the server
Install the Enterprice 2008 on another machine running Win 8, ran same App – No problem
Very puzzled, can someone shed some light? Thanks in advance!
Reply
First turn on SQL server browser !
You rule! Thank you for the help yet again!
Thank you! Windows Firewall wouldn’t allow traffic through the port until I added an exception.
I have found that testing the linked server from a remote connection of SSMS doesn’t work ether, you have to be logged on to the server.
I solved this problem by using Management Studio, but specify a port name. eg in the Server name = 192.0.12.11, 1433
Hello!
I recently setup a brand new Server 2008 R2 and discovered that simply switching on Network Discovery fixed this issue.