Connecting to a remote SQL express instance

Connecting from VS 2005 to a SQL express instance on another machine took me some puzzling. The sqlexpress weblog has a recipe how to get that done
Which did not work as written down (most likely because it’s addressing
previous CTP’s not the release build) but provided enough info to get
it to work on my machine. Let me share this.

SqlExpress is installed as a part of VS. To get some tools I installed SQL server 2005 pro, a present from the launch party,
on top of that. By default this server instance does not accept any
connections from another machine. You switch that on using the SQL
Server configuration manager. After a default install only the shared
memory protocol is switched on. Activate the TCP/IP protocol:

Now the sql server instance will accept a remote connection. As you
can see in the dialog it will listen on all IP addresses on port 1070.
You need this information to connect from VS.

The server name is more complex than I was used to. It consists out of three parts: The machine name (Peckoltia), the named instance of SQL server (SQLexpress) and the port number (1070).

This entry was posted in Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://www.dataportweb.com rg
  • http://martinlaurin@brimich.com Martin Laurin

    thank you, it helped me a lot too

  • SQ

    Thanks so much, didn’t know about the specifying the port number!!

  • http://www.icooper.co.uk iancooperman

    Thank you so much, i spent 3 hours this afternoon with this problem before finding your post via Google. Thank you!

  • LV

    Thanks your solution saves me from hours of trying to figure it out myself. I gave up until I found this solution using google. I was missing the port

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    What do you want to do? This is about tools like the sql manager and VS connecting to a database instance. You’re talking about DB instances taliking to each other.

  • KJS

    I suppose there is no way to do the opposite: that is, connect to a SQL Server Pro instance from SQL Express?

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    You’re welcome :) Glad it helped.

  • Chris

    Thank you so so so much. I can’t tell you how much time I wasted on this problem before I found this. Thank you.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    There is no way that a client app can do something about the server’s firewall. That would be the ultimate hack :)

  • Steve

    Very good as far as it goes, but does not explain how to connect to a remote sql express from my compiled .net application. At present I have to disable the firewall on the machine bearing the sql express, which is all very well in the developement environment, but not good for the installation situation.

    Steve