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 6 and 2 and type the answer here:
  • Post
  • PingBack from http://blog.a-foton.ru/2008/09/enabling-remote-sql-express-2008-network-connections-on-vista/

  • Hi Beth

    Just 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,

    Logan

  • Hi Logan,

    Yep I think this is the same thing you need to do for SQL 2005 Express on Vista.

    -B

  • 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 lol

    Thanks again, and keep shelling out these awesome tips will ya.

    Logan

  • 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!

  • Thank you very much. This is just what i need. I've been trying a lot of things but they never worked.

    TDC

  • You're a life-saver!  I was banging my head against the wall and you've stopped the headache!!!!

  • 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!

  • Does this work on Workgroups on a Local Network?

  • 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..)

  • Fixed it -> the connection string on the DataSet was invalid :-) now it works perfectly! Again, your How Do I videos are great!

  • Hi Carl,

    It should. That's what I have here when I tested it.

    Cheers,

    -B

  • just another thank you from another fellow programmer who's on his way now thanks to this article

    thanx!

  • 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>:<name\instance,<port>

    e.g:

    tcp:myMachine\MydbInstance,49172 - this if you want to use TCP/IP.

    Hope this will complete Beth's very welcome blog a little.

  • Thanks, this saved me a lot of time!

Page 1 of 4 (59 items) 1234