SQL SERVER - Fix : Error : 40 - could not open a connection to SQL server.

April 23, 2007 by pinaldave

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 default settings SQL server does not allow remote connection. ( provider: Named Pipes Provider, error: 40 - could not open a connection to SQL server. )

Fix/Workaround/Solution:
Step 1) Make sure SQL SERVER is up and the instance you try to connect is running.
Step 2) Your system Firewall should not block SQL Server port.
Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.
Step 4) Now follow this KB Article of MSDN depending on your server : http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

UPDATE : If above solution does not help refer the follow up post SQL SERVER - Fix : Error : 1326 Cannot connect to Database Server Error: 40 - Could not open a connection to SQL Server where additional issues with FIREWALL is explained with images.

Posted in DBA, SQL, SQL Authority, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology | 64 Comments

64 Responses to “SQL SERVER - Fix : Error : 40 - could not open a connection to SQL server.”

on May 20, 2007 at 3:00 pm Stan

Thank you, I would have never figured out that port enabling!
on June 19, 2007 at 3:29 am mahesh

Hello , I did all the settings what you suggested but still it giving me same error messages

some of error messages are like..

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

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)
on August 2, 2007 at 4:20 am genious

hi,

thanks for your help.

have a nice day.

genious
on September 26, 2007 at 8:09 am Kavya

Hi

I am using Asp.net and Sql Server.There is a situation where i need to connect to Remote Sql Server to get some values.Some values are from local Sqlserver.

If remote Sql server is down i get the Error Named Pipes Provider,Error:40

Is there a way i can handle this and just show empty strings if the remote sql server is down.

Please hlep me with this

Thanks
Kavya
on October 13, 2007 at 9:47 pm Me

Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration

Can you please tell where this ‘Computer Management’ folder/file is?
on October 19, 2007 at 11:38 am kavya

Hi

I am not exactly sure which folder your’e asking about.

Thanks
Kavya
on October 19, 2007 at 11:56 am kavya

Hi

I also have one more Question

My Sql server DB is taking almost 54Gb of Physical Memory,and i see its not the Data but the LogFile,Can you let me know why is Log eating up the memory.

Thanks
Kavya
on November 14, 2007 at 12:29 am Shahul

Hi all,

I also have the same problem.

I tried all the above given. But still the problem continues.

Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration

How to go to Computer Management.

Please Help.

Thanks in Advance
on November 18, 2007 at 3:06 am pHelics

