Enable TCP/IP Remote Connection on SQL Server 2008

Introduction

Whenever I use a physical or a virtual machine containing SQL Server, I do not know easily create a SQL remote access to the server. By default, SQL Server Express doesn’t allow remote connection, but sometimes, a classic SQL Server Installation doesn’t active some features, like firewall access or correct authorizations.

This article explains how to:

• Enable TCP/IP protocol and Set a predefined port number (1433) to listener all remote SQL requests.
• Add a firewall rule to allow inbound traffic to SQL Server.
• Disable SQL Server Browser Service for security reasons.

Typical errors

Some typical errors can occur if your server is not correctly configured.

• Cannot connect to SQL-Server-Instance-Name. An error has occurred while establishing a connection to the server. When connecting to SQL Server, 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: 28 – Server doesn't support requested protocol) (Microsoft SQL Server).
• Cannot connect to SQL-Server-Instance-Name. An error has occurred while establishing a connection to the server. When connecting to SQL Server, 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).
• Cannot connect to SQL-Server-Instance-Name. Login failed for user 'username'. (Microsoft SQL Server, Error: 18456).

To enable remote connection on SQL Server 2008

1. Enable TCP/IP protocol and set a predefined port number (1433)

To enable the TCP/IP protocol for SQL Server 2008, to accept remote connection:

1. Open SQL Server Configuration Manager. Click Start / Programs / Microsoft SQL Server 2008 R2 / Configuration Tools / SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration / Protocols for [Instance Name]. If you see that TCP/IP protocol status is disabled, right click to Enabled it.
3. Open TCP/IP properties to set the correct listening port:
   1. In Protocol / General, set Enabled = Yes.
   2. In IP Addresses / IPAll, set TCP Port = 1433 and TCP Dynamic Port = an empty string (to disable the dynamic port).

   

4. Restart SQL Server Service. Click SQL Server Services in the left panel, right click on SQL Server and select Restart action.

2. Add a firewall rule

By default, windows don’t allow inbound traffic from 1433 port. To allow inbound traffic to SQL Server:

1. Open Windows Firewall with Advanced Security. Click Start / Control Panel / System and Security / Windows Firewall / Advanced Settings.
2. Select Inbound Rules (in left panel), and click on New Rules (in Actions panel).
3. Complete all wizard steps like:
   1. Rule Type = Port.
   2. Protocol and Port = TCP on specific local port 1433.
   3. Action = Allow the connection.
   4. Profile = Domain, Private, Public.
   5. Name = SQL Server TCP/IP (1433).

   

3. Stop SQL Server Browser Service

Please, for security reasons disable the service SQL Server Browser. This service is not a requirement to accept remote (or local) connection to SQL Server!

1. Open SQL Server Configuration Manager (see the previous step 1).
2. Click SQL Server Services in the left panel, right click on SQL Server Browser and select Properties action.
   1. In Log On tab, click on Stop button.
   2. In Service tab, set Start Mode to Manual.

4. Check SQL Server Authentication

Eventually, check the authentication mode set in SQL Server.

1. Open Microsoft SQL Server Management Studio and connect you to SQL server.
2. Right click on your SQL instance and select Properties.
3. In Server Properties windows, select Security page.
4. Select SQL Server and Windows Authentication mode, and verify if you have set a correct password to the login sa.
   

5. Open a remote connection

To verify if your SQL Server is correctly configured, you can open a SQL IDE (like the excellent free tools SqlDbx). In Login page, set the following server string syntax: “tcp:[ServerName\SQLInstance],[Port]” (SqlInstance is empty if you have installed SQL Server with the default instance).

Example:

• Server = tcp:MySqlServer,1433
• Database = Master
• User = sa
• Password = xxx