How to Enable Remote Connection to SQL Server 2008 Express Database

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #.

1)  First make sure the SQL Server Authentication is enabled and the User name that you're using to connect to the database has been assigned the db_owner role membership.  To do this see: Enable SQL Server Authentication and Windows Authentication for SQL Server Databases

2) Now we need to enable TCP/IP connections from another computer.  Open SQL Server Configuration Manager, then expand SQL Server Network Configuration, and double click on Protocols for SQLEXPRESS (or MSSQLSERVER).

The default instance (an unnamed instance) is listed as MSSQLSERVER. If you installed a named instance, the name you provided is listed. SQL Server 2008 Express installs as SQLEXPRESS, unless you changed the name during setup.

3) In the list of protocols, right-click the protocol you want to enable (TCP/IP), and then click Enable.

Note: You must restart the SQL Server service after you make changes to network protocols; however, this is completed in the next task.

4) We now will Configure a Fixed Port. Follow these steps

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click on the server instance you want to configure.

2. In the right pane, double-click TCP/IP.

3. In the TCP/IP Properties dialog box, click the IP Addresses tab.

4. In the TCP Port box of the IPAll section, (which is at the very at the bottom), type an available port number. Port numbers should be assigned from numbers 49152 through 65535. For this tutorial, we will use 49172.    Also you may want to clear the TCP Dynamic Ports box.

5. Click OK to close the dialog box, and click OK to the warning that the service must be restarted.

6. In the left pane, click SQL Server Services.

7. In the right pane, right-click the instance of SQL Server, and then click Restart. When the Database Engine restarts, it will listen on port 49172.

5) You now need to open the Windows Firewall to allow inbound port 49172.

1. Click Start -> Control Panel -> System and Security -> Windows Firewall -> then click on Advanced settings to open Windows Firewall with Advanced Security
2. In the left pane, click on Inbound Rules, then in the right pane click on New Rule...
3. In the New Inbound Rule Wizard select Port and click Next, then select TCP and in Specific local ports box type: 49172 and then click Next.
4. Select Allow the connection and click Next and apply the rule to Domain, Private and Public (all checked) and click Next.
5. Now give your rule a name like: "SQL Server Express - My Fixed Port 49172" and then click Finish. 

6)  Now to connect to the newly configure fixed port for SQL Server Express from another computer open SQL Server Management Studio.   See Connecting to the Database Engine from Another computer:

"In the Server name box, type tcp: to specify the protocol, followed by the computer name, a comma, and the port number. To connect to the default instance, the port 1433 is implied and can be omitted; therefore, type tcp:<computer_name>. In our example for a named instance, type tcp:<computer_name>,49172."  Then in the Authentication drop down box, select SQL Server Authentication and type in your database owner's User name and password.  The connections string to a SQL Server Express database would look like the following, just replace "<computer-server-ipaddress>" with your server's ip address:

connectionString="data source=<computer-server-ipaddress>\SQLEXPRESS,49172; Initial Catalog=MyTestDatabase;User Id=MyUser;Password=mypassword;"