Wednesday, September 8, 2010

A network-related or instance-specific error occurred while establishing a connection to SQL Server. sql server 2005, asp.net, c#.net, sqlconnection

Sometimes when trying to connect to sql server 2005 database the following error message will come

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

To resolve this error follow the steps

1. Start -> All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration. Click on Surface Area Configuration for Services and Connections. Select the instance that is having a problem > Database Engine > Remote Connections. Enable local and remote connections. Restart instance.

2. If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

4. You may need to create an exception on the firewall for the SQL Server instance and port you are using. Start > Run > Firewall.cpl. Click on exceptions tab. Add the sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Binn), and port (default is 1433). Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

Wednesday, July 15, 2009

An error has occurred while establishing a connection to the server - asp.net,sqlconnection,sql server 2005,sql,remote,connection,c#.net,vb.net

The Following is a usual error when trying to connect sql server 2005 from a remote computer.

Sqlcmd: Error: Microsoft SQL Native Client: 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.


This happened because the sql server 2005 instance is not configured to accept remote connections. To configure sql server 2005 to accept remote connections follow the following steps.

1. You have to enble remote connections on the instance of sql server that you want to connect from remote computer.

2.You have to check sql server browser service is on.

3. Make sure the configuration of firewall is allowing network traffic related to sql server and sql server browser service.

Now we can look in detail each of the above steps.

Enable remote connection for sql server express or sql server 2005 developer edition

Click on start->Programs->Microsoft Sql server 2005->Sql server configuration tools-> Sql server Surface area connection as below


When clicking on sql server 2005 surface area configuration the form will be
displayed like this


On this sql server 2005 surface area configuration click on the surface area configuration for services and connections. On the surface area configuration for services and connections click on the remote connections.



When clicking on remote connections




If the local connection only radio button is checked then change it to local and remote connections. When clicking apply then a message will appear as changes will apply only when you restart the service. Click Ok then restart the system

2.Enable the SQL Server Browser service
When connecting sql server 2005 using instance name you have to enable sql server browser service to allow remote connections. For example if sql server is connecting using computername/sqlexpress you have to enable sql server browser service to enable remote connections. To enable sql server browser service follow the following steps.

1. Click start-> Programs-> sql server 2005-> Configuration tools->Sql server surface area connections

2. On the Sql server surface area configuration page click surface area configuration for services and connections.

3. In the surface area configuration for services and connections page click sql server browser. Make startup automatic and then click apply.

4 Click start then OK