The other day I came across a bit of a strange issue when trying to delploy a backup to a fresh SqlExpress 2008 R2 instance. I had setup the Wix Sql database tag using a loopback address:
Database="DbName"
Server="127.0.0.1"
Instance="SQLEXPRESS"
CreateOnInstall="yes"
DropOnUninstall="yes"
User="MySQLUser"
ContinueOnError="yes">
but for some reason it wasn’t connecting at all just erroring with a most helpful error message:
I enabled TCP/IP so that I could use sql profiler and re-ran the installer hey presto guess what it worked! Obviously as SqlExpress only installs with “Shared memory” enabled this was the issue, it couldn’t connect. I tried the sqlcmd method of using “lpc:” as a prefix but still no joy. It turns out that if you cannot use a hostname or ip for the servername to switch over to shared memory’ you have to use “.” as the server name:
Database="DbName"
Server="."
Instance="SQLEXPRESS"
CreateOnInstall="yes"
DropOnUninstall="yes"
User="MySQLUser"
ContinueOnError="yes">