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.
Take a look here:
You 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.
On x64 bit systems, the SQL Browser.exe will be in "c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"
SQLServer.exe will still be in the same place as above
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...
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)
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:
Is there any way to do it without installing Management Studio?
Sure, I just don't know what the commands are offhand. SQL Server books online is a good place to start
You can use sqlcmd at the command prompt to execute T-SQL statements.
Finally, a good walkthrough that really works! Thanks! :)
your tutorial saved a time of my time.
keep up the good work!
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.
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!
p.s: I have SSMS 2005 on my primary and it can access the 2008 sql express engine, for anyone curious.
I 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 appreciated
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. thanks
or if you can help me with example for creating a form login.
You can select to install SQL Server Express as a prereq to your deployment, just like the .NET framework. You do this on the Publish tab of the project properties. Once SQL server is installed you can automate the installation of your database using a SQL script and executing it via sqlcmd. See this article for an example of hot to run a script with sqlcmd:
Then you just update the connection string in the app.config file. You can do this when your application starts the first time or as a separate utility. I'll put it on the list to write a post about this, you can also check out Robin's deployment blog for more tips:
Take a look at this post: http://blogs.msdn.com/bethmassi/archive/2007/06/06/login-form-parameterized-queries-part-2.aspx