Erwyn van der Meer

Does SQL Server 2005 Express Edition support features that are not supported by other editions, like Developer Edition?

I didn't know, but I ran into a problem today while trying out the Personal Web Site Starter Kit. In the web.config it had this connection string:

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />

Since my SQL Server 2005 Developer Edition instance is named SQL2005 I changed this to:

<add name="LocalSqlServer" connectionString="Data Source=.\SQL2005;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />

When trying to run the ASP.NET application, I got this exception:

[SqlException (0x80131904): The user instance login flag is not supported on this version of SQL Server. The connection will be closed.]

Googling for information gave me an answer in a forum by Scott Forsyth:


So I had to install Express Edition to get the application to work. It also means, the answer to the question in the first line is YES.

After the installation I had three instances of SQL Server running:

• SQL Server 2000 Standard Edition
• SQL Server 2005 Developer Edition
• SQL Server 2005 Express Edition

These showed up in Process Explorer as three separate processes all running a version of sqlservr.exe. A little investigation, that involved comparing the digital signatures, showed that 2005 Developer and Express Edition have identical binaries for sqlservr.exe even though both are installed in a separate location. What was different is that the Developer instance was running under the local SYSTEM account whilst the Express instance was running under the local NETWORK SERVICE account.

Something interesting happened when I started the web application when connected to the Express Edition. A new sqlservr.exe process was fired up. This time running under the interactively logged-on account named Erwyn. When looking at the open handles I saw it had opened the master database in the Application Data directory for that user: P:\Documents and Settings\Erwyn\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\master.mdf. This explains what the User Instance=True part in the connection string does. I think it is supposed to increase security in a shared hosting environment to allow lower priviliged accounts to dynamically attach databases. In my case this dynamically created SQL Server instance actually got more rights than the normal one, because the Erwyn account has more rights than the NETWORK SERVICE account on my computer. This happened because the web application was running in the ASP.NET Development Server. This process runs under my interactively logged-on account and not under the ASPNET account. So I configured the ASP.NET application to run in IIS 5.1. When connecting to the application, another sqlservr.exe process was started. This process ran under the low-privileged ASPNET account.

Something I noticed is that the user instances of SQL Server Express did not spin down automatically when no longer in use. Also the user instances kept running when I restarted the SQL Server Express service.

The connection to the user instance from the process running the data-access code (aspnet_wp.exe or WebDev.WebServer.exe) is done using named pipes. Something like \Device\NamedPipe\61A9EC07-5FDC-45\tsql\query.

The next thing I tried was to get Developer Edition to spin up a user instance. I copied the command-line parameters for a user instance spun up by Express Edition and changed the path to the Developer Edition binary. So I ran this from the command line:

"C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqlservr.exe" -U"C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Template Data" -d"P:\Documents and Settings\Erwyn\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"\master.mdf -l"P:\Documents and Settings\Erwyn\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"\mastlog.ldf -e"P:\Documents and Settings\Erwyn\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"\error.log -c -SSQLEXPRESS -s61A9EC07-5FDC-45 -w60

Notice how the value of the -s parameter is the name of the named pipe. This worked just fine. WebDev.WebServer.exe had troubles connecting to the broken named pipe, but the user instance was up-and-running.

So this still begged the question: what is the hidden flag to let Developer Edition refuse to start a user instance when asked to in the connection string. Remember, its binary is identical to that of SQL Server Express.

So I manually created a new service for the Developer Edition binary and started it:

sc create testsvc binpath= "\"C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqlservr.exe\" -sSQLEXPRESS" type= own type= interact
net start testsvc

This indeed gave me an Express Edition-like instance capable of starting user instances. The key is the -sSQLEXPRESS command line argument. This tells SQL Server to use the SQLEXPRESS settings tree in the registry. Something in there tells sqlservr.exe how it should act.

Next I cloned the registry settings for the SQLEXPRESS instance to SQLEXPRESS2 and created another service for it. This amounts to manually creating another SQL Server instance. I also changed all the paths in that part of the registry to point to the Developer Edition installation. After changing the connection string in the ASP.NET application to SQLEXPRESS2, I connected to another Express Edition like instance, capable of starting user instances.

After some further experimentation I nailed it down to exactly one registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\Setup\checksum. Toggling its (binary) value between the Express Edition and Developer Edition values enables and disables the user instance feature for a Developer Edition installation.

The conclusion is: there is absolutely no technical reason why Developer Edition cannot support creating user instances, yet the Developer Edition refuses ;(

BTW: If you wonder what the point of all this is. I dunno. I was just inspired by Mark Russinovich's search that revealed the Sony Music Rootkit to go hunting on my machine.
Filed under: .NET