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