Wednesday, December 06, 2006

Connecting to a Remote SQLExpress

SQLExpress is both a blessing and a curse. It's nice to have a free alternative to SQLServer for development, but we all have to learn a new set of utilities to administer SQLExpress. When I first tried to connect to an instance of SQLExpress on a virtual machine with this:

C:\home\mfincher>osql -Usa -P"MyPassword" -S "MyServer\SqlExpress"

The result was:

[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).

The solution took three steps to remove the default locked-down settings:
1. Select "Start/Programs/Microsoft SQL Server 2005/Configuration Tools/SQL Server Configuration Manager"
Select "SQL Server 2005 Network Configuration/Protocols for SQLEXPRESS" in the left pane. In the right pane, enable "Named Pipes" and "TCP/IP".
2. Select "Start/Settings/Control Panel/Services" panel.
Right click on "SQL Server Browser". In the "General" tab set "Startup type:" to be "Automatic".
In the Services panel make sure the "SQL Server (SQLEXRESS)" service is running.
3. In "Microsoft SQL Server Management Studio Express" (see previous post on how to get), right click on the machine instance (the top item) and select "Connections". The check the box labeled "Allow remote connections to this server".

It's thrilling to see Microsoft take such pains to have a more secure system by default, but it would be nice to have a single, obvious checkbox to allow the system to talk with the rest of the world.

No comments: