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)

SQL Error Messages, SQL Scripts, SQL Server Security
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

  • muyassirsMuyassar
    June 6, 2012 1:20 pm

    Step 2, first process worked…
    SQL Browser Service was Stopped.
    Thanx Imran.

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

    Reply
  • Thank you…. this was very helpful. :)

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

    Reply
  • After start the sql services browser, the problem solved. Thank a lot.

    Reply
  • thanks for ur article…..very helpful for me….

    Reply
  • Thanks for the description

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

    Reply
  • Sandip Chaudhari
    March 25, 2014 6:18 am

    I got solution here of sql server error:40 and error:2

    Reply
  • how to solve instance error in sql server 2008 when installing setup

    Reply
  • muhzubairali
    June 4, 2015 11:27 am

    While following Imran’s Suggestion :2
    The start option for SQL Server Browser is disabled, What should I do

    Reply
    • Start option would be greyed out if service is set to disabled state. Go to properties and enable the service first.

      Reply
  • i changed the path in sql services and stopped the sql services and moved the maste database files from c to another drive after i started sql services its not started and sql server also not connected , i am using widows 8, can any body give me sollution

    Reply
  • I hv installed sql server 2008,after installation it seems error 40-couid not be connect to sql server….plz help me to overcome the error .

    Reply
  • Hi Pinal
    Forgot the obvious solution!
    Always a great site.
    Thanks,
    Paul

    Reply
  • How to solve windows could not start sql server (sqlexpress) service on local computer

    Reply

Leave a Reply