May 11, 2011

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005.

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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Steps to resolve above error.
1) SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.


2) Enable TCP/IP in SQL Server Configuration

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

3) Open Port in Windows Firewall

All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports.By default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception.
Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions >> Add Port

4) SQL Server browser service should be up and running
If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser service should be up and running and also be added to the exception, as described in Step 3.

5) Enable Remote Connection
If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

No comments: