Manually Migrating Users (3.3.5 -> 4.4.1) Revisited
This blog post is actually a revisit regarding the manual migration of users from DNN 3.3.5 -> 4.4.1. I am revisiting this topic due to some script errors that individuals have noted experiencing while migrating user accounts. If you are migrating to a clean installation of 4.4.1 you shouldn't have any troubles using the old blog post here. This new posting simply adds additional duplicate checking to ensure that the copied user accounts do not exist in the destination database. Added sections to the scripts
will be noted with a comment prior to the entry in the WHERE clause. Please
NOTE this SHOULD prevent the number of script errors encountered when trying to
merge records into an existing database but it CANNOT be considered a perfect match.
If migrating into a heavily used new database you will need to take a different
approach with the DNN users and userportal tables to ensure unique values.
If there is enough demand for that type of a tutorial I will work to get one setup!
Also based on feedback I have received you CANNOT use this script as is for any DNN installation 3.2 or older.
It is possible to copy users from a DNN 3.x installation to a DNN 4.x
installation manually and this article will explain the processs needed to
complete this task. Please note there are a number of assumptions and
requirements listed below, please TAKE all of them seriously as making
modifications to your database can have tragic effects. Also, all scripts
listed here are in their inital stages, they do NOT handle conflict items such
as duplicate user accounts or anything of that nature, therefore it is assumed
that you are loading all users into a blank 4.x instllation.
Setup Requirements
To successfully copy users from 3.x -> 4.x following the below tutorial you
must ensure that the following items are complete in your configuration. First
you must ensure that the validation and decryption keys are the same for both
applications. Secondly you must know the application name for your 4.x
installation. Lastly you must have both databases on the same server, you can
transfer data cross server, however that is outside of the scope of this
article.
All examples below are showing a conversion from 3.3.5 -> 4.4.1 with databse
names of dnn335 and dnn441. Prior to following the steps below you should make
backup copies of BOTH databases. REMEMBER, there are many points for potential
failure by performing these actions and I am NOT responsible for any data loss
or corruption you may encounter while trying to perform this data copy
NOTE REGARDING BELOW SQL: All SQL Examples include full database names
based on the dnn335 and dnn441 database names. You MUST change these names to
reflect your databse names for these scripts to work successfully.
The Process
Once you have validated that your environment is setup in a manner that will
allow you to follow this process it is time to get started. First we need to
find the applicationid for the new DNN installation. To do this run the
following script replacing 'DotNetNuke' with your application name. (by default
DotNetNuke is the value set).
SELECT ApplicationId
FROM dnn441.dbo.aspnet_applications
WHERE ApplicationName = 'DotNetNuke'
Now that you have noted your ApplicationId it is time to start the data copies.
We will first move the records from the aspnet_users table, when we move our
records we will NOT be moving any records for the host or admin accounts to
avoid any potential conflicts with accounts. Below is the query used to
copy the users records from our dnn335 database to the dnn441 database.
NOTE: you will need to change the ApplicationId value that is hard coded in the
query to be that of your application, which you researched above.
INSERT INTO dnn441.dbo.aspnet_users (ApplicationId, UserId, username,
LoweredUsername, IsAnonymous, LastActivityDate)
SELECT 'E3AD66EA-58E6-46A5-AC80-0904A9FB8E4D' as "ApplicationId", userid, username,
loweredusername, IsAnonymous, lastActivityDate
FROM dnn335.dbo.aspnet_users
WHERE username NOT LIKE 'Host'
AND username NOT LIKE 'admin'
--New script addition to check for duplicates..
AND userid NOT IN (SELECT DISTINCT userid
FROM dnn441.dbo.aspnet_users)
The next step of the process is to copy over all aspnet_membership information
for the users. The information contained in the membership table is what
controls the users ability to login to the site, including password and
password salt values. Below is the script needed to copy the user accounts.
NOTE: As with the last script be sure to change the hard set ApplicationId
value to the value you found for your application.
--Insert Statement to place new records into the DNN 44l aspnet_membership table
INSERT INTO dnn441.dbo.aspnet_membership(applicationid, userid, [password], PasswordFormat,
PasswordSalt, Email, LoweredEmail, isApproved, isLockedOut, CreateDate,
LastLoginDate, LastPasswordChangedDate, lastLockoutDate, FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart, Comment)
SELECT 'E3AD66EA-58E6-46A5-AC80-0904A9FB8E4D', u.userid, m.[password], m.passwordformat,
m.PasswordSalt, m.Email, m.LoweredEmail, m.isApproved, m.islockedout, m.createdate,
m.LastLoginDate, m.lastPasswordChangedDate, m.lastLockoutDate, m.FailedPasswordAttemptCount,
m.FailedPasswordAttemptWindowStart, m.FailedPasswordAnswerAttemptCount,
m.FailedPasswordAnswerAttemptWindowStart, m.comment
FROM dnn335.dbo.aspnet_users u
INNER JOIN dnn335.dbo.aspnet_membership m
ON (u.userid = m.userid)
WHERE u.username NOT LIKE 'Host'
AND username NOT LIKE 'admin'
--Addition for duplicate checking
AND u.userid NOT IN (SELECT DISTINCT userid
FROM dnn441.dbo.aspnet_membership)
Next we will want to copy over any profile information that might have been
stored for the users in the aspnet_profile table. Below is a script to copy
over all profile information.
--Script to copy over any potential user profile information stored for a user
INSERT INTO dnn441.dbo.aspnet_profile (UserId, PropertyNames, PropertyValuesString,
PropertyValuesBinary, LastUpdatedDate)
SELECT u.UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate
FROM dnn335.dbo.aspnet_profile p
INNER JOIN dnn335.dbo.aspnet_users u
ON (p.userid = u.userid)
WHERE u.username NOT LIKE 'Host'
AND u.username NOT LIKE 'admin'
--Script addition for duplicate checking
AND u.userid NOT IN (SELECT DISTINCT userid
FROM dnn441.dbo.aspnet_profile
The next step is to copy over the DNN user information. This query is a bit more
complicated than you would expect. The reason for this is a tendency for older
versions of DNN to not correctly remove user accounts. To ensure that we ONLY
copy over active user accounts we will perform a join to the aspnet_users table
this way we only copy over user accounts that have all needed supporting
information. Also we will be setting the database to temporarily allow for
IDENTITY_INSERT which will allow us to insert records into an identity field.
NOTE: This requires that there be NO additional accounts other than host and
admin in your new portal, if there are more user accounts this step of the
process could fail.
--Script to copy over all DNN user table information!
SET IDENTITY_INSERT dnn441.dbo.users ON
INSERT INTO dnn441.dbo.users (UserId, UserName, FirstName, LastName, IsSuperUser,
AffiliateId, Email, DisplayName, UpdatePassword)
SELECT dnnu.UserId, dnnu.username, dnnu.firstname, dnnu.lastname,
dnnu.isSuperuser, dnnu.AffiliateId, dnnu.Email, dnnu.DisplayName,
dnnu.UpdatePassword
FROM dnn335.dbo.users dnnu
INNER JOIN dnn335.dbo.aspnet_users u
ON (dnnu.username = u.username)
WHERE u.username NOT LIKE 'Host'
AND u.username NOT LIKE 'admin'
--Addition for duplicate check (Not the best option, but it works...)
AND u.username NOT IN (SELECT DISTINCT username
FROM dnn441.dbo.users)
AND u.Userid NOT IN (SELECT DISTINCT userid
FROM dnn441.dbo.users)
SET IDENTITY_INSERT dnn441.dbo.users OFF
The final step is to copy over the UserPortal table. This is the table that maps
a users permission to a specific portal. Since your setup currently only has
one portal, I will be defaulting the script to automatically set users to only
access your primary portal. If you have other portals or needs you will need to
modify this query. As with the last query this will have a few joins in it to
ensure that we only have good records for users. This script is another
where we will need to temporarily turn on IDENTITY_INSERT as we will be
inputting information into an identity field.
--Script to copy over the user portal information
SET IDENTITY_INSERT dnn441.dbo.userportals ON
INSERT INTO dnn441.dbo.userportals (UserId, PortalId, UserPortalId, CreatedDate, Authorised)
SELECT up.userid, 0 as "PortalId", up.UserPortalId, up.createdDate, up.Authorised
FROM dnn335.dbo.userportals up
INNER JOIN dnn335.dbo.users dnnu
ON (up.userid = dnnu.userid)
INNER JOIN dnn335.dbo.aspnet_users u
ON (dnnu.username = u.username)
WHERE u.username NOT LIKE 'Host'
AND u.username NOT LIKE 'admin'
--Addition for duplicate checking
AND u.userid NOT IN (SELECT DISTINCT userid
FROM dnn441.dbo.userportals
WHERE dnn441.dbo.userportals.portalid = 0)
SET IDENTITY_INSERT dnn441.dbo.userportals OFF
Testing the Transfer
To test the data transfer you can simply try to login with one of your existing
user accounts. You should be able to login without any difficulties using the
same password as you did on the old portal.
Final Thoughts
This tutorial provides you a method to copy user accounts over from a 3.3.5 installation to a 4.4.1 installation. This only covers the basics of moving the root accounts into a clean portal. To perform more detailed moves with regard to multiple portals etc additional thought will need to take place when migrating user data.
Please leave your feedback below, if you have any requests for assistance please visit my forums where I will gladly respond to any questions.
Posted by Mitchel on Saturday, March 10, 2007
Click here to post a comment