How to Enable Remote Connection to SQL Server 2008 Express Database

by

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;"

---

That's it... Hopefully, this post helps you get your SQL Server 2008 database enabled for remote connection.

 


0
5

5 Comments

anonymous by Khem on 3/20/2011
Thanks for sharing stuff, great, its works for me.
anonymous by Eman on 5/29/2012
Thaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaanks =)))
anonymous by Pablo on 11/27/2012
thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I was looking for that weeks!!!!!!!!
anonymous by Kevin D on 4/10/2013
Thank you so much for your tutorial, it was very explicit and helped me connect to my 2008 R2 express instance from my dev machine! Well done you! :)
Doug by Doug on 4/10/2013
Awesome! Glad to hear the tutorial helped you out!

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:

Comment:
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


Here's how you can enable SQL Server Authentication in a SQL Server 2008 database, and then add a User login and password account for connecting to the database. Open SQL Server Management Studio In the Object Explorer sidebar, right-click on the top SQL...  more »

When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

Here's one way to setup your connection string for a SQL Server 2008 Express database for a live website using ASP.NET on Windows Server 2008. Open "Sql Server Configuration Manager" -> the click on "SQL Server Network Configuration" -> then click...  more »