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 FOR YOUR HELP
Hello Pinal,
I have enabled Named Pipes and restarted service – changed SQL server to Network service.
Still unable to access from other systems
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)
The network path was not found
Please help.
Thanks,
Priya
Hi Pinal,
I have this error.
SQL Server Error: 10061
I can’t login to the instance. After I trying to login SQL Server by giving user name and PW, SQL Server service is stopped.
Appreciate it if you could help me.
Thanks,
Peiris
Pinal, thanks a lot for your great tips. Unfortunately I was not able to resolve notorious . (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) error when trying to connect SQL Server 2014 Data Quality Client by specifying defaulted “(LOCAL)” option. I spent almost two evenings following all your steps and even going beyond them. I have two instantiates of SQL Server Services on my local machine which is not connected to any network. One was installed during SQL Server 2012 Express edition installation and the second one is SQL Server 2014 Service instance installed later when installed SQL Enterprise Edition. Both of the instances running well in SSMS.
I’ve enabled the tow server instances mentioned in my previous comment, added slq serve browser to firewall allowed remote connection to the server , set up a new port for SQL 1433, but still getting the same error. SSMS Error log does not have any related to the SQL Server Data Quality Client connection issue. I’m using windows Authentication when connect to the Instances using SSMS as no remote networks are setup.
Thanks alot , this helped me. I found the service was stopped so i set to Run manually
Pinal, the error message that I’m still getting is this:
System.Data.SqlClient.SqlException (0x80131904): 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) —> System.ComponentModel.Win32Exception (0x80004005): The system cannot find the file specified
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Ssdqs.Proxy.Database.DBAccessClient..ctor(IDictionary`2 connectionData)
at Microsoft.Ssdqs.Proxy.Database.DatabaseMetadataUtility..ctor(String instanceName, Boolean useEncryption)
at Microsoft.Ssdqs.Studio.ViewModels.ViewModels.Application.LogOnWindowViewModel.ConnectToServer()
at Microsoft.Ssdqs.Studio.ViewModels.Utilities.UIAsynchronousRelayAction.Action()
at Microsoft.Ssdqs.Studio.ViewModels.Utilities.UIAsynchronousAction.ActionExecuter()
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:2,State:0,Class:20
Also for the SQL Server Instance in SQL Configuration Manager under Properties on the Log On tab I changed setting to the Built-In account selecting Local System, but it made no difference.
I was able to resolve the (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) error. The problem was with one of my instances that I tried co connect. Please make sure you:
1. You have installed SQL Server Data Quality Server installed on the your SQL Server Instance that you want to connect using SQL Server Data Quality Client.
If you are not sure about it, please connect to the SQL Server Instance using SSMS and expand databases folder; if you see the three database below it means you are good to go, if not follow simply find SQL Server Data Quality Server Installer app under SQL Server folder and double click to launch the command line installation and then carefully follow to command line instructions. Also this video can be very handy:
[link removed as it was breaking the comment’s html]
2. If you have only one default instance of SQL Server installed that you can you the “(LOCAL)” as the server name when connecting SQL Server Data Quality Client; otherwise, if you have multiple SQL Server instances installed refer them by the server name (host name/instance name). TO avoid typos connect SSMS to the instance you want SQL Server Data Quality Client get connected to and type the following query in the SSMS query editor window: SELECT @@SERVERNAME and then copy returned server name into the SQL Server Data Quality Client Server Name field.
Thanks i have solved the error. But it comes everytime my server restarts. what could be the permanent solution?
Thank you for your article it helps a lot!
Or maybe just open Services and start Sql Server.
Thank you very much. In my case, the same error occured because the data source I provided was working fine on my local machine but not from a remote machine. So I had to change the datasource. You could also add the point to check the datasource by pinging it.
I am not even able to open Properties for enabling remote access,its showing the error while i am trying to login, is there any other way to enable Remote Access.
I had the same problem and disabling my Anti-Virus(Avast) solved the issue
@Mwenda Sam – Thanks for sharing your solution. I am sure it would help others.
As usual, when I find your blog on a google search, I don’t need to look at anything else ;)
I am glad to hear that.
thanks it is working….
Absolutely incredible guide to solve this issue. Thank you very much for taking the time to create this.
Thank you for this awesome guiding article.
you don’t know how much struggle you saved me for making this, THANKS :-)
Thank you for this FIX-guide! + I’d like to suggest a little change In Method 2 “Enable TCP/IP in SQL Server Configuration”: All Protocols for MSSQLSERVER must be in Enable status include TCP/IP, Named Pipes etc.,
and in the method title: “Enable All Protocols in SQL Server Network Configuration”