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 2008.
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.
- Open Server Manager. In the left pane, double click: Configuration > Windows Firewall with Advanced Security.
- First click, then right mouse click, Inbound Rules. Click New Rule...
- For Rule Type, select Port, then click Next.
- On Protocol and Ports ensure TCP is selected. For Specified local ports, enter 1433 then click Next.
- Just click Next to get through the Action and Profile screens.
- On Name enter SQL Server 2008 Default Port. Enter a description if you would like, then click Finish.
Now we'll need to repeat the procedure to add an entry for port 1434 as well.
- In the Server Manager left pane, right mouse click Inbound Rules then click New Rule...
- On Rule Type select Port, then click Next.
- On Protocol and Ports ensure UDP is selected. On Specified local ports, enter 1434 and click Next.
- Once again click Next through the Action and Profile screens.
- On Name enter SQL Server 2008 Browser Port then enter a description if you'd like one. Click Finish.
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.
- Select the IP Address tab on top of the Properties screen 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 server...
- 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 the toolbar.
Also, while you are here, make sure that the SQL Server Browser service is started.
Verify Windows 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.