Geeks With Blogs
New Things I Learned

When I started using SQLExpress 2005, everything works on my development machine – however when I wanted to access the database from another machine (trying to access the sqlexpress database remotely) I was not successful.  I discovered the solution way back then, also through Google searches, which still yield a valid result.  However, every now and then my peers would ask me this same question again.

Thus I’m creating this blog so I can forward them to this page (instead of talking about it); it’ll also remind me of how to do it if my memory starts to fail me.

By default, SQLExpress installations does not allow remote connections, so we need to change that first.  To change it, you have to run the SQL Server Surface Area Configuration tool – as the name vaguely implied, this tool will allow you to configure SQL Server’s Surface (what gets exposed).  This tool is installed with your SQL installation (express or not); it should be located in the Microsoft SQL Server 2005 / 2008 program group, under the Configuration Tools subgroup.

When you run this, the following dialog appears:

 sac01

Select the ‘Surface Area Configuration for Services and Connections’ (as circled above).  The next dialog should appear:

sac02

Select the Remote Connections node on the tree view (as circled above); the dialog changes to the following:

sac03

As you can see, SQL Express by default does not allow remote connections (the text above it also enforces that); so we’d like to change that – select the ‘Local and remote connections’ radio button, depending on your needs you choose to use TCP/IP or named pipes or both, then click the ‘Apply’ button – an alert should show up telling you that the settings will not take effect until the database engine is restarted, we’ll do this last.

We’re not finished yet; what happens right now is that the system is set up to allow remote connections.  However, there’s another piece of service that needs to be up before we can connect to it remotely.  That service is the SQL Server Browser service.  Select the SQL Server Browser node on the left tree view, and the dialog will change to show the following:

sac04

Again, by default the SQLBrowser service is disabled, so let’s change that to Manual, click ‘Apply’ (which will enable the ‘Start’ button) then click the ‘Start’ button, which will start the service.  OK, we’re almost finished; the last step is to restart the SQL Server service itself, so let’s click on the Service node under the SQLEXPRESS and Database Engine node in the tree view.  The dialog will change to the following:

sac05

Click ‘Stop’ and then wait for the service stoppage to complete, then click ‘Start’ again.  You should be able to connect to your SQLExpress instance from another computer (if it’s the default instance, you would use computername\SQLEXPRESS as the server name).  Good luck!

Posted on Wednesday, November 11, 2009 4:08 AM | Back to top


Comments on this post: Make SQLExpress DB accessible from other computers

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Muljadi Budiman | Powered by: GeeksWithBlogs.net