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
Thanks Vince, post was helpful.
Thanks my issue is resolve by using your steps…
Thanks, this post was helpful. The only issue i had was that the sql browser service was disabled, which is weird ’cause that’s the way it is on the actual server. I only had to enable on my testing environment =S.
Thanks a lot!
Good one…
Thanks a lot, you are my lifesaver
Hi Dear Pinal,
Must thank you for the wonderful steps. I installed sql server on windows 7 for the first time. and was getting the ’40’ error. and luckily the first page i opened was of yours. Immensely loved the steps, easy and in few minutes my sql server was up and running. :) cheers!
Quite impressive!!! Thanks for the blog…Very Useful….
You saved lot of time Thank you so much
Thanks for your help, Pinal.
Hello Sir,
while installing SQL Server 2008 in my system i got following error:
Hello Sir, While installing SQL Server 2008 in my system … i got following error..
Description:
SQL Server 2008 Setup has encountered an error.
Problem signature:
Problem Event Name: SQL100Exception
Problem Signature 01: SQL2008@RTM@
Problem Signature 02: 0x2AECB151
Problem Signature 03: 0x2AECB151
Problem Signature 04: 0xC24842DB
Problem Signature 05: 0xC24842DB
Problem Signature 06: LandingPage
Problem Signature 07: Unknown
Problem Signature 08: Unknown
Problem Signature 09: Unknown
Problem Signature 10: Unknown
OS Version: 6.1.7600.2.0.0.256.1
Locale ID: 1033
Additional information about the problem:
LCID: 1033
Please Help…..
Hello Vishal, I think its Service pack problem.
can u help me out with how to do the same in windows 7
u r not only best u r the best best best and best
Hi all,
We’ve tried everything from top to bottom of this post – tried nearly every single reply and link!!
And still no joy.
We have changed so many things so many times it’s ridiculous. I just don’t know where to go from here and we have to get this application published.
We have and ASP app on our web server that cannot connect to our SQL server
Made sure remote connections enabled.
Checked ports on connection manager.
Added exceptions in the firewalls of the SQL server (Win2k8r2 SQL 2008r2 Ent.) and the Web server (IIS7 Win2k8r3) for all ports, services and applications too.
Portqueries from any machine on the network show TCP and UDP ports 1433-1434 listening.
Tried with and without ‘alias’ no joy.
Tried with firewall off – no joy.
Added port to connectionString with comma and without no joy.
Here is our connection string now:
blanked our password but it’s really there.
We have been looking at posts for two weeks on this issue and are still stumped.
Here is the error we still get.
Server Error in ‘/’ Application.
——————————————————————————–
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)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: 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)
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[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.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6351856
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) +6366442
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) +180
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +6366814
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6366729
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +352
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +831
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +49
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6368534
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +78
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +2194
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +89
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6372046
System.Data.SqlClient.SqlConnection.Open() +300
System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) +67
[EntityException: The underlying provider failed on Open.]
System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) +11109774
System.Data.EntityClient.EntityConnection.Open() +142
System.Data.Objects.ObjectContext.EnsureConnection() +97
System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +66
System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator() +47
System.Linq.Enumerable.FirstOrDefault(IEnumerable`1 source) +220
System.Linq.Queryable.FirstOrDefault(IQueryable`1 source) +383
eFortesModel.DAL.CommandExecutor.ExecuteFirstorDefault(ObjectQuery`1 objectQuery, MergeOption mergeOption) +370
Member_MemberLogin.GetFortesUserInfo(String fortesUserLogin) +202
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +25
System.Web.UI.Control.LoadRecursive() +71
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3064
——————————————————————————–
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.237
ANY help would be GREATLY APPRECIATED!
Thanks to all for all the great information. What a wonderful community.
Well after two weeks of wrangling, a call to MS and WAY too much reading it turns out it was the code!!
There was a coding problem in the dynamic data portion of the application. The application developer modified the connection settings, and now the application works as expected.
Hello the above Exception(provider: Named Pipes Provider, error: 40 – Could not open a …….) occurred due to service pack problem. If you Installed SQL 2000 in your Server 2003. Upgrade with SERVICE PACK 4. Your Application will Execute on remote PC’s Over IIS.
Thats all…..
This machine is a brand new installation of Windows 2008R2 with SQL2008R2. All windows service packs and patches show up to date. No other databases (or dbase servers) ever installed.
thank u, u r saved my time
Hi there,
I have got the error message prompt:
Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 – No process is on the other end of the pipe.)(.Net SqlClient Data Provider)
my db servers are in cluster environment and they are running fine until recently got this security agent installed in the db servers which I see this error prompts, and they are running fine again once I shut the security agent off.
Kindly advice, as the security agent installed is a passive agent which only does monitoring on local db activities.
thanks,
Boonlep Chua
GO START -> ALL PROGRAMS -> SQL SERVER 2008/2005/2008 R2
->CONFIGURATION TOOLS->
->SELECT SQL SERVER CONFIGURATION MANAGER
->SELECT SQL SERVER NETWORK CONFIGURATION
->SELECT PROTOCOLS FOR MSSQLSERVER(YOUR INSTANT NAME)
->IN RIGHT TASK PANEL
->MAKE SURE MENTION BELOW:
DISABLE SHARED MEMORY AND VIA
ENABLE NAMED PIPE AND TCP/IP
—-MAKE SAME THING IN
->SQL NATIVE CLIENT 10.0(SERVER VERSION) CONFIGURATION – CLIENT PROTOCOLS
RESTART SQL SERVER(MSSQLSERVER [YOUR INSTANT NAME]) SERVICE
Hi Rupendra,
Thanks for the info, beside was wondering do I need to open/add the port for the security agent installed in the db, as the security appliance is running on port 443 which I do not think it is the cause of the error message prompts when the security agent is switched on.
Thanks.
Hi, I would really appreciate some help, I seem to be failing at step one here. I have installed SqlServer 2008 on a Windows 2008 Server. The installation seems to go fine. When I go into the server configuration manager everything is running, just like in your screen shot. But when I try to connect using Management Studio I get the Named Pipes error 40. I am not even connecting remotely, I am on the same machine. So I tried running sqlcmd -L and sure enough this must be the problem, because MSSQLServer is not listed. It just lists the name of the computer. So this is my question: why is my server not listed, and how can I fix it. Everything is on one machine. MSSQLServer is definitely installed and running, but why doesn’t it show? I have done the other steps with enabling named pipes and restarted the server. I even added the tcp port to the firewall to the exceptions list (even though I don’t think this is relevant because it is a named pipes error). Maybe the answer is here but I am too thick to get it. I would be very grateful for any help!
I finally uninstalled SqlServer 2008 and installed SqlServer 2008 RS. I was actually trying to install on Windows 2008 Server RS. I don’t know what the RS stands for but it seemed to make a difference. To begin with I was getting the same errors but then I found this blog which really was useful:
https://blogs.msdn.microsoft.com/sql_protocols/2007/05/16/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error-xxx/. When I do sqlcmd -L now I see ComputerName/InstanceName running properly. Nothing happens in a vacuum and I was trying a bunch of things, but I think the most important might have been adding printer and file sharing to the firewall. I guess MS figure that anyone who installs SQL Server is probably a network administrator or something so there is no need to even try to be use friendly. Final balance of wasted days: 4.
thanks a lot for step by step guide
First I’d like to thank you for this as it’s helped me setup a lab environment where I had this particular issue when trying to setup a SQL Mirror. During the setup I tested running the sqlcmd -L command and can confirm that if SQL Server Browser service is not running on a server, being the default instance or not, it will not be displayed. The install was a default instance of SQL Server 2008 R2 Enterprise.
Once the service was started it would show up when running the command.