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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database
Next Post
SQLAuthority News – 4 Million Visits – over 675 SQL Server Articles

Related Posts

62 Comments. Leave new

  • Hello Rakesh,

    Sometime it happens that using the ip address with correct port connects successfully but using the server name not.

    Regards,
    Pinal Dave

    Reply
  • 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.

    Reply
  • Exllent Solutions

    Reply
  • 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. NEPTUNENEPTUNE 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.

    Reply
  • I had read the links above,i had apply those setting but unfortunately i still can’t connect to SQL Server2008 remotely

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • thank you Imran…

    Your suggestion really helped me a lot..

    Reply
  • Semanda Thomas
    January 5, 2011 9:30 pm

    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

    Reply
  • Semanda Thomas
    January 5, 2011 9:32 pm

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

    Thanks alot.

    Reply
  • hi i got the error 89845dcd8080cc91 when i am installing an application in to POC server can u pls suggest the solution

    Reply
  • 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

    Reply
  • Thanks a lot.. It worked

    Reply
  • That is image solution yaar…

    Reply
  • thanks alot….. got a solution

    Reply
  • hi,
    im getting the same problem Error:40 in sql server 2008
    plz plz help me

    Reply
  • Suggestion 2 works for me ^^
    but only until 2 point…. i didnt found “SQL Server Surface Area configuration”. any way thanks

    Reply
  • Thanks Dude! Its working fine…

    Reply
  • 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

    Reply

Leave a Reply