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
Advertisements
Tagged with:
Posted in SQL Server
4 comments on “Enable TCP/IP Remote Connection on SQL Server 2008
  1. Brad says:

    I just wanted to say THANK YOU. Literally everybody else on the Internet seems to not know about this part of the config as I found plenty of people saying go to the SQL Native Client configuration to enable TCP. Thanks to you I finally saw that the issue was in the Server Network Configuration and I just needed to enable TCP! Thanks

  2. Dennis says:

    Brilliant article. This really helped me getting to connect from my new SQL2012 system to my old SQL2008 system. One thing though… The connection initially didn’t work after following all of the steps in your article. I was required to switch the SQL Server Browser back on in order to connect from the SQL2012 SSMS.
    Also, I don’t understand why the all IPs in the TCP/IP Properties are active:yes but enabled:no. Shouldn’t the individual IPs be enabled. Oh well, I am just happy I got this working. Don’t have to understand everything. Curiosity killed the cat.

    • Thanks for your comments.
      SQL Server Browser Service provides information about SQL Server instances installed on the computer. If this service is not started, you need to knwo the servcer IP (or name) and you can not click on the “Browse” button… but, for me it’s better to stop this service and to write the correct IP address.
      About IP activation, you can activate one IP only (and not All), but if your server is configured with multiple IPs, you must know exactly which IP will be used… if you set “All IP”, there are no problem in this case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 611 other followers

Who I am?






%d bloggers like this: