Using full-blown SQL Server instead of SQL Server Express Edition in Visual Studio / IIS

Dear Diary

Today kicked off rather troublesome. After installing the developer edition of SQL Server 2008 I've decided to get rid of the SQL Server Express Edition. Why would I want to run multiple versions of SQL Server on the same machine? This turned out not to be a very good idea, especially when you are about developing ASP.NET applications using Visual Studio. Once you try to run an ASP.NET application which requires some kind of role management and/or personalization (Web Parts do), you will run into this pretty error:


Yes, the local IIS is still referencing the SQL Server Express datasource. One would assume that you could change those settings somewhere within Visual Studio as you can change the Port of the ASP.NET application. Well… Don’t waste time as I did. It is not that easy, here is what you need to do:

Create a new Database dedicated to the IIS, for the sake of simplicity we’ll call it “IIS”:

Create a new IIS DatabaseCreate a new IIS Database

Locate and run “aspnet_regsql.exe”. In my case it is "C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe". Here you want to stick with the first option (Configure SQL Server for application services) and point it to the freshly created database on the next and let the configuration tool initialize the empty db.

Initialize the new DatabaseInitialize the new Database

Almost done, you still need to point the LocalSqlServer configuration not to use the SQLEXPRESS Server but the dedicated Database. Locate the machine.config and provide the connection string to the IIS Database:

Modify machine.configModify machine.config

I am using a 64bit Version of Windows 7, so I had two different (32bit vs. 64bit) versions of that configuration file. I’ve changed both of them.