Connecting to a SqlExpress instance with Wix

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:

<sql:SqlDatabase Id="DbNameRef"
                 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:

CreateDatabase:  Error 0x80004005: failed to create SQL database but continuing, error: unknown error, Database: [DATABASE_NAME]

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:

<sql:SqlDatabase Id="DbNameRef"
                 Database="DbName"
                 Server="."
                 Instance="SQLEXPRESS"
                 CreateOnInstall="yes"
                 DropOnUninstall="yes"
                 User="MySQLUser"
                 ContinueOnError="yes">