Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
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.
thanl you so much i seems to solve my problem too
It is really very helpful. Thanks a lot.
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 firstname.lastname@example.org
Help! I am running on a Win7 64-bit box with 3 Gig of ram. I have VS 2010 and SQL Server 2008 Express installed and I can talk to SQL via Vs. But I have fought for 3 days now to get SMSS installed and it fails every single time. I have tried both to the existing instance of SQL server and a new instance with the same results. I have tried installing R2 express with the same results. I have tried installing the trial version of SQL Server 2008 enterprise with the same results. Am I doing something wrong. Between each version installed I have rolled my system back to before doing the install, so it is essentially a "clean" box each time. Help! You can reach me here or via email at email@example.com
Hi, cannot find SQLBrowser.exe on my system (first looking in the folder you suggested).
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?
@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.
for real beth massi this is whats im looking for thank you ands now i can start my project in lightswitch easy
thank you again
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??
Thanks, Beth, it was the Browser Service that had me stumped! Thanks for posting this!
Thank you for this one! Saved me a lot of trouble:)
Thank you soooo much - exactly what was needed!
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 2008
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.