How to Migrate from Membership to Origam Based Authentication

docs
1

How to Migrate from Membership to Origam Based Authentication

This article describes mapping from AspNetMembership tables to OrigamUser table.

OrigamUser Column DataType Allow Nulls AspNetMembership Column Note
refBusinessPartnerId uniqueidentifier no Value has to be looked for in Origam BusinessPartner table. It should be searched by UserName.
Password nvarchar(100) yes

aspnet_Membership.Password

aspnet_Membership.Salt

Values from Password and Salt column need to be combined into one string with | as separation character: password|salt.
RecordCreatedBy uniqueidentifier yes
RecordUpdatedBy uniqueidentifier yes
Id uniqueidentifier no Primary key.
RecordCreated datetime yes aspnet_Membership.CreateDate
RecordUpdated datetime yes
IsLockedOut bit no aspnet_Membership.IsLockedOut
LastLoginDate datetime yes aspnet_Membership.LastLoginDate
LastLockoutDate datetime yes aspnet_Membership.LastLockoutDate
FailedPasswordAttemptCount int no aspnet_Membership.FailedPasswordAttemptCount
FailedPasswordAttemptWindowStart datetime yes aspnet_Membership.FailedPasswordAttemptWindowStart
UserName nvarchar(255) yes aspnet_Users.UserName
EmailConfirmed bit no aspnet_Membership.IsApproved

The following sql script could be used to migrate users from aspnet membership database to ORIGAM user database. The script expects to be run connected to your ORIGAM data database and AspNetMembership database to be named as ‘aspnetdb’. There could be a problems with different collations of the two databases. In the following example script it’s solved by added COLLATE expressions. However if the collation of your databases (ORIGAM data and AspNetMembership) are the same, then all the COLLATE expressions should be ommitted.

Sql Script for migration of users

INSERT INTO OrigamUser
(
    refBusinessPartnerId
    , Password
    , RecordCreated
    , RecordUpdated
    , Id
    , IsLockedOut
    , LastLoginDate
    , LastLockoutDate
    , FailedPasswordAttemptCount
    , FailedPasswordAttemptWindowStart
    , UserName
    , EmailConfirmed
)
SELECT
    (SELECT Id From BusinessPartner bp WHERE bp.UserName = au.UserName COLLATE Czech_CI_AS)
    , CONCAT(am.Password COLLATE Czech_CI_AS, '|' COLLATE Czech_CI_AS, am.PasswordSalt COLLATE Czech_CI_AS)
    , am.CreateDate
    , NULL
    , NEWID()
    , am.IsLockedOut
    , am.LastLoginDate
    , am.LastLockoutDate
    , am.FailedPasswordAttemptCount
    , am.FailedPasswordAnswerAttemptWindowStart
    , au.UserName
    , am.IsApproved
FROM
    aspnetdb.dbo.vw_aspnet_Users au
    INNER JOIN aspnetdb.dbo.aspnet_Membership am ON am.UserId = au.UserId
WHERE
    (SELECT Id From BusinessPartner bp WHERE bp.UserName = au.UserName COLLATE Czech_CI_AS) is not null
2