Husband, father, son and brother. Software developer building solutions in .NET, C#, ASP.NET MVC, Sharepoint, React and other web frameworks!

Configuring Remote Access to SQL 2005 on Windows 2003 Server

2 minute read

I’ve recently had to install and setup SQL 2005 Express edition on a Windows 2003 server.  My goal was to remotely access the SQL 2005 instance for a few applications I’ve been building.  The Windows firewall was preventing me from accessing my databases remotely so I had to do some changes to it to fix this issue.  Below are the steps I took to fix this issue:

First, you’ll need to get the instance id of your SQL 2005 Server.  Follow the steps outlined below to accomplish this:

  1. Click on Start
  2. Click on SQL Server Configuration Manager
  3. Select Server 2005 Services
  4. Right click SQL Server and choose ‘Properties’
  5. Click on the Advanced Tab
  6. Take note of the Instance ID as you’ll need this when adding exceptions on the firewall
  7. Click on OK
The next thing to do is to make sure that the SQL Server Browser Service is enabled and that it will start up automatically after a server restart.  Enabling this will allow you to access the SQL Server instance remotely by using the ComputerName\SQLExpressInstanceName format.  Follow the steps outlined below to accomplish this:
  1. Go to SQL Server Configuration Manager
  2. Select SQL Server 2005 Services
  3. Right click SQL Server Browser and choose properties
  4. Click on the Service Tab
  5. Choose Automatic for the Start Mode
  6. Click on OK
Exceptions on the Firewall
To allow remote connections, you must add a few exceptions to the firewall.  To create an exception for SQL 2005 follow these steps:
  1. Click on Start -> Control Panel -> Windows Firewall
  2. Click on the Exceptions Tab
  3. Click on Add Program
  4. Browse to c:\Program Files\Microsoft SQL Server\MSQL.1\MSSQL\Binn\sqlserv.exe (where MSSQL.1 is the name of your Instance ID).
  5. Click on OK

To add an exception for the SQL Server Browser Service in the Firewall, follow these steps:

  1. In the Windows Firewall, click on the Exceptions tab
  2. Click on Add Program
  3. Browse to c:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe
  4. Click on OK

Remote Access thru TCP/IP
If you need to access the sql server remotely by using TCP/IP, follow the steps outlined below:

  1. Click on Start
  2. Click on SQL Server Configuration Manager
  3. Expand the SQL Server 2005 Network Configuration
  4. Right click TCP/IP and choose ‘Enable’
  5. Select SQL Server 2005 Services
  6. Right click the SQL Server (INSTANCENAME) and choose ‘Restart’

Leave a comment