Brian Mishler on .Net - Using existing Asp.Net Membership data with CommunityServer

Sunday, April 09, 2006

Using existing Asp.Net Membership data with CommunityServer

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

Sunday, April 09, 2006 10:12:06 PM (Eastern Daylight Time, UTC-04:00)