Occasionally we need to transfer user accounts from existing ASP.NET membership based sites into MojoPortal

Note : if the databases are on the same server, or can be linked, then it is possible to take the SQL of the selects in the ExportASPNETUserData.bat file and use them to insert into the MojoPortal database tables. Using the .bat files will work wherever the databases are, as long as your PC can connect to both.
If you can't connect to both, run the XMLFormat and Export scripts on a machine that can connect to the ASP.NET membership database, then copy the files and run the Import script on a machine that can connect to the MojoPortal database

Note: It is possible to do the format/export/import in one script, but being able to sanity check the data before inserting is useful. You could also use staging tables to test the scripts before using the real MojoPortal tables

Note: The switches set in each file are those I found were best for the LHaR data. Change them if you feel it is appropriate for you data. See: BCP Utility Technical Reference

How-to

• Set MojoPortal to use plain text passwords
• Create a folder on your PC to contain the scripts
• In this folder, create the following .bat files:
  • CreateMojoUserTablesXMLFormats.bat
  • ExportASPNETUserData.bat
  • ImportMojoPortalUserData.bat
• In this folder create the following subdirectories:
  • data
  • formats
  • output
• Create and run the CreateMojoUserTablesXMLFormats.bat file
• Create and run the ExportASPNETUserData.bat file
  • Note: add as many user properties as required. To do this, select out each property in a separate select and then UNION the results together
  • Check the output directory for any errors
  • Check the data files have been created and contain the data you expect
• Create and run the ImportMojoPortalUserData.bat
  • Check the output directory for any errors
  • Check the database tables have the data
• Switch MojoPortal back to using encrypted passwords
• Test a few logins

CreateMojoUserTablesXMLFormats.bat

set login=-U [Username] -P [Password] -S [SQL Server Instance]
set database=[Databasename].[Schema].
set switches= -c -x
set formatDir=[Full path to formats directory with trailing slash]

bcp %database%mp_Users format nul %login% %switches% -f %formatDir%mojoUsers.xml
bcp %database%mp_UserProperties format nul %login% %switches% -f %formatDir%mojoUserProperties.xml
bcp %database%mp_userRoles format nul %login% %switches% -f %formatDir%mp_userRoles.xml

ExportASPNETUserData.bat file

set login=-U [Username] -P [Password] -S [SQL Server Instance]
set dataDir=[Full path to data directory with trailing slash]
set database=[Databasename].[Schema].
set switches= -k -c

bcp "SELECT 0 AS UserID, 1 AS SiteID, aspnet_Users.Username AS Name, aspnet_Users.UserName AS LoginName, COALESCE (aspnet_Membership.Email,aspnet_Users.UserName + '@dummyemail.co.uk' ) AS Email, aspnet_Membership.LoweredEmail AS LoweredEmail, NULL AS PasswordQuestion, NULL AS PasswordAnswer, NULL AS Gender, aspnet_Membership.IsApproved AS ProfileApproved, NULL AS RegisterConfirmGUID, aspnet_Membership.IsApproved AS ApprovedForForums, 0 AS Trusted, CASE aspnet_Roles.RoleName WHEN 'xg' THEN 0 ELSE 1 END AS DisplayInMemberList, NULL AS WebsiteURL, NULL AS Country, NULL AS [State], NULL AS Occupation, NULL AS Interests, NULL AS MSN, NULL AS Yahoo, NULL AS AIM, NULL AS ICQ, 0 AS TotalPosts, NULL AS AvatarURL, 0 AS TimeOffsetHours, NULL AS [Signature], aspnet_Membership.CreateDate AS DateCreated, aspnet_Membership.UserId AS userGUID, NULL AS Skin, 0 AS IsDeleted, aspnet_Users.LastActivityDate AS LastActivityDate, aspnet_Membership.LastLoginDate AS LastLoginDate, NULL AS LastPasswordChangedDate, NULL AS LastLockoutDate, 0 AS FailedPasswordAttemptCount, NULL AS FailedPwdAttemptWindowStart, 0 AS FailedPwdAttemptCount, NULL AS FailedPwdAnswerWindowStart, aspnet_Membership.IsLockedOut AS IsLockedOut, NULL AS MobilePin, NULL AS PasswordSalt, NULL AS Comment, NULL AS OpenIDURI, NULL AS WindowsLiveID, '77C33D82-D6F0-49ED-95A7-84C11919AD94' AS SiteGUID, NULL AS TotalRevenue, userInfo.ForeName AS FirstName, userInfo.Surname AS LastName, aspnet_Users.Username as Pwd, 1 AS MustChangePassword, NULL AS NewEmail, NULL AS EditorPreference, '00000000-0000-0000-0000-000000000000' AS EmailChangeGuid, NULL AS TimeZoneID, '00000000-0000-0000-0000-000000000000' AS PasswordResetGuid FROM %database%aspnet_Membership INNER JOIN %database %aspnet_Users ON aspnet_Membership.UserId=aspnet_Users.UserId INNER JOIN %database%userInfo on aspnet_Membership.UserId=userInfo.userId INNER JOIN %database%aspnet_UsersInRoles ON aspnet_Membership.UserId=aspnet_UsersInRoles.UserId INNER JOIN %database%aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId" queryout %dataDir%lharUsers.txt %switches% %login% -o output/usersOutput.txt

bcp "select RoleName,UserId from %database%aspnet_Roles INNER JOIN %database%aspnet_UsersInRoles on aspnet_Roles.RoleId=aspnet_UsersInRoles.RoleId" queryout %dataDir%lharUsersRoles.txt %switches% %login% -o output/userRolesOutput.txt

bcp "SELECT newid() AS PropertyID, UserId AS userGUID, [String to use as MojoPortal PropertyName] AS PropertyName, Title AS PropertyValueString, NULL AS PropertyValueBinary, GETDATE() AS LastUpdatedDate, 0 AS IsLazyLoaded FROM %database%UserInfo
UNION ALL
SELECT newid() AS PropertyID, UserId AS userGUID, [String to use as MojoPortal PropertyName] AS PropertyName, Title AS PropertyValueString, NULL AS PropertyValueBinary, GETDATE() AS LastUpdatedDate, 0 AS IsLazyLoaded FROM %database%UserInfo" queryout %dataDir%lharUserProfiles.txt %switches% %login% -o output/userPropertiesOutput.txt

ImportMojoPortalUserData.bat

set login=-U [Username] -P [Password] -S [SQL Server Instance]
set database=[Databasename].[Schema].
set dataDir=[Full path to data directory with trailing slash]
set switches= -k -R
set formatDir=[Full path to outputs directory with trailing slash]

bcp %database%mp_users IN %dataDir%lharUsers.txt %login% %switches% -f %formatDir%mojoUsers.xml -o output/importUsers.txt
bcp %database%mp_userProperties IN %dataDir%lharUserProfiles.txt %login% %switches% -f %formatDir%mojoUserProperties.xml -o output/importUserProperties.txt
bcp %database%mp_userRoles IN %dataDir%lharUsersRoles.txt %login% %switches% -f %formatDir%mp_userRoles.xml -o output/importUserRoles.txt