System.Data.SqlClient.SqlException: Invalid column name ‘IsApproved’, ‘IsLockedOut’, ‘Comment’, ‘CreationDate’, ‘LastLoginDate’, ‘LastLockoutDate’

Got this SqlException again moving a database from a client’s production environment into development. Thought I’d write a short note on resolving it.

Invalid column name 'IsApproved'.
Invalid column name 'IsLockedOut'.
Invalid column name 'Comment'.
Invalid column name 'CreationDate'.
Invalid column name 'LastLoginDate'.
Invalid column name 'LastLockoutDate'.

Check the migration history table:

SELECT * FROM [dbOptimizelyDatabase].[dbo].[__MigrationHistory]

It is likely that the initial create of the tables used Microsoft.AspNet.Identity.EntityFramework.IdentityUser instead of the expected EPiServer.Cms.UI.AspNetIdentity.ApplicationUser.

The ContextKey column would contain EPiServer.Cms.UI.AspNetIdentity.ApplicationDbContext`1 [Microsoft.AspNet.Identity.EntityFramework.IdentityUser] rather than EPiServer.Cms.UI.AspNetIdentity.ApplicationDbContext`1 [EPiServer.Cms.UI.AspNetIdentity.ApplicationUser].

This may be resolved by removing the tables dbo.__MigrationHistory (or just the row with the identity migration if there are others), dbo.AspNetUserClaims, dbo.AspNetUserLogins, dbo.AspNetUserRoles, dbo.AspNetRoles, dbo.AspNetUsers. Then restarting the Optimizely application and attempting to login to the admin/edit interface.

Of course, any data in these tables will be lost. Also, do not forget to make a backup of your database before removing tables. A guess on how this occurred is that the NuGet package EPiServer.CMS.UI.AspNetIdentity was not installed at the time the migration to add the tables was run, thus the Microsoft.AspNet.Identity.EntityFramework.IdentityUser was used instead.

EDIT: Here is a comment by Antti Alasvuo on the problem.