Brian Mishler on .Net - CommunityServer

Using existing Asp.Net Membership data with CommunityServer

Brian Mishler — Mon, 10 Apr 2006 02:12:06 GMT

I really like the new Version 2.0 of CommunityServer and have been trying to figure out how to best integrate it with my existing web site. 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.

I figured I would ask Telligent about the Asp.Net Forms Authentication Module for CommunityServer so I initiated contact with their Sales team. While I waited for a response, I decided to open up the database and figure out what is going on inside.

The first thing I noticed was that the CommunityServer database includes the tables and stored procedures used by the AspNet membership system. These include AspNet Applications, AspNet Membership, AspNet Profile, AspNet Users and several more.

A quick look at web.config confirmed that CommunityServer has created custom providers that (presumably) inherit from SQLMembershipProvider, SQLRoleProvider and SQLProfileProvider.

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.

With hopes of sharing common membership information, I set out to understand the extra information required by CommunityServer for records that were initiated from my own system. After a bit of trial and error I found that CommunityServer expects each user to be a member of the "everyone" role in the AspNet membership system and that a record is required for each member in the cs users and cs profiles tables which maintain Community-server specific informaiton on each member. At this point, I was feeling pretty confident about my chances of sharing CommunityServer's membership system with my own, so I imported my own data into the CommunityServer database. After doing so, I was able to log into CommunityServer with one of my imported user accounts and I figured I was done.

Knowing a thing or two about Asp.Net providers, I reset the "ApplicationName" attribute for each of the three providers (SqlProfileProvider, SqlRoleProvider and SqlMembershipProvider) in my own application's web.config. This would need to be "dev" in order that any new records created by my system would wind up with the same ApplicationID that is used by CommunityServer's providers. Finally, I changed my connection string to point over to CommunityServer and then "let 'er rip".

Curses! It didn't work. My application was dead and I was being told that Procedure or function aspnet Membership GetPasswordWithFormat has too many arguments specified.

Hmmm. I remember this. It was the same error message I got when I upgraded from the .Net 2.0 Beta to the live version last October. Sure enough, as I looked into it a little more, I found that CommunityServer 2.0 is shipping with the beta versions of the Asp.Net membership providers. A lot of early adopters will remember this situation: Microsoft told us that they reserved the right to change the product prior to final release--and they did. The membership tables did not change but many of the stored procedures did. For the folks that built sites with the basic providers, the issue was easily resolved with the AspNet regSql utility that comes with the framework. (It can be found at c:\windows\microsoft.net\framework\v2.0.50727\aspnet regsql.exe.)

Here are (most of) the differences:

Stored Procedure	Beta Version Parameters	Version 2.0 Parameters
aspnet Membership GetPasswordWithFormat	@ApplicationName NVARCHAR(256)	@ApplicationName nvarchar(256)
Provider Exception: Procedure or function aspnet Membership GetPasswordWithFormat has too many arguments specified	@UserName NVARCHAR(256)	@UserName nvarchar(256)
		@UpdateLastLoginActivityDate bit
		@CurrentTimeUtc datetime

aspnet Membership UpdateUserInfo	@ApplicationName NVARCHAR(256)	@ApplicationName nvarchar(256)
Provider Exception: Procedure or function aspnet Membership UpdateUserInfo has too many arguments specified	@UserName NVARCHAR(256)	@UserName nvarchar(256)
	@IsPasswordCorrect BIT	@IsPasswordCorrect bit
	@UpdateLastLoginActivityDate BIT	@UpdateLastLoginActivityDate bit
	@MaxInvalidPasswordAttempts INT	@MaxInvalidPasswordAttempts int
	@PasswordAttemptWindow INT	@PasswordAttemptWindow int
	@TimeZoneAdjustment INT	@CurrentTimeUtc datetime
		@LastLoginDate datetime
		@LastActivityDate datetime

aspnet Profile GetProperties	@ApplicationName NVARCHAR(256)	@ApplicationName nvarchar(256
Provider Exception: Procedure or Function 'aspnet Profile GetProperties' expects parameter '@TimeZoneAdjustment', which was not supplied	@UserName NVARCHAR(256)	@UserName nvarchar(256)
	@TimeZoneAdjustment INT	@CurrentTimeUtc datetime


aspnet Membership GetUserByName	@ApplicationName NVARCHAR(256)	@ApplicationName nvarchar(256)
Provider Exception: Procedure or Function 'aspnet Membership GetUserByName' expects parameter '@TimeZoneAdjustment', which was not supplied	@UserName NVARCHAR(256)	@UserName nvarchar(256)
	@TimeZoneAdjustment INT	@CurrentTimeUtc datetime,
	@UpdateLastActivity BIT = 0	@UpdateLastActivity bit = 0

aspnet Membership GetUserByUserId	@UserId UNIQUEIDENTIFIER	@UserId uniqueidentifier
Provider Exception: Procedure or Function 'aspnet Membership GetUserByUserId' expects parameter '@TimeZoneAdjustment', which was not supplied	@TimeZoneAdjustment INT	@CurrentTimeUtc datetime
	@UpdateLastActivity BIT = 0	@UpdateLastActivity bit = 0


aspnet Membership CreateUser	@ApplicationName NVARCHAR(256)	@ApplicationName nvarchar(256)
Provider Exception: Procedure or Function 'aspnet Membership CreateUser' expects parameter '@TimeZoneAdjustment', which was not supplied	@UserName NVARCHAR(256)	@UserName nvarchar(256)
	@Password NVARCHAR(128)	@Password nvarchar(128)
	@PasswordSalt NVARCHAR(128)	@PasswordSalt nvarchar(128)
	@Email NVARCHAR(256)	@Email nvarchar(256)
	@PasswordQuestion NVARCHAR(256)	@PasswordQuestion nvarchar(256)
	@PasswordAnswer NVARCHAR(128)	@PasswordAnswer nvarchar(128)
	@IsApproved BIT	@IsApproved bit
	@TimeZoneAdjustment INT	@CurrentTimeUtc datetime
	@CreateDate DATETIME = NULL	@CreateDate datetime = NULL
	@UniqueEmail INT = 0	@UniqueEmail int = 0
	@PasswordFormat INT = 0	@PasswordFormat int = 0
	@UserId UNIQUEIDENTIFIER OUTPUT	@UserId uniqueidentifier OUTPUT

If you built a custom providers for the beta-version of Asp.Net, a code change would be required to work with the released version of the schema.

At first glance, it appeared that Telligent overlooked the change, but as I performed additional research, I found that their released version was designed to provide default backward compatibility with their original (.Net 1.1) product but also an upgrade path for the folks that want compatibility with the Asp.Net 2.0 Membership System. The CommunityServer 2.0 SDK (http://communityserver.org/files/40/releases/entry516270.aspx) includes a readme.txt file that explains what they did and why they did it. In that document, they explain that "Telligent will be providing both Framework 1.1 and 2.0 compatible binaries." You can update the binaries (dll) files and also run a script which will update the Schema, but at the expense of 1.1 compatibility. The readme.txt goes on to explain how to replace the web.config and communityserver.config files and also how to locate the SQL Script file that may be used to upgrade the default schema to that of Asp.Net 2.0.

I am not sure why Beta versions of the Asp.Net schema are involved at all, however, to utilize the final Asp.Net 2.0 database Schema, you need to do the following:

1. Substitute CommunityServer.ASPNet20MemberRole.dll for CommunityServer.MemberRole.dll in the \bin folder

2. Modify CommunityServer.config to have the MemberRoleProfileProvider use classes from the new dll. (swap out the config file)

3. Swap out the provided web.config file for the one in the SDK. (not sure of the differences)

4. Update the database schema using their provided script.

-- The only thing left is to get the ASPNet20MemberRole.dll

Once I do these things, I am hopeful that I will have a database schema that works with the "vanilla" Asp.Net providers. If I can get all this to work, then I should be able to share my membership with that of CommunityServer.

With all respect due to the great folks at Telligent, I would like to suggest that they update the default installation (for new users) to incorporate the released version of the Asp.Net membership schema which should now be considered the gold standard. I believe there are plenty of folks that might wish to unify their membership database with that of CommunityServer and it can be a very easy process if the database schema is consistent. If anyone knows more or has insight on this subject, I invite your suggestions and comments.

Brian Mishler

http://www.qualitydata.com

How to import existing Asp.Net Membership data into CommunityServer

Brian Mishler — Fri, 07 Apr 2006 02:10:19 GMT

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

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.

Brian Mishler

http://www.qualitydata.com