Error Has Occurred While Establishing A Connection To SQL Server 2005 / 2008 / 2012 / 2014 Which Does Not Allow Remote Connections

When connecting to an instance of Microsoft SQL Server 2005 or Microsoft SQL Server 2008, Microsoft SQL Server 2012 or Microsoft SQL Server 2014 database, including SQL Server Express, Standard, Web, Workgroup, Business Intelligence, 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)

Or,

Cannot connect to SQL Server.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, 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)

This problem may occur when SQL Server instance is not configured to accept incoming remote connections, which is disabled by default in most SQL Server Express Edition and other SQL Server commercial editions. To solve the connection forbidden problem, SQL Server needs to configure to allow incoming local and remote connections.

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

With SQL Server Management Studio

If your SQL Server installation comes with Management Studio (normally excluding Express edition) then follow steps below to configure the remote access option:

1. Open SQL Server Management Studio.
2. In Object Explorer, right-click a server and select Properties.
3. Click the Connections node.
4. Under Remote server connections, select the Allow remote connections to this server check box.

   

5. Next, open the SQL Server Configuration Manager, expand the node SQL Server Network Configuration.
6. Select Protocols for <MSSQLServer instance name>.
7. Enable the TCP/IP if it’s disabled.

   

8. Restart your SQL Server instance.

Without SQL Server Management Studio

If you don’t have access to SQL Server Management Studio, try the steps below:

1. Open SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 / 2008 / 2012 / 2014 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 (Local connections only, the default setting, is 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. Open SQL Server Surface Area Configuration.
2. On the “SQL Server 2005 / 2008 / 2012 / 2014 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, Windows Vista, Windows 7, Windows Server 2008 (R2), Windows 8, Windows 8.1, Windows Server 2012 (R2), Windows 10, Windows Server 2016 or later 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 instances 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 must have its own exception, together with an exclusion for SQL Server Browser service.

SQL Server 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. Open 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 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 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.