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)