Peter's Gekko

public Blog MyNotepad : Imho { }

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

Published Jan 05 2006, 10:28 AM by pvanooijen

Filed under: Data

Comments

Steve said:

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

# August 4, 2006 4:33 AM

pvanooijen said:

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

# August 4, 2006 5:45 AM

Chris said:

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.

# November 24, 2006 3:42 AM

pvanooijen said:

You're welcome :) Glad it helped.

# November 25, 2006 2:19 PM

KJS said:

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

# December 19, 2006 5:52 PM

pvanooijen said:

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.

# December 20, 2006 2:26 PM

LV said:

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

# January 5, 2007 9:05 AM