SQL – How to Configure SQL Server Express to accept remote connections

914277 - How to configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Some people have been having issues when trying to make remote connections to SQL Express.  This document will hopefully clarify most of the issues around remote connections.

First, networking protocols are disabled by default in SQL Server Express. Thus, if someone simply installs Express and chooses all the defaults, SQL Server Express will only be able to have connections originating on the local machine where SQL Server is installed.

To enable SQL Server Express to accept remote connections we need to perform the following steps:

STEP 1: Enabling TCP/IP

First we must tell SQL Server Express to listen on TCP/IP, to do this perform the following steps:

1. Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2005 CTP" Program menu;
2. Click on the "Protocols for SQLEXPRESS" node,
3. Right click on "TCP/IP" in the list of Protocols and choose, "Enable"

STEP 2: To Browse or not to Browse

Next, we have to determine if we want the SQL Browser service to be running or not.  The benefit of having this service run is that users connecting remotely do not have to specify the port in the connection string.

Note: It is a security best practice to not run the SQLBrowser service as it reduces the attack surface area by eliminating the need to listen on an udp port.

[tab: Option A]

OPTION A: If you want to always specify a TCP port when connecting (Not using SQL Browser service) perform the following steps else skip these steps:

1. Launch the SQL Server Configuration Manager from the "Microsoft SQL Server 2005 CTP" Program menu;

2.      Click on the "Protocols for SQLEXPRESS" node;

3.      Click on the "TCP/IP" child node;

4.      You will notice an entry on the right panel for "IPAll", right click on this and select, "Properties";

5.      Clear out the value for "TCP Dynamic Ports";

6.      Give a TcpPort number to use when making remote connections, for purposes of this example lets choose, "2301";

At this point you should restart the SQL Server Express service.  At this point you will be able to connect remotely to SQL Express.  A way I like to check the connection is my using SQLCMD from a remote machine and connecting like this:

SQLCMD -E -S YourServerSQLEXPRESS,2301

The "," in the server name tells SQCMD it's a port.

So you've tried this and still get an error.  Take a look at Step 3, this should address the remaining issue.

[tab: Option B]

OPTION B:  If you want to use SQL Browser service perform these steps:

Note: You will need to make this registry key change if you are using the April CTP or earlier versions:

1. To enable sqlbrowser service to listen on the port 1434, the following registry key must be set to 1;

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90SQL BrowserSsrplistener

2. Next, restart the sqlbrowser service.

3. Start the SQL Browser Service;

STEP 3: Firewall..?

At this point you should be able to remotely connect.  If you still can't chances are you have a firewall configured on the computer where SQL Express is running.  The instructions below are for Windows XP SP2's firewall settings.

To enable the firewall to allow SQL Server Express traffic:

1. Launch the Windows Firewall configuration tool from the control panel;

2. Click the Exceptions Tab;

3. Click the "Add Programs" button and select "sqlservr.exe" from the location where you install SQL Server Express;

You should be able to remotely connect.  Note, you can get more restrictive by just specifying the port number that will be allowed (used best when configured with Option A).

Note: If you chose to use the SQL Browser service, you must also add sqlbrowser service executable to the exception list as it listens on udp port 1434.

[tab:END]