SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server – Fix Connection Problems of SQL Server

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

This error happens due to many reasons. There are few solutions already given on my original threads.I encourage to read following two articles first and see if you can find your solution. If you can not find any solution from here, I suggest to read additional suggestions listed below in ticket.

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 connection to SQL Server

Let us go over few of the other suggestions.

Suggestion 1: From Dhaval

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.

Suggestion 2: From Imran

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.

Suggestion 3: From Emeka

There are couple of ways to troubleshoot this problem. The one you should use depends on how your databse server was configured and some other factors as well.

For example, if you configure the database engine to use dynamic port allocation, make sure that sql browser is runnning. SQL browser provides the connection information about the database engine to the the client.

If the sql browser is not running and you have restarted sql server and port 1433 is being used by other applications, database engine will be allocated a different port number. Imagine that the client has been configured to use port 1433 to connect and the database engine is using a different port number. The client wont be get the connections properties to the database engine from sql browser because sql browser is not running.

To avoid this type of problems, when the database engine has been configured to use dynamic ports, make sure that your sql browser is always running.

If you have found any other solution to this problem, please let me know and I will add it here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

56 thoughts on “SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server – Fix Connection Problems of SQL Server

  1. Hi Pinal,

    I am getting the same 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) ) for one of my application on running on SQL 2000 on OS Window 2000 Terminal.

    I tried and tested all the possibilities but all were unsuccessful.

    The same Application is running successfully on other server having same configuration.

    Please help in above matter.

    Like

  2. How to solve the same Problem in case of SQL 2000!I am getting the error as :
    [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object ‘sys_numbers’, database ‘XXXX’, owner ‘XXXXX’.’
    Can i get a sloution for this!

    Like

  3. I am getting the same error, but the Provider is TCP (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: TCP provider, error: 0 – An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.) )

    Like

  4. i try it..and fixed it..but i can’t change password of account login “sa”.. when i restart SQL server, it isn’t change…how i can do??? please!!!!!

    Like

  5. Hi Pinal ,

    I am facing the same problem Error : 40 – could not open a connection to SQL server but I am using SQL SERVER 2008. I tried out all the options you all mentioned above that are applicable to SQL server 2008 but it still not working. Please help

    maina

    Like

  6. I was facing same problem but got the solution. I use Vista.
    Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
    Enable TCP/IP protocol >> Right hand click to open properties of TCP/IP protocol >> open Ip Adresses Tab >> IPAll >> TCP Dynamic port >> put the value 1433 if it’s different. Hope this will help anybody if anybody is facing the problem on Vista.

    Like

  7. Hi.

    Another sql quirk: using VB6 to Open ADODB.Recordset from Exec proc and an ADODB.Connection using SQLOLDDB.1 breaks the statement: while not Recs.EOF wend. BUT using DSN the exec populates recs no problem.

    The target server is 2k8, and the sql user the apps uses has full rights (privileged to the crown), and the usual firewalls, etc. issues don’t apply.

    What “SQLOLDDB.1″ does a DSN use? What’s the difference DSN / my connection string?

    Eish!
    Habeeb

    Like

  8. Hi,
    I don’t know what happen but for some reasons I’m not able to connect to my server anymore. Everything was fine couple days ago and today when I tried to use web developer to build a web site it gives me error with connection to my database which I wanted to use. When I opened configuration manager I found that server browser is working fine but server stopped working. I tried to start it again but it didn’t work. I disabled firewall just to see if the problem with it but the same thing. I got vista on my pc and use sql express. Btw my server surface area doesn’t work as well and I cant check if the problem is remote connections.
    Thanks.

    Like

  9. @Alex,

    The account under which SQL Server Service is running, is it expired ? Did you check if password is still valid.

    Go to Configuration Manager , double click SQL Server Service and change user name and password.

    This could one of many reasons, why this could happen.

    Also, please post exact error message.

    ~ IM.

    Like

  10. Hello IM,
    here is the exact error message.
    I use management studio express to connect to the server

    TITLE: Connect to Server
    ——————————

    Cannot connect to ALEX\SQLEXPRESS.

    ——————————
    ADDITIONAL INFORMATION:

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

    I disabled my user account control on my laptop because that was a reason why I couldn’t attach databases to my server before.
    Also, I made my default port 1433 for TCP/IP protocol and made exceptions for sql server and sql browser in firewall.

    And my surface gives me that:

    TITLE: Surface Area Configuration
    ——————————

    Could not load file or assembly ‘Microsoft.SqlServer.CustomControls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified. (SQLSAC)

    Honestly to say I don’t know what else to do.
    Thanks for your time

    Like

  11. @Alex.

    Please apply SQL Server 2005 Service Pack 2. To both client tools and database engine and see if you still face the same issue.

    I searched on web for this issue, below was the conclusion.

    Error Message :
    Could not load file or assembly ‘Microsoft.SqlServer.CustomControls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified. (SQLSAC)

    1. This error occurs when the version of Client tools is not same as SQL Server Database Engine.
    Resolution : Apply Service pack and make version of Client tool same as SQL Server Database Engine.

    2. Some issue with SQL Express, few people suggested after they installed SP2, they got rid of this issue.

    Try installing SP2, and apply it on Client tools as well as your database engine.

    Download Link for SQL Server 2005 Express Edition Sp2:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=31711d5d-725c-4afa-9d65-e4465cdff1e7&displaylang=en

    Leave a comment, if this does not solve your problem.

    Also please provide below information

    1. Is SQL Server installed on the same box where you have your sql client tools.
    2. What version of SQL Server is that (RTM, Sp1, Sp2, Sp3)
    3. If it a default instance of Named instance ? ( From what you have mentioned in the post, it is clear it is a named instance, but want to confirm, if you installed a default instance of named instance.)

    Thanks,
    ~ IM.

    Like

  12. Hi,

    Ensure {server name} is written/spelled properly. If computer name is {lambda}, {server name} should be something like {lambda\sqlexpress}.

    regards.

    Like

  13. I’ve got a problem in this vein.

    Two days ago, my hard drive failed. After reinstalling Windows and SQL, the connection string I’ve previously used without problems started throwing the error in the title.

    What really vexes me is that everything seems to be fine:
    – in SQL SERVER Surface Area Configuration, Remote Connections accept both TCP/IP and named pipes (we’re using pipes)
    – when trying to connect to the server via SQL Server Management Studio, with the

    – connection string (original, excerpt): Data Source=(local)\;Initial Catalog=SMA;Integrated Security=True;

    Like

  14. I’ve got a problem in this vein.

    Two days ago, my hard drive failed. After reinstalling Windows and SQL, the connection string I’ve previously used without problems started throwing the error in the title.

    What really vexes me is that everything seems to be fine:
    – in SQL SERVER Surface Area Configuration, Remote Connections accept both TCP/IP and named pipes (we’re using pipes)
    – when trying to connect to the server via SQL Server Management Studio, with Server Name=lizm (instance name is same as computer name), and user name and password. Connection happens without problems.
    – on a quick look through cliconfg.exe, I’ve had both protocols enabled, and no aliases (which I’ve read are causing problems) present.

    The relevant excerpt from my original connection string :
    Data Source=(local)\;Initial Catalog=SMA;Integrated Security=True;
    I’ve tried variations on it, even though it used to be functional; still no luck.

    Like

  15. @Liz,

    1. Check Browser Service ( For SQL Server 2005 and above)
    2. Check Windows Firewall or Anti Virus, if that is blocking…
    3. Check the service you are trying to connect is actually running.
    4. Check the Server Name to which you are connecting, if it is a named instance of default instance.

    ~ IM.

    Like

    • when i start the services it says sql server on the local computer start then stopped. some services stopped automatically if they dont have any work to do. for example
      logs and alert services.

      Like

  16. Thanks for the answer, Imran.

    The key was with #4; my SQL server was installed by Visual Studio 2008 kit and thus was default instance; the ‘lizm’ I was connecting to was an alias that I previously created for testing, and although it seemed to work just fine from Management Studio, it wouldn’t allow connections from another computer, or, apparently, our application.

    I’ve tried reinstalling an SQL server, with instance name=’LIZM'; it created the name LIZM\LIZM, which is of no use to me. What should I do to get the name LIZM? Uninstall everything and try again? Is there a renaming process? Searching for ‘install same name as machine’ only turns up linkage errors, so I’m forced to ask.

    Like

  17. I am getting the same 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) )

    Only SQLserver (MSSQLSERVER) and
    SQLserver Agent (MSSQLSERVER)
    was not start and give above error massage

    I tried and tested all the possibilities but all were unsuccessful.

    The same Application is running successfully on other server having same configuration.

    Please help in above matter.

    Like

  18. 19.

    I am getting the same 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) )

    Only SQLserver (MSSQLSERVER) and
    SQLserver Agent (MSSQLSERVER)
    was not start and give above error massage

    I tried and tested all the possibilities but all were unsuccessful.

    The same Application is running successfully on other server having same configuration.

    Please help in above matter.

    Like

  19. Hello, i faced this problem in the first time when i installed sqlserver2005 . And it was solved by implementing the solution in this problem. i had to uninstall the program and re-install it again for some problems in my pc. But when i re-installed it , i had to change the instance name. Because it wouldn’t work without that , and it gives me an error. Now i have this error when i try to establish a new connection or add a database to my project.

    Like

  20. This by far is one of the more irritating errors with MS dev tools. MYSQL just works. Anyway after installing multiple times MSSQL SErver 2008 and R2 versions on my win 7 ultimtate os I found that sql management tools failed to browse any DB engine that was present until……….
    in the server dialog I entered my \. For me both my machine and instance name are neptune e.g. NEPTUNE\NEPTUNE and the co nnection worked immediately.

    What bothers me the most is that when /I initiated a browse for any engine this did not come up. Even after turning off the firewall and ensuring the sql configuration was correct, named pipes and TCP 1433 enabled also.

    Needles to say it works now thank goodness ;) Hope this helps someone.

    Like

  21. Hi,

    I hv developed a small project in Visual Basic 6 with sql server 2000 as database. I wanted to know that whether it is possible to use sql server 2008 as database with vb6 application, if yes pl guide me how to connect the server.

    thank a ton in advance

    Stephan

    Like

  22. when i start the services it says sql server on the local computer start then stopped. some services stopped automatically if they dont have any work to do. for example
    logs and alert services.

    Like

  23. I completely solve the problem from instruction of one GURU above replies, and I will copy again for sure to implement that instruction I have SQL Server 2008, Vista and VS2010.

    and Thank to:

    Saurabh >>>> You are amaizing GURU

    here is:

    I was facing same problem but got the solution. I use Vista.
    Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
    Enable TCP/IP protocol >> Right hand click to open properties of TCP/IP protocol >> open Ip Adresses Tab >> IPAll >> TCP Dynamic port >> put the value 1433 if it’s different. Hope this will help anybody if anybody is facing the problem on Vista.

    Like

  24. Hi there!
    I need help.
    Am running a windows server 2008 R2.

    We have installed Microsoft Dynamics Navision 2009.
    At one point i tried opening Microsoft Dynamics Navision, i got this error:

    ” The Microsoft Dynamics NAV server cannot connect the Change Listener to SQL server because of the following error: A network -related or instance error occurred while establishing a connection to SQL server”
    provider : named pipes provider error: 40 – couldn’t open a connection to SQL server,.

    Please help, our office is down

    Like

  25. Hi we have been working pretty well with the above mentioned application.
    Its only today that it has mis-behaved.

    Thanks alot.

    Like

  26. can anyone provide info on how to get SQL 2008 past that STUPID error 40. I am trying to get to run on a single XP system. I have tryed all the fixes i could figure out but nothing works so far. I am `REALLY sick of SQL!!!
    Thanks Bob

    Like

  27. I think I followed all the suggestion above but I still have problem.
    I can login to the SQL server 2008 locally with windows or sql authentication. The server is running in Windows Server 2008 R2 added in a domain. (not a dc)
    I enabled Remote Connection.
    I started and enabled automatically, the SQL Server Browser service.
    I added TCP port 1433 to both server 2008 r2 and Windows 7 firewall. I also added UDP port 1434 from the server firewall. I also turnoff the firewall on both server and desktop computer.
    Installed MS SQL Management Studio in Windows 7 which is not in a domain.
    I can ping the SQL server
    I can also see the SQL server in the MS SQL Management Studio in Windows 7.
    But when I try to remotely connect in Windows 7 using the SQL authentication, it gives me this error:

    I can’t connect to the SQL Server with an error “Cannot connect to . A network-related or instance-spedivic error occured 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 Provided, error: 40- Could not open a connection to SQL Server) (Microsoft SQL Server, Error:5)”

    Please help,
    vera

    Like

  28. I was attempting to use visual studio to connect to a database and received the error which consists of the title of this thread.

    I solved the problem by selecting the correct instance of sql server to connect to, I was connecting to SQLEXPRESS and I needed to connect to a non-default instance, for example CORRECTSQLEXRESS.

    Like

  29. hi my server is SQL SERVER COPMACT EDITION and database is:temp@123
    and iam using sql server authentication and not having username,password is to give coonection string in web.config

    Like

  30. *********************************************************
    NOTE:
    ********************************************************************************************************
    my solution :
    start sql server in control panel… administrative tools…services….sqlserver(MSSQLSERVER)
    it works
    *******************************************************************************************************

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s