Brian Mishler on .Net

How to import existing Asp.Net Membership data into CommunityServer

I am in the process of enhancing my existing web site with the forums capabilities offered by CommunityServer. Since I already have several thousand members registered on my site, I would like to either migrate their data into Community Server or find a clean way to share authentication information so my existing members do not have to maintain separate passwords for the existing customer-service and new community server functions.

When I saw that CommunityServer was using the Asp.Net membership provider model for authentication, I became hopeful that I might be able to share a common membership database with my own web application since I am using Microsoft's SqlMembershipProvider.  Unfortunately, I found that CommunityServer is using a non-standard membership database schema (by default) which is incompatible with Microsoft's SQL providers, so I would not be able to simply point my provider to their membership tables. I did look into Telligent's Asp.Net Forms Authentication module but, as was explained to me, that module provides access for existing CommunityServer users and, of course, my users were not in the CommunityServer membrship database. In any case, I was curious to see if I could import my existing membership users into Community Server.

Part 1 - Import existing membership users into the CommunityServer database

Using SQL Server Import and Export Wizard in SQL Server Management Studio (SQL Server 2005), I was easily able to append the User and Membership data from my existing application into the corresponding tables in the CommunityServer database.

Since AspNet Membership depends on AspNet Users and they both depend on AspNet Applications, I found that the Foreign Key constraints would not let me just check everything off and run the wizard. By running it first for AspNet Applications and then for AspNet Users, I was able to then import AspNet Membership as shown below:

Step 1: Run the wizard for AspNet Applications.

Step 2: Run the wizard again for AspNet Users.

Step 3: Run the wizard one more time for AspNet Membership.

Although my data was now physically located in the same database, a bit of massaging was still necessary to make it usable by CommunityServer. CommunityServer has its own user-related tables and also my user information is stamped with a distinct ApplicationId.

Part 2 - Consolidate the ApplicationID

My data was originally created using mostly generic provider settings and may therefore be distinguished by a unique ApplicationId in the AspNet Applications, AspNet Users, AspNet Membership and AspNet Roles tables. The data which was pre-populated by the CommunityServer Setup Wizard is uniquely identified by its own ApplicationID. Further, the custom providers defined in CommunityServer’s web.config file are configured to use the ApplicationID associated with the applicationName “dev”. As shown here, I actually had two applications in my database at this point.

In order for CommunityServer's provider to "see" the membership data I just imported, I would have to change the ApplicationId in each AspNet Membership and AspNet Users record to the one corresponding to the "dev" applicationName.

So, here is the procedure for modifying the imported data to use the applicationID corresponding to the applicationName “dev”

1. Issue the following statements in SQL Server:

DECLARE @DevApplicationID uniqueidentifier

DECLARE @SlashApplicationID uniqueidentifier

SELECT @DevApplicationID=applicationID FROM AspNet Applications WHERE ApplicationName='dev'

SELECT @SlashApplicationID=applicationID FROM AspNet Applications WHERE ApplicationName='/'

UPDATE AspNet Membership SET ApplicationID=@DevApplicationID WHERE ApplicationID=@SlashApplicationID

UPDATE AspNet Users SET ApplicationID=@DevApplicationID WHERE ApplicationID=@SlashApplicationID

2. Delete the record in AspNet Applications for ApplicationName=”/”. This record is now unused.

DELETE FROM AspNet Applications WHERE ApplicationName='/'

At this point, all of my existing member records are physically contained in CommunityServer's Asp.Net membership tables and have been updated with the same ApplicationID that is referred-to by their custom membership providers. Still, CommunityServer cannot use my imported data since its custom user tables have not been loaded.

Part 3 - CommunityServer’s Roles

I noticed that when a new “guest” user is added into the system, he/she is added to the “Everyone” role. I was not sure if this is a requirement, but it was simple enough to do this for each of my existing (legacy) users with the following SQL Statement:

DECLARE @DevApplicationID uniqueidentifier

SELECT @DevApplicationID=applicationID FROM AspNet Applications WHERE ApplicationName='dev'

DECLARE @EveryoneRoleID uniqueidentifier

SELECT @EveryoneRoleID=RoleID FROM AspNet Roles WHERE loweredRoleName='everyone' AND ApplicationID=@DevApplicationID

INSERT INTO AspNet UsersInRoles (UserID,RoleID) SELECT UserID,@EveryoneRoleID FROM AspNet Users WHERE UserID NOT IN (SELECT UserID from AspNet UsersInRoles)

Note that @EveryoneRoleID is the value in the roleID (GUID) column that corresponds to the “everyone” role in the AspNet roles table.

Part 4 - Importing data into CommunityServer’s User tables

I really like the fact that CommunityServer leverages the AspNet membership providers for generic mainstream authentication functions. I believe this is a trend we will see more of now that Microsoft has established a de-facto standard. Telligent has done the smart thing by extending the base providers with their own customized versions that provide application-specific functionality and utilize additional data tables.

While I was not able to find any documentation on the Internet, I was able, through trial and error, to figure out which minimal data is required in the CommunityServer tables.

The cs users table maps the AspNet membership system’s UserID to the userID which is used internally by CommunityServer. Whereas Asp.Net uses a GUID to uniquely identify users, CommunityServer’s userID is an Integer which is autonumbered. Since most of the fields defaulted to good values, I was able to populate the CommunityServer Users table with the following SQL Statement:

INSERT INTO cs users (membershipID) SELECT UserID from AspNet Membership where UserID not in (SELECT MembershipID as UserID from cs users)

Notice that CommunityServer knows the AspNet membership system’s UserID as “MembershipID” in order that it can use the name “UserID” for its own purposes.

The cs UserProfile table includes colums for tracking the much of the same profile data that is maintained in the AspNet Profile table. I am not sure why it is being kept in both places but I am guessing that the cs UserProfile table is left over from previous implementations and still used as the primary storage location for profile data. In any case, a record is required for each user and the minimum (non-default) data appears to be the UserID, MembershipID and a settingsID.

The UserID and MembershipID were pretty handy for each user and I discovered that the settingsID corresponded to the key field value in the cs SiteSettings table. In my case, there was a single record whose value was 1000. Thus, I was able to initialize the cs UserProfile table with the following SQL Statement:

INSERT INTO cs userprofile (membershipID,UserId,settingsID) SELECT MembershipID,userid,1000 FROM cs users WHERE userid NOT IN (SELECT userid FROM cs userprofile)

Now able to log in

Having tweaked the ApplicationID, imported my existing users, created “everyone” role records for each and inserted records into the cs users and cs userprofile tables, I was now able to log into CommunityServer using an account from my existing web site.

Moving forward, I recognize that the CommunityServer membership system will immediately become unsynchronized with my own. While I am considering using the Asp.Net Forms Authentication module (possibly with some extra work on my part) I am opposed to duplication of any type of information, especially user credentials.

In the meantime, I believe there are plenty of folks that might wish to migrate existing membership data over to CommunityServer and thereafter let CommunityServer handle user credential management. Hopefully, this article will help. If anyone knows more or has insight on this subject, I invite your suggestions and comments.