Search This Blog

Under the default settings SQL Server does not allow remote connections

It is quite often that when you try to connect to an instance of SQL Server 2005 form a remote machine, you may receive the following 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."

Here are the steps to be taken once you face such errors

I. Make sure that Remote Connections are enabled in Surface Area Configuration tool

Click Start -> Programs -> SQL server 2005 -> Configuration Tools - > SQL Server Surface Area Configuration

Now you will be able to see the following window

image 

 

 

 

 

 

 

 

 

 

 

Now choose the first option there "Surface area Configuration for services and Connections"

image 

 

 

 

 

 

 

 

 

 

 

 


Make sure that you choose the Right "SQL server instance at the Left Pane". As you can see from the above picture, I have 2 instances named MSSQLServer, the sql server 2000 instance, and my SQL Server 2005 instance, named SQL2005_1.

Click on Apply button, you can see a warning message saying that you need to restart the Service.

II. Check Whether TCP/IP protocol is enabled in the SQL Server Configuration Manager

Click Start -> Programs -> SQL server 2005 -> Configuration Tools - > SQL Server Configuration Manager

image

 

 

 

 

 

 

 

 

 

 

 

 

image

Now check whether "TCP/IP" is enabled, if not, double click on "TCP/IP" and choose "Yes" to enable it .

 

 

 

 

 

 

 

 

 

 

III. Make sure that  SQL server Browser service is running

You can do this in two ways, the first method is to use the same SQL Server Configuration Manager.

Click on the "SQL Server 2005 Services", on the left pane, and check "SQL server Browser" i running ? if not right click on that service and start. Or else choose properties and make the service to be run as "Automatic"

image

 

image

 

The second option is to make it automatic thru Control panel -> services

image

Now, if there is a firewall installed on that machine, then make sure that both SQL Server (SqlServer.Exe) and SQL server Browser ( sqlbrowser.exe ) are exempted in the firewall.

No comments: