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.
System.Data.SqlClient.SqlException:
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.
