Winsby Group LLC Corporate Logo

Configuring MS SQL Server Express 2005 for Remote Access

Introduction

This procedure is provided for those that wish to try to open up SQL Server Express for remote access from another machine. We do not support this configuration but have seen it operate for one or two users. Check with your network administrator for any security or corporate policies that may be necessary.

Procedure

  1. Logon as administrator to the machine with Sql Server Express 2005.

  2. Run Sql Server Management Studio Express.

  3. Right-Click on the Server and select Properties.

  4. Chose the Security tab. For Server authentication, check Sql Server and Windows Authentication Mode.

  5. Go to the Connections tab and make sure “Allow remote connections to this server” is checked.

  6. Close Sql Server Management Studio Express

  7. Run Microsoft Sql Server 2005 -> Configuration Tools -> Sql Server Surface Area Configuration.

  8. Click on Surface Area Configuration for Services and Connections. Then find in the tree SQLEXPRESS -> Database Engine - > Remote Connections. Check Local and Remote Connections. Under that check Using TCP/IP only.

  9. On the same screen, go to Sql Server Browser -> Service. (If Sql Server Browser does not appear in the tree, you will need to run the Sql Server Express 2005 setup to install the Sql Server Browser). Make sure the Startup type is set to Automatic and the Service Status is Running. If the service is not running, click the Start button.

  10. Run Microsoft Sql Server 2005 -> Configuration Tools ->Sql Server Configuration Manager.

    Go to Sql Server 2005 Network Configuration->Protocols for SQLEXPRESS. Right click on TCP/IP and select Enable.

    Right click again on TCP/IP and select Properties. Go to the IP Addresses tab.

    There will be one IP record for each network adapter and possibly one for localhost (127.0.0.1). On the entry for the network adapter for TCP Port enter 1433. Under IP All, if there is an entry for TCP Dynamic Ports, delete it. It should be blank as above.

  11. If you are running Windows Firewall, you will need to open the firewall to the port for Sql Server and Sql Browser. Bring up Windows Firewall in the Control Panel and go to the Exceptions tab. Click Add Port… For the Name use Sql Express. For the port enter 1433. Click OK. Click Add Port again and enter Sql Browser and 1434.

  12. If you are using a different firewall, follow the instructions for that firewall to allow remote connection on ports 1433 and 1434.

Reboot the machine to restart Sql Server. You should now be able to connect from a remote machine. The remote machine will need to have Sql Server client utilities installed and will need to be configured to use TCP/IP protocol. If you don’t have client components installed on the remote machine, you can run the Sql Server Express setup program to install just the client components and the connectivity components.

A separate walkthrough of this procedure can be found at: https://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/

And a video (for SQL Express 2014) here:

https://www.youtube.com/watch?v=5UkHYNwUtCo

Revised: 2010-03-29
Next