To Enable Remote Connections Via TCP / IP for SQL Server Express Info Blogging Info Blogging

Sunday, September 4, 2011

To Enable Remote Connections Via TCP / IP for SQL Server Express

05:13 infoblogging

Networking protocol is disabled by default on SQL Server Express. Therefore, if we are to installs Express and select defaults, then SQL Server Express will only be accepting connections from the local computer where SQL Server was installed.

To enable SQL Server Express to accept connections from remote computers we need to do the following:

STEP 1: Enabling TCP / IP

We have to instruct SQL Server Express to listen on TCP / IP, this is done by following these steps:

Run SQL Server Configuration Manager from the Programs menu - Microsoft SQL Server 2005 CTP
Click on the node "Protocols for SQLEXPRESS"
Right-click on the "TCP / IP" in the Protocols list and select "Enable"

STEP 2: To Browse or not to Browse

Next we need to determine whether we need the SQL Browser service running or not. The advantage of the passage of this service is that users who connect remotely do not need to specify a port in the connection string.

Note: For security reasons are advised not to run SQLBrowser service.

OPTION A:

If we want to always specify a TCP port when connecting (ie not using the SQL Browser service) do the following:

Run SQL Server Configuration Manager from the Programs menu - Microsoft SQL Server 2005 CTP
Click on the node "Protocols for SQLEXPRESS"
Click on the child node "TCP / IP"
Note the entry on the right panel for "IPAll", right click and select "Properties"
Clear the "TCP Dynamic Ports"
Fill Tcp Port number to use keystrokes to make remote connections, for example "1433"

In this step we have to restart SQL Server Express service, and we will be able to connect remotely to SQL Express. To check use SQLCMD commands from a remote computer and make connections as follows:

SQLCMD-E-S YourServer \ SQLEXPRESS, 1433

Sign "," in the name of the server means is the port number.

If there are errors, refer to Step 3.

OPTION B:

If we use the SQL Browser service perform the following steps:

Note:

We need to make changes to the registry key if using a TCP version.

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

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ 90 \ SQL

Browser \ Ssrplistener

Then restart sqlbrowser service.

- Start the SQL Browser Service

STEP 3: Firewall ..?

At this point we should be able to connect remotely. If still not successful, there may be a firewall issue on the set on the computer where SQL Express is running. The following steps are used for setting the firewall in Windows XP SP2.

To enable the firewall to allow traffic to SQL Server Express:

Run the Windows Firewall configuration tool from the Control Panel.
Click on the Exceptions Tab
Click on the "Add Programs." and select "sqlservr.exe" from the location where we install SQL Server Express.

We should be able to remotely connect. We can also set more stringent firewall by typing the port number that is allowed, for example in 1433 (well used to the configuration of Option A).

If we choose to use the SQL Browser service, we also have to add sqlbrowser service executable on the Exception tab because the program listens on UDP port 1434.

Do you Like this Post ?