Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections

When connecting to an instance of Microsoft SQL Server 2005 database, including SQL Server 2005 Express, Developer and Enterprise editions, for authentication or accessing and retrieving and updating the data, the the following error message may appear. The problem happens no matter the connection is originated from remote computer or on SQL Server local computer itself, and can be caused by any program, such as SQLCMD utility, used to connect to SQL Server.

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.

Or,

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

This problem may occur when SQL Server 2005 is not configured to accept incoming local and remote connections, which is disabled by default in SQL Server 2005 Express Edition, SQL Server 2005 Developer Edition and also SQL Server 2005 Enterprise Edition. To solve the connection forbidden problem, SQL Server 2005 needs to configure to allow incoming local and remote connections.

Firstly, ensure that SQL Server 2005 is configured properly to allow incoming connections on the instance of database server, else enable and turn on the local and remote connections setting.

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
3. On the “Surface Area Configuration for Services and Connections” page, expand Database Engine tree, click Remote Connections.
4. Select Local and remote connections, or Local connections only which applicable only if there is no remote system tries to connect to the SQL Server, useful when you just trying to connect and authenticate with the server after installing.
5. Select the appropriate protocol to enable to local and/or remote connections environment. To ensure maximum compatibility, select Using both TCP/IP and named pipes.
6. Click Apply button when done.
7. Click OK button when prompted with the message saying that “Changes to Connection Settings will not take effect until you restart the Database Engine service.”
8. On the “Surface Area Configuration for Services and Connections” page, expand Database Engine, then click Service.
9. Click Stop button to stop the SQL Server service.
10. Wait until the MSSQLSERVER service stops, and then click Start button to restart the MSSQLSERVER service.

Secondly, SQL Server Browser service has to be enabled to allow for local and remote connections if SQL Server 2005 is running by using an instance name and users are not using a specific TCP/IP port number in the connection string.

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
3. On the “Surface Area Configuration for Services and Connections” page, click SQL Server Browser.
4. Select Automatic as the Startup type to start SQL Server Browser service automatically every time system starts.
5. Click Apply button.
6. Click on Start button to start the service immediately.
7. Click OK button.

Finally, if remote computer needs to connect and access SQL Server, an exceptions in Windows Firewall included in Windows XP SP2 (Service Pack 2), Windows Server 2003 and Windows Vista needs to be created. If you’re using third-party firewall system, the exception rules also needed to be created to allow external remote connections to the SQL Server 2005 and SQL Server Browser Service to communicate through the firewall, else connections will be blocked. Consult the firewall manual for more details. Each instance of SQL Server 2005 must have its own exception, together with an exclusion for SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Configuration Manager.
2. In “SQL Server Configuration Manager”, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the “SQL Server Browser Properties” page, click the Advanced tab, locate the instance ID in the property list.
4. Click OK button.

Then create an exception for SQL Server 2005 in Windows Firewall.

1. Click on Start button, the click on Run and type firewall.cpl, and then click OK. For Windows Vista, type firewall.cpl in Start Search box and press Enter key, then click on Allow a program through Windows Firewall link on left tasks pane.
2. In “Windows Firewall”, click the Exceptions tab, and then click Add Program.
3. In the “Add a Program” window, click Browse button.
4. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open button. MSSQL.1 with is a placeholder for the instance ID that is obtained from previous procedure. Note that the path may be different depending on where SQL Server 2005 is installed.
5. Click OK button.
6. Repeat steps 1 through 5 for each instance of SQL Server 2005 that needs an exception.
7. For SQL Server Browser service, locate the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, and click Open button.
8. Click OK button.

Related Articles

• Workaround for Remote Desktop Client to Connect to Localhost (Local Terminal Server Service)
• Vista Remote Desktop Connection Client Slow Delay or Disconnect When Connect to Windows Server 2003 SP2 x64 Edition RDP
• Easily Maintain Multiple Remote Desktop Connections with visionapp Remote Desktop (vRD)
• Connect to Remote Computer using Specific Non Standard Port using Remote Desktop Connection Client
• Remote Connections Manager for RDP, VNC, SSH, RLogin and More mRemote Free Download
• Enable Multiple Concurrent Remote Desktop Connections or Sessions in Windows XP
• How to Find and Check Number of Connections to a Server
• Lowest Number MX Record Points to Local Host Rejected RCPT Error
• Tweak (Increase or Change) Maximum Simultaneous HTTP and Downloads Connections to Web Server
• Create and Setup Your Own Windows Vista KMS Server for Local Self Activation

This entry was posted on Wednesday, October 31st, 2007 at 3:10 am and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.