Enabling Remote SQL Express 2008 Network Connections on Vista

Enabling Remote SQL Express 2008 Network Connections on Vista

  • Comments 59

Today I spent way too long trying to enable remote network connections on my SQL 2008 Express database running on my Vista development machine so I thought I'd post the steps here. Please note that these settings may NOT be appropriate for your production environments. I'm a developer ;-), hence this is what I did to get the development environment working. If you need to set up production environments I'd read the SQL Server Books online.

Normally when developing code I always use the local SQL Express instance on my box and the default install takes care of everything so local connections via Visual Studio work fine. But what I was doing today was trying to connect a VPC to SQL 2008 Express on my host development machine in order to test some apps, simulating a network. When installing SQL 2005 or 2008 Express it locks out remote access to be on the safe side. Here's the basic steps you need to do to get it working:

1. Open up the SQL Server Configuration Manager. Programs / Microsoft SQL Server 2008 / Configuration Tools / SQL Server Configuration Manager.

2. Expand the SQL Server Network Configuration node and select the Protocols for SQLEXPRESS

3. Enable TCP/IP by right-clicking and selecting Enable, then OK.

4. Click on the SQL Server Services node and in the right panel right-click SQL Server (EXPRESS) and select restart to restart the service.

5. Right-click on the SQL Server Browser and select start to start the browser service if it isn't started already. This will allow you to access the SQL Express instance by the computer name.

6. Open up SQL Server Management Studio as Administrator. (If you don't have SSMS installed, get it here)

7. In the Object Explorer under the Security node, add a new user for the account that will be connecting by right-clicking and selecting "New User". This opens the Login Properties page. If you're on a domain then use Windows Authentication. For my VPC scenario it wasn't connected to a domain so I added a SQL Server login and password. (To enable SQL logins you need to first right-click on the SQL Express instance at the top, select Properties and under Security select "SQL Server and Windows Authentication mode".)

8. Select User Mapping on the Login Properties and check off the database you want to connect to. If you're just testing then you can select db_owner in the role membership to grant all access to the database, including altering the schema. Please note that this is not secure but it works for development and testing.

If you want to be secure you can go and specifically grant permissions under the Security node for the specific database back up in the Object Explorer. But if you're on a domain then I would recommend creating a least privileged Windows Security Group on your domain and adding that to a SQL Server Role that you can configure explicit permissions on (i.e. GRANT EXECUTE on your CRUD stored procs and GRANT SELECT on tables, don't let DELETE, INSERT and UPDATE directly). Then you can just use normal Windows security to add users on the domain to the group. This also lets you use integrated security in the connection strings which is much more secure.

9. Configure the Firewall. This is the step that I forgot and was banging my head on the wall 4-EV-R! Open up Windows Firewall and select Change Settings, select the Exceptions Tab and click Add Program. You'll need to select the SQLservr.exe in Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\ and the SQLBrowser.exe in Program Files\Microsoft SQL Server\90\Shared\. Then select Properties for each of them and select the "Change Scope" button. Then select the proper scope. For development and testing you could specify the remote computer's IP address or widen it to your network subnet. I recommend not allowing Internet users. ;-)

10. Configure the connection string. Now back in Visual Studio you can configure your connection string settings in your Project Settings (app.config). If you used Windows Authentication then it's really easy:

Data Source=SERVERNAME\sqlexpress;Initial Catalog=MyDatabase;Integrated Security=True

If you used a SQL Login then you need to supply a less secure connection string with the username and password:

Data Source=SERVERNAME\sqlexpress;Initial Catalog=MyDatabase;User ID=username;Password=password

I hope that works for you, it did for me. If you're still having trouble please see these troubleshooting tips in the library or ask a question on the SQL Server forums. I'm by no means an expert in SQL Server configuration or networks.

Enjoy!

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • Great article. I got it working in about 2 minutes. Thanks.

  • I found this article MOST helpful!  You're a peach!

  • do you beleive what you're telling? ("it works for me")

    no it doesn't work for no body there are much more to do please tell people completely or don'tell anything.people are spending days and days oh it works for her so am i an ideot???

  • Hi Delta Dirac,

    Well it seems to have worked for many people here acoording to the other comments. It's kind of hard to help you if you don't explain what steps you tried or what error you are getting.

    -B

  • ofcourse i know you don't have to solve any personel difficulties met when programming,at least you don't have time for that Just wanna say,this sql networking thing is not so simple,i passed many many obstacles,first 'the remote connection settings and allowing named pipes and tcp/ip ' then 'user guest doesn't allowed in something',than i made logins on server,i made everything and everything,i gace roles i gave permissions but no way at the and of the fourth day i 've got a "User does not have permission to perform this action" what permisson i gave all te roles all the permissions, you know what something wrong with me or with these procedures,anyway thanks for your interest to my rude and angry comment(but i am still angry this is tha fact :))

  • When they say "Firewall" ... it's not just windows firewall, but ANTIVIRUS FIREWALL too!!!!

    That was the key for me...  may be a little kork-head  (cabeza'e termo para latinos), don´t care. That was it. 11:30 pm, at client site... Ramon, you saved my day!!!

  • Awesome tip!  Love the detail and step by step approach.  Worked like a charm!

  • Thanks for the straightforward post and step by step instructions.

  • THANK YOU!

    I had been wrestling with this for two Evenings!!

    (Even though the Error Message was Very Descriptive and Telling me what was wrong!!)

    Thanks Again!

  • Very helpful post.

    Willem,

    your pointing out the need to disable the dynamic port was a life saver.

    Thanks.

  • Beth (and Willem!)...

    Thanks a lot for all this info!  It really helped us.

  • Thank you for your explanation.

    Again a step further.

    Best regards

  • Thanks Beth!

    You've helped get my project another step forward (again!)

  • v been listen to ur CBTs neva knw u r that pretty ,  well thanks for the post that has really helped me alot.

  • Hii,

    I was wondering if there's a good tutorial for connecting a Visual basic 2008 database app to the SQL 2008 database trough the network and what do do for it.. cause i made a dbase in SQL server Managment Studio, via tutorial, but i dont know for far how to connect it..

    I'm creating a app on the laptop, en the sql server 2008 & managment  database are on my local pc.

    THnx in advanced!

    Btw.. Your tutorials are very good.

Page 2 of 4 (59 items) 1234