Enabling Remote SQL Express 2008 Network Connections on Vista
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!
Comments
Anonymous
September 17, 2008
PingBack from http://blog.a-foton.ru/2008/09/enabling-remote-sql-express-2008-network-connections-on-vista/Anonymous
September 17, 2008
Hi BethJust wondering, you mention SQL Server Express 2005, saying that it locks out remote connections, will these steps work to enable remote connections when using SQL Server Express 2005?Regards,LoganAnonymous
September 25, 2008
Hi Logan,Yep I think this is the same thing you need to do for SQL 2005 Express on Vista.-BAnonymous
September 28, 2008
Hi Beth,You beauty thank you :) I keep getting these errors saying "default settings don't allow remote connections" or something... although, not so much now I've watched the windows forms over data video series and know how to do it right lolThanks again, and keep shelling out these awesome tips will ya.LoganAnonymous
October 10, 2008
I just want tosay thanks for writing this. I'm a novice and was searching all the wrong things until I found your post. So, thanks!Anonymous
October 13, 2008
Thank you very much. This is just what i need. I've been trying a lot of things but they never worked.TDCAnonymous
October 15, 2008
The comment has been removedAnonymous
November 19, 2008
Hi Beth,I read you blog and several other hundred trying to figure out how to set up sql server on a small LAn(like a home network). You should use Mixed Mode Authentication. If you use windows Authentication(Integrated Security=True; in the connection string),unless you mirror each user on the network on the server computer, that user will not be able to login. A shortcut is to give the Guest account in every windows installation login rights to SQLSERVER. That is because every network log on is presented in windows authentication mode as Guest to the SQLSERVER. The SQL books Online recommends using WIndows Authentication mode as more secur. I cannot see how!Anonymous
January 04, 2009
Does this work on Workgroups on a Local Network?Anonymous
January 04, 2009
Hello Beth,I have an odd problem but i dont know if its the right topic :-) I have an VB application that connects to SQL Server Express 2008 on my computer. It works fine when i run it on my machine but when i run it on any other, it returns Network Instance 26 error. I enabled remote connections and if i type in the global IP (im behind a router) in the connection string (VB), it also works fine. If someone in local network tries to run the app, it crashes. I followed this manual but it didnt work :( (btw, i followed your How Do I videos when creating a database..)Anonymous
January 05, 2009
The comment has been removedAnonymous
January 07, 2009
Hi Carl,It should. That's what I have here when I tested it.Cheers,-BAnonymous
January 13, 2009
just another thank you from another fellow programmer who's on his way now thanks to this articlethanx!Anonymous
February 03, 2009
Hi Beth. Great and helpful blog!Can I add after having seen people experience difficulties: I will simply add on to your writing:Beth:3. Enable TCP/IP by right-clicking and selecting Enable, then OK.Willem: Double click the TCP/IP and click on the IP-adress tab. Go straight to "IPALL" - at bottom. Ensure Dynamic Ports contains a BLANK and add a port like 1433 to IP Port. If you have a named Instance leave the 1433 for some default and use 49172 (up to 65000)Now you are ready to restart your instance's service again"Beth:10. Configure the connection string. "With the advent of the VS express editions, only WEB DEVELOPER can connect to a database engine as such. Use the following example to sort out your connection string.<protocol>:<nameinstance,<port>e.g:tcp:myMachineMydbInstance,49172 - this if you want to use TCP/IP.Hope this will complete Beth's very welcome blog a little.Anonymous
February 26, 2009
Thanks, this saved me a lot of time!Anonymous
March 17, 2009
Great article. I got it working in about 2 minutes. Thanks.Anonymous
March 20, 2009
I found this article MOST helpful! You're a peach!Anonymous
April 04, 2009
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???Anonymous
April 04, 2009
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.-BAnonymous
April 04, 2009
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 :))Anonymous
April 20, 2009
The comment has been removedAnonymous
June 26, 2009
Awesome tip! Love the detail and step by step approach. Worked like a charm!Anonymous
July 07, 2009
Thanks for the straightforward post and step by step instructions.Anonymous
July 16, 2009
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!Anonymous
July 22, 2009
Very helpful post.Willem,your pointing out the need to disable the dynamic port was a life saver.Thanks.Anonymous
July 31, 2009
Beth (and Willem!)...Thanks a lot for all this info! It really helped us.Anonymous
September 05, 2009
Thank you for your explanation.Again a step further.Best regardsAnonymous
September 21, 2009
Thanks Beth!You've helped get my project another step forward (again!)Anonymous
September 22, 2009
v been listen to ur CBTs neva knw u r that pretty , well thanks for the post that has really helped me alot.Anonymous
November 03, 2009
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.Anonymous
November 04, 2009
Hi Raphael,Take a look here:http://msdn.microsoft.com/en-us/library/ms171886.aspxYou can connect to a remote database in Visual Studio and it will save that in the app.config file so that if you need to change the database location you don't have to recompile your application.HTH,-BAnonymous
November 09, 2009
On x64 bit systems, the SQL Browser.exe will be in "c:Program Files (x86)Microsoft SQL Server90Sharedsqlbrowser.exe"SQLServer.exe will still be in the same place as aboveAnonymous
December 02, 2009
Great Article...I've tested it in windows 7...it works just fine...now i'm able to connect to my database installed in a Windows 7...tnx...Anonymous
January 29, 2010
Hello Beth,I am a beginner in all this and I need help.When you say:"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""Am I supposed to see all the computers on my LAN?I have SQLSExpress on my main PC (Server), I developed an app with VBSExpress to access the data. Now I want to install the app on my Laptop (Client) and access the database (which is on my main PC).To do that do I add the LAPTOP/Account to the authorized connections? Or did I just misunderstand the whole thing?P.S. SQL Native Client is installed on the Client Machine (Laptop)Thanks,Malek.Anonymous
February 04, 2010
Hi Malek,Yes you need to add the windows account that you're using in the connection as an authorized account on the database. See this video for information on how to set that up:http://www.msdev.com/Directory/Description.aspx?eventId=1464HTH,-BAnonymous
February 10, 2010
Is there any way to do it without installing Management Studio?Anonymous
February 11, 2010
The comment has been removedAnonymous
March 01, 2010
Finally, a good walkthrough that really works! Thanks! :)Anonymous
March 06, 2010
your tutorial saved a time of my time.keep up the good work!Anonymous
March 23, 2010
I really appreciate you putting this tutorial out. I am swamped with work and never really was interested in the ins and outs of sql server administration. I just needed to slam together a dev environment without wasting a lot of thought on it. This was perfect for me. You saved me a ton of time, which makes you a saint in my book.Anonymous
March 23, 2010
This is the final step in my using MS Web PI on a dev workstation. I am now able to use my primary PC or laptop to connect to the dev machine and access the different DBs for my clients' test sites ... you rock for easing the pain.Thanks so much for setting up this tut!-Jasonp.s: I have SSMS 2005 on my primary and it can access the 2008 sql express engine, for anyone curious.Anonymous
April 28, 2010
BethI have my application that reads from the SQLSERVER express 2008 installed on my machine , but I want to move the application on another computer , I want to install the database automatically without manually configuring it ( no management studio I mean ) can you help me with this ?your assistance is appreciatedJMAnonymous
May 04, 2010
Hi beth, pls for the past 2 months i have not being able to create a login form i developed. reason is that it have being giving problem to query my database so as not to type wrong values to my textbox when login in. i used sql 2005. pls assist me. thanksor if you can help me with example for creating a form login.festy patggykool@yahoo.comAnonymous
May 04, 2010
The comment has been removedAnonymous
May 04, 2010
Hi festy,Take a look at this post: http://blogs.msdn.com/bethmassi/archive/2007/06/06/login-form-parameterized-queries-part-2.aspxHTH,-BAnonymous
June 16, 2010
thanl you so much i seems to solve my problem tooAnonymous
August 17, 2010
It is really very helpful. Thanks a lot.Anonymous
August 30, 2010
Hi Guys,I am facing a problem.In SQL server 2008, I am able to connect with the local server.But i am unable to connect remotedly.If i connect remotedly to the server, in that server SQL Server is working fine.But from my system it is not connecting..If anyone knows answer, send the solution to my mail id..my id is raju.kuruvella@gmail.comAnonymous
October 21, 2010
The comment has been removedAnonymous
December 07, 2010
Hi, cannot find SQLBrowser.exe on my system (first looking in the folder you suggested).Anonymous
December 13, 2010
Hello Beth, just following your deployment and testing notes regarding Lightswitch...browser 3-tier app..to my surprise SQLServer.exe was not in my /binn. Is there a simple source to download and install it?Thanks!~AndrewAnonymous
December 14, 2010
@Peg, if you are running a 64-bit OS then look in C:Program Files (x86)...@Andrew the file name is sqlservr.exe. If you don't have that then SQL Server is not installed.HTH,-BAnonymous
January 12, 2011
for real beth massi this is whats im looking for thank you ands now i can start my project in lightswitch easythank you againAnonymous
May 03, 2011
Hello Beth,I am new to SQL could you tell me, if I do as you instruct above, will I be able to test if it works by attempting to connect to the network database (mdf file) through Database Explorer in the VB 2010 Express IDE??ThanksBradAnonymous
September 12, 2011
Thanks, Beth, it was the Browser Service that had me stumped! Thanks for posting this!Anonymous
November 10, 2011
Thank you for this one! Saved me a lot of trouble:)Anonymous
January 23, 2013
Thank you soooo much - exactly what was needed!Anonymous
July 14, 2013
Thank you for the valuable info.This worked perfectly but now i have another issue. Whenever i restart the server i have to add the exceptions again. any solution you know? I am on MS server 2008Anonymous
October 22, 2013
Fabulous!After browsing a dozen other posts to no avail, I found yours, and I'm fixed. I had one other issue, though. I'm hosting my SQL Server on a Hyper-V machine. YOU MUST CREATE IN/OUTBOUND RULES TO ALLOW port 1433 on the Hyper-V server itself to make this work.Most of a day in the toilet for this seemingly simple project, but that's Microsoft for you. Most of their security features keep you from running THEIR programs, but do little or nothing to protect you from badguys.Anonymous
November 04, 2014
Thank you so much for informative article.Anonymous
January 23, 2015
Great article. It helped me a lot, thank you.Anonymous
May 05, 2015
how to attach ms sql express 2008 database file in windoionapplicatwAnonymous
June 03, 2015
Your six year old post is still helping people - Thank You.Anonymous
November 06, 2015
HOW CAN i connect to SQL Server Management Studio in a network ..im using visual studio 2010 .i want to connect my PC2 to the DATABASE of PC1 ..any TUTORIAL PLEASE