Tuesday, 18 January 2011

Annoying issue with the ASP.NET membership databases

In order to speed up making changes to my databases during development I've set up a .cmd script that drops and recreates my project databases, and fills them with some seed data. This is pretty expedient, though in hindsight I think a solution using something like Visual Studio's database projects might be better. At any rate, I digress...

Yesterday I ran into a problem with the ASP.NET membership database tables. I kept getting this error:

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

My script uses the aspnet_regsql.exe tool to install the membership database - so this was very strange. The actual error stems from data missing in the aspnet_SchemaVersions table. Depending on what membership features you have installed you will have some of the following rows in this table:

common 1 1
health monitoring 1 1
membership 1 1
personalization 1 1
profile 1 1
role manager 1 1

The weird thing for me, though, was that this data was present! So, what was going on?

The error was only thrown by IIS, but not by the development web server in VS. Discovering this prompted me to restart IIS... and the problem went away.

In my case I figured out that the problem was that my database script had deleted all the rows in the aspnet_SchemaVersions after the database had been created. This caused the error in the first place. When I updated the script, however, the error didn't go away (in IIS) and I can only guess that this must be because this data had been cached in the IIS worker process.