Configuring Remote SSMS on Windows Server 2003
Note: Rackspace no longer offers Cloud Servers with Windows 2003, but these instructions are here for legacy support purposes.
By default, Microsoft SQL Server Express 2008 uses TCP Dynamic Ports for communication and data transfer. For remote access to your SQL Server using SQL Server Management Studio, you must configure the system to use a static TCP port. For this document, we will use port 1433 (default), but you can assign whichever port you would like.
This document assumes you have successfully installed SQL Server 2008 Express on Windows Server 2003.
Create Firewall Rules
First we need to create two firewall rules. One for the (TCP) port that the SQL Server will use and one for the (UDP) port that the SQL Server Browser uses.
- Click START > Control Panel > Windows Firewall.
- On the Exceptions tab click Add Port.
- On Add a Port, for Name, enter: SQL Server 2008 Default Port.
- For Port number enter 1433. Ensure TCP is selected. Click OK.
- On the Exceptions tab, click Add Port.
- On Add a Port, for Name, enter: SQL Server 2008 Browser Port.
- For Port number enter 1434. Ensure UDP is selected. Click OK.
Configure SQL Server to use port 1433
Now we need to remove the dynamic ports and tell SQL Server that you want to use port 1433 to connect to the server.
- Go to START > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager
- In the left pane of SQL Server Configuration Manager, double click SQL Server Network Configuration.
- Click: Protocols for (Instance Name). Make sure that TCP/IP is enabled. Right mouse click TCP/IP and select Properties.
- Here you need to select the IP Address tab on top and scroll all the way to the bottom (IPALL).
- Delete the entry for the TCP Dynamic Ports.
- Enter 1433 for TCP Port.
- Click OK, then OK on the Warning message.
You need to restart the SQL Server Service for the changes to take effect.
Restart SQL Server Service
To restart the SQL service...
- Go to START > Administrative Tools > Services.
- Scroll down on the right pane and select: SQL Server (Instance Name).
- Click on the Restart Service icon in toolbar.
Also, while you are here, make sure that the SQL Server Browser service is started.
Verify Window Firewall Settings
Open a Command Prompt. Run the command:
Ports 1433 and 1434 should both be open and listening.
You should now be able to connect to your SQL Server remotely.
© 2015 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License
See license specifics and DISCLAIMER