SQL Server 2005 configuration to allow remote connections

SQL Server 2005 does not configured by default to accept remote connection. While connecting SQL Server remotely you may receive several error depending on the condition.

You may receive the following error:

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.

However, this error may occur due to other issues:

• SQL Server not prepared to accept remote connection.
• Firewall blocking incomming and outgoing MSSQL port.
• SQL Server Browser service Turn Off.

Firewall blocking incomming and outgoing MSSQL port.

Windows Firewall and other firewall block incoming and outgoing MSSQL port by default. You need to unblock that port in your fireball. We are covering here how to unblock port on Windows firewall. If you are using other firewall you need to check your firewall manual to change settings.

SQL Server Browser service Turn Off

By default, SQL Server uses instance name build with pc name and SQLEXPRESS with forword slash to connect with SQL server. If SQL Browser service disabled, you many not connect to SQL server remotely. SQL Server Browser Service allow remote system to connect with SQL server.

Follow the steps to configure your server for accepting remote connection

Enable remote connections for SQL Server 2005

• Click Start ->> Programs ->> Microsoft SQL Server 2005 ->> Configuration Tools ->> SQL Server Surface Area Configuration.
• Now click Surface Area Configuration for Services and Connections.
• Now Click + sign to expand Database Engine,then click Remote Connections ->> Local and remote connections ->> Using both TCP/IP and named pipes and then
• Click Apply.
• Restart MSSQLSERVER service from Start – >> Control Panel ->> Administrative Tools ->> Service ->> Select MSSQL Server ->> Restart Service from left top corner link.

Firewall blocking incoming and outgoing MSSQL port.

You need to create exception in windows firewall for SQL Server

• Open Start ->> Control Panel ->> Windows Firewall
• Select Exceptions tab ->> Add Program
  
  
• Browse C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe and click ok. This location may change depend on your installation path. So please change path accordingly.
• Go back to firewall window and create an exception for SQL Server Browser service. Follow the above 2 steps for this.
• Now browse C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and click ok. Again here to remind path may be different depending upon your instalattion.

Enable SQL Server Browser service

• Click Start ->> Programs ->> Microsoft SQL Server 2005 ->> Configuration Tools ->> SQL Server Surface Area Configuration
• Now click Surface Area Configuration for Services and Connections
• Select SQL Server Browser ->> Select Startup type Automatic
• Click Apply.