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
Now choose the first option there "Surface area Configuration for services and Connections"
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
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"
The second option is to make it automatic thru Control panel -> services
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.