Thanks, the problem in my case was the firewall, but I didn`t even bothered to open the port in Windows Firewall, because I have the ESET Smart Security, and I was recieving the same error, with the ESET Firewall on or off, I thought that the Windows Firewall is disabled allready, so it has no impact. I guess I was wrong. :)
on November 28, 2007 at 5:12 pm Kensai

we are getting the wrror like

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)

ple give me the guidence to solve above error
on December 6, 2007 at 1:33 am jimmy

Hello , I did all the settings what you suggested but still it giving me same error messages

some of error messages are like..

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

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)
on December 8, 2007 at 6:44 pm Purushotam

Hey,

I too had same problem with my dev environment at home, but could figured it after banging my head. for some reason it did not work as suggested by many experts.

but dont worry, figured what was the problem at last………

the probelm is the Instance name from the client is not resolved, to do that follow the below shown steps.

1. navigate to C:\Windows\System32\drivers\etc
2. Open hosts file in notepad
3. add the entry at the end of line, as i did for example
192.168.131.65 KSSQLSRVR
192.168.131.65 KSSQLSRVR\SPDEV01

those were my instances i am tried to connect remotely
Hope this helps…

i think u got me!
on December 17, 2007 at 12:45 pm Amit Malviya

Hi,

Thanx.. I got Solution.

Have Nice Time.
on December 18, 2007 at 11:29 am Murugappan

Hi,

Thanks…..
on December 25, 2007 at 2:25 pm mahmood

Hi i use sql server2000 but i recive this error :

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)

can any body help me ?
on December 28, 2007 at 4:48 am Derek

Hey Mahmood
looks like your remote connection is not set properly.
As Dave mentioned earlier do the follwing and still doesnot work we will go from there.
Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.
Step 4) Now follow this KB Article of MSDN depending on your server :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

hope this works,
on December 29, 2007 at 2:06 am Dhaval

I faced the same problem. I wasted almost a week. Finally I got the solution.
There are so many reasons behind the same problem.
One of then is as below.

There might be possoble you have defined aliases for the SQL server in your machine.

To check the alias :

Go to Control Panel -> Administrative Tools -> Computer Management : Open It

Expand Services And Application-> SQL Server Configuration Manager->SQL Native Client Configuration

Check Aliases Option Under.

If you find any aliases for the SQL Server that you are getting problem. Delete it for time being and test it.

For me it woeks hope that will help you too. Thanks.
on December 31, 2007 at 9:43 pm Lejanson

Hi,

I have tried doing the enabling of Named Pipes and TCP/IP and VIA but still I get the same error…

Does this work with .sdf files?

Lejanson
on January 2, 2008 at 2:28 pm Venkateswarlu Cherukuri

I tried in so many ways to solve the following problem,
but still i am not getting the solution.

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

this problem has encountered when using web parts in asp.net.

Thanks

Cherukuri
on January 18, 2008 at 2:35 am Apo

I have the same error( error 40 ), trying to connect to my SQL Server 2005. I use .net C# to make a web service (no web site!!) and trying to establish a connection locally in my PC. I cannot find a connection string too.
SqlConnection conn = new SqlConnection(???????????????);

Thanks you,
Apo
on January 29, 2008 at 6:39 am RayG

Fixed my problem with SQL Express.

Thanks,

RayG
on February 12, 2008 at 8:42 pm Vinay

I am trying to run asp.net 2.0 app against SQL Server 2000 and get error message

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)

Both app and SQL server are local

connection string is

“Data Source=(local);Initial Catalog=DBNAME;user id=sa;password=”

I have tried enabling TCPIP protocoll on 2005 server but it does not help.

Please help

Thanks
Vinay
on February 18, 2008 at 12:34 pm Gayathri

hi, i am using biztalk server. I am getting the same problem when i am trying to deploy the biztalk server. I have applied your guidence. But my problem is not solved.
If you have any different idea please tell me.
thank you.
from
Gayathri
on February 19, 2008 at 10:14 am Reddy Giri

Thanks for u r suggestion. It work were well, i will tryed lost 10 days.
on February 19, 2008 at 4:01 pm Sravani

Hi Frnz,
i am using Microsoft SQL 2005, i am getting the same problem,

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)

i enabled the TCP/IP, and also i changed the settings as mentioned in http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277 but no use.

could you please suggest me some other solution to solve this problem.
on February 24, 2008 at 2:32 am w43l

Thanks alot
that port thing worked like hell
on March 29, 2008 at 1:23 am grateful

thanks gave me some ideas.
on April 2, 2008 at 11:37 pm manu

I am trying to run windows application against SQL Server 2000 and get error message dot net 2005 is developement environment

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)

Both app and SQL server are local

connection string is

“”Data Source={m};Initial Catalog=dbname;Integrated Security = SSPI; Trusted_Connection=Yes;”

I have tried enabling TCPIP protocoll on 2005 server but it does not help.

Please help

Thanks
Manish
on April 4, 2008 at 9:42 am Tracy

I tried your fix above for a situation I have with SQL and so far, I have still not been able to fix the problem. I get exactly the same error message and I have applied your ideas as well as the ideas on the microsoft link and so far, none has worked. Can you help please???
on April 4, 2008 at 9:44 am Tracy

Better still, Can you also give me information on how to create a System Administrator Account for my SQL server. I mean add a user as a sysadmin and configure the account in such a way that user is able to access the DB
on April 12, 2008 at 1:25 am vensat

I am able to connect to SQL Server databases using SQL Server Client but not able to connect to any of the SQL server 2005 64bit databases from .NET code. I am getting following error message in .NET.

TCP/IP, named pipes and Shard Memory were already enabled in SQL Server 2005 network configuration and SQL Native client configuration. TCP/IP Default port 1433. SQL server, SQL agent and Browser serveices were running.

Error Message :
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)

Following is the connection string that developers were using using.

Persist Security Info=False;Integrated Security=SSPI;database=omhq537e;server=INV_PROD

Any suggestions about how to resolve this issue?
on April 29, 2008 at 1:06 pm ABBAS

THIS WEBSITE VERYUSEFUL FOR ME
on May 7, 2008 at 2:55 pm Kamesh

This solution works for me

1. navigate to C:\Windows\System32\drivers\etc
2. Open hosts file in notepad
3. add the entry at the end of line, as i did for example
192.168.131.65 KSSQLSRVR
192.168.131.65 KSSQLSRVR\SPDEV01

Thanks to Purushotam
on May 12, 2008 at 3:36 pm sameer

I try this but problem is still persist
on May 22, 2008 at 3:18 pm gurujie

I have the same problem still, I tried all the suggestions but still no fix. I also realise that the 2 servers are on 2 different domain. I presume due to this my issue remains unresolved
on June 2, 2008 at 6:18 pm junior dev

I also had that problem but it was caused by the DNS suffix Search List on the server set to an ip address instead of the domain name, so my app was looking for server.111.222.111.222 instead of server.domain.name

Thanks for helping me look in the right general direction anyway.
on June 9, 2008 at 4:10 pm Saed.NET

I am facing the same proble, even after I apply all the solution suggested above,
Please if any one hase extra information that might help,save my life …
note:
I have the web application on a machine with SQL server installed and I connect to a database on othe machine
on June 10, 2008 at 6:18 pm TheProgrammerAnalyst

If you changed your connectionstrings in your web.config, verrify that the information is correct.

I use a different synthax for the production server and my development server and I accidently copied the web.config file from the production server to my development server and there was my error.

Have a great day and I hope that this will help someone!
on June 12, 2008 at 4:19 pm Ajay

hi all
i hv tried all solution for the errr

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)

at the time of sql server 2000 connection
and it working fine with sql server 2005 express
but no exact solution is not getting
plz help me
on June 17, 2008 at 5:55 pm Ashwin

I am trying to run windows application against SQL Server 2000 and get error message dot net 2005 is developement environment

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)
on June 25, 2008 at 4:29 pm Hari

Thanx Mannn things worked ….
on July 7, 2008 at 3:13 pm anil

Hi,
i am trying to connect my local server in sql server 2005.
every time fails raising error “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) (.Net SqlClient Data Provider)”

tried with net start mssqlserver - command gives service is invalid

any help…
on July 9, 2008 at 9:02 pm larry

SQL Server 2005 connecting from MS VS 2005 app gives no problem, but SQL Server 2005 connecting from MS VS 2008 app gives the exact problem and seemingly no one knows the fix, not even Microsoft!
on August 9, 2008 at 7:00 am SQL SERVER - Fix : Error : 1326 Cannot connect to Database Server Error: 40 - Could not open a connection to SQL Server Journey to SQL Authority with Pinal Dave

[...] SQL SERVER - Fix : Error : 40 - could not open a connection to SQL server [...]
on August 21, 2008 at 6:47 am Tom

For those that might not have figured it out yet, Computer Management refers to the same place as either 1) right-clicking “My Computer” and hitting “Properties”, or 2) going to Start, Run, and typing “compmgmt.msc” (no quotes).
on August 21, 2008 at 6:49 am Tom

Actually, sorry, I meant right-clicking “My Computer” clicking “Manage”, not “Properties”.
on August 21, 2008 at 11:44 am Pallav

Hi,

We are using plesk 8.3 and using remote sql server connection ,couple of days ago database conection was working fine. Now we are facing an unknown issue..

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)

If i use the same connection string in my local Asp.net application i am able to connect to remote database but when i try in plesk it return error. Even i am able to connect using MS SQL management studio and SQL Manager Lite .

What could be the issue . Please help us ASAP.
on August 22, 2008 at 5:47 am Imran Mohammed

@ Pallav,

Try checking these things,

1. Check SQL Server Browser service is running and is kept in automatic Start mode.

This can be checked by.

Start -> run- > type services.msc -> enter -> check for SQL Server browser service, and start if it is not running.

2. Under SQL Server Surface Area Configuration check if SQL Server allows remote connections, by default it will allow only local connections.

This can be checked by,
Login to Server ( Where SQL Server is installed )
Start-> allprograms -> Microsoft SQL Server 2005->Configuration Tools -> SQL Server Surface Area configuration -> Click Server Area configuration for Services and connections ->choose your sql server - remote connections -> on the right side, choose allow remote connections to this server and choose both TCP/IP and NamedPipes.

3. Or Check your Windows Firewall, if SQL Server is being blocked, try to disable Firewall and then connect if it works then problem could be WIndows firewall.

Hope this helps.
Imran.
on August 23, 2008 at 5:02 pm Shafaqat Ali

I am getting the same error while publishing the application. It runs fine on the local IIS but does not publish it in a local directory so that i can upload it a production server. Any suggestions?
on August 24, 2008 at 7:00 am SQL SERVER - Fix : Error : 40 - could not open a connection to SQL server - Fix Connection Problems of SQL Server Journey to SQL Authority with Pinal Dave

[...] SQL SERVER - Fix : Error : 40 - could not open a connection to SQL server [...]
on August 25, 2008 at 10:32 am Nagesh

After following steps, i am getting the error called “Microsoft Sql Server error : 2″

So what to do?
on August 30, 2008 at 7:00 am SQL SERVER - FIX : ERROR : Could Not Connect to SQL Server - TDSSNIClient initialization failed with error 0×7e, status code Journey to SQL Authority with Pinal Dave

[...] and I have already posted few solutions on this error, see if you can find your solution here SQL SERVER - Fix : Error : 40 - could not open a connection to SQL server SQL SERVER - Fix : Error : 1326 Cannot connect to Database Server Error: 40 - Could not open a [...]
on September 2, 2008 at 7:02 am Jennifer

When i go into control panel a try to uninstall a program it tells me that the file log can’t be found. I have even tried to restore the computer and it tells me it worked fine until it restarts and then it says it didn’t restore properly.. Can u help me?

Jennifer
on September 9, 2008 at 10:25 pm tinto

hai

Thanks a lot
gr8

regards
tkt
on September 16, 2008 at 6:49 am Junaith

Hi Dude

Thanks for the info on port changing. It solved my problems that I had for weeks. Thanks.
on September 17, 2008 at 5:47 pm Gagan

ultimate reply

thks

gagan
on October 10, 2008 at 12:32 pm Ephrem

I have getting the same error message and I tried everything in this article unfortunately t wasn’t working.
Finally I make the following change and it works fine:
Visual Studio 2008 Tools Menu>>Options>>Database Tools…there was a Sql-Server instance specified to alias of the sql-Server instance and I delete it and it works fine.
I hope this helps
Cheers,
Ephrem
on October 21, 2008 at 11:36 pm Steve

Step 3 in the suggested workaround above solved my problem. The defalut SQL Server Port was blank. I changed it to 1433.

Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.
on December 1, 2008 at 11:15 am narayanan

Hi we u get the error sql server does not allow remote connections then go to sql surface area configuration and there select allow remote connections TCP/IP and named Pipes and then restart SQL server both in surface area configuration and sql server for restatating sql first u need to connect to u windows authenication and then right click on the server name and click restart and then try to connect.
NOTE: Remote connections will allow only sql authentication and windows authentictaion cannot be used
on December 9, 2008 at 12:30 pm mithril27

I am getting the following error and have tried all the above workarounds but my SQLServer & SQLServer Agent are still refusing to run. the error msg i am getting is below: “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:2).
Please assist.
on December 21, 2008 at 3:42 am shawnl

I had the same error. Mine had to do with user permissions. The SQLServer2005 security group was created locally on the server and not at the domain level. I created a new group in AD, then added that AD group to the local group on the server. Everything is working now.
on December 26, 2008 at 4:45 pm Alex

Hello, I have a related problem installing a new instance of SQL SERVER 2005, when it is installing the database engine it says that there are no process on the other side of the named pipes and I can’t configure anything because the instance is not installed yet, someone have any idea. Thanks.
on December 30, 2008 at 12:27 am Sara

Hi. I am posting my SQL issue that is driving me crazy for the past few days.

Error: ” 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: 2) ”

I get this message when I try to open SQL Server Management Studio. I have SQL Server 2005 (SP 2) on my new Windows Vista PC. I went to Services to check if teh MSSQLSEVER is running only to find that it is stopped and I cannot get it started, and also the other SQL Sever services except SQL Browsing.

Below is the last part of the ErrorLog if this givesd any idea:

—————————————–

2008-12-29 12:06:15.37 Server Error: 17182, Severity: 16, State: 1.
2008-12-29 12:06:15.37 Server TDSSNIClient initialization failed with error 0×2, status code 0×80.
2008-12-29 12:06:15.37 Server Error: 17182, Severity: 16, State: 1.
2008-12-29 12:06:15.37 Server TDSSNIClient initialization failed with error 0×2, status code 0×1.
2008-12-29 12:06:15.37 Server Error: 17826, Severity: 18, State: 3.
2008-12-29 12:06:15.37 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2008-12-29 12:06:15.37 Server Error: 17120, Severity: 16, State: 1.
2008-12-29 12:06:15.37 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
—————————————–

I did some basic troubleshooting like enabling Named Pipes, disabeling VIA protocols etc but no luck yet.

I found few articles with a “solution ” to this error online but none specify steps in orderly fashion where I get lost. Any help in this in this regard would be greatly appreciated.

Thanks,
Sara
on January 6, 2009 at 11:20 pm Mehar Sudha

I tried lot of solutions for this problem and atlast the following solution worked for me.

Added and Entry of IP of the Server in the C:\Windows\System32\drivers\etc

Thank You Purushotam