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.


Jaimal said...
This comment has been removed by the author.
Jaimal Chohan said...

Seriously, Øyvind, use an 'real' ORM (i.e. not L2S). You can then flick a switch to rebuild the DB on App_Start AND fill it up with test data, in c#, no messing about with scripts.

Øyvind Valland said...

Hi Jaimal,

Who said I'm using L2S or another 'not real' ORM? I'm using NHibernate, and the schema is auto-generated. Even so, it doesn't do the full job because NHibernate cannot help me with indexes, constraints, triggers, etc. So I need other tools. In lieu of a db source control mechanism I have a few scripts that I coordinate with the auto-generated schema.

But the post isn't about ORM. It is about a small issue you may encounter if you tamper with the aspnet membership database underneath a running web application. Changes to the membership database objects must be scripted because you manage these with the aspnet_regsql.exe tool. Even if I had an NHibernate provider for membership I couldn't auto-generate the whole schema because of db-level constraints and indexes that are required.

Lastly, in my not so humble opinion, data generation and seeding using App_Start is a fundamentally bad idea. Separation of concerns? Would you release an app with this kind of code in your App_Start? Even if you used a debug directive you'd litter your app with code that shouldn't be there. It may be expedient on a very small single-developer project, but I still wouldn't use that approach.