Implementing Password History using a Custom Membership Provider

I recently had to add login functionality to one of our intranet sites, which incorporated quite a few constraints. Certain sections had to be restricted to admin users, and other sections had to be accessible without logging in at all. All passwords had to contain at least one number, upper case letter, lower case letter, symbols, and had a minimum and maximum length. The list went on! In the last week or so, I’ve written over 300 tests around this!

Most of the rules were relatively easy to accomplish using a combination of Forms Authentication, the default SQL Membership provider, and the various login-related controls that come out of the box in Web Forms. The rules that couldn’t be configured in the membership section of the web.config could still be accomplished easily enough using the various hooks built in to the login controls (e.g. adding various additional checks using the ChangePassword.OnChangingPassword event, unapproving users if their last login was too long ago, etc.).

The rule that presented the most difficulty, however, was the one that prevented users from using any of their most recent passwords. Having already implemented most of the other functionality using the System.Web.Security.SqlMembershipProvider, I now had a few options:

  1. Change the aspnet_Membership_SetPassword sproc to first check a list of recent passwords. Return some failure code if a match is found.
  2. Look up the list of recent passwords from the ChangePassword control’s OnChangingPassword event.
  3. Create a custom membership provider to check a list of recent passwords.

I ruled out the first option pretty quickly – I felt it goes against the principle of least surprise – i.e. you should do what you can to minimise confusion and surprise for any developer that needs to look at this later. Most developers, upon seeing that the built-in SqlMembershipProvider is being used, would expect the sprocs to be as generated using the Aspnet_regsql.exe command-line tool. That is the typical setup.

Looking at the second option, I was already using the ChangePassword.OnChangingPassword hook for the more basic password validity checks, and so it might seem like a good option to just add the extra password checks in right here to look up a list of recent passwords. I felt though that database access was the Membership Provider’s job, and not something to be done using a separate data access component from a hook in the ChangePassword control. Not choosing option 2 does mean that the password validation rules are split between the ones implemented in the OnChangingPassword event handler and the recent password rules that would then be implemented elsewhere, but I felt this was the lesser of 2 evils. So I went with option 3.

So, whether you chose option 2 or 3 above, you’ve still got to store the recent passwords. This calls for a new table:

CREATE TABLE dbo.PasswordHistory
(
	PasswordHistoryId INT PRIMARY KEY IDENTITY(1,1),
	UserId UNIQUEIDENTIFIER,
	[Password] NVARCHAR(128) NOT NULL,
	PasswordSalt NVARCHAR(128) NOT NULL,
	CreatedDate DATETIME DEFAULT GETDATE()
)

ALTER TABLE dbo.PasswordHistory ADD CONSTRAINT FK_PasswordHistory_aspnet_Users_UserId FOREIGN KEY (UserId) REFERENCES dbo.aspnet_Users (UserId)

I then added a trigger so that whenever the (hashed) password and password-salt changed, the old values would be added to a new row in the new table. Here’s the trigger I created:

CREATE TRIGGER dbo.trPasswordUpdate
   ON  dbo.aspnet_Membership
   AFTER UPDATE
AS 
	BEGIN
		
		INSERT INTO dbo.PasswordHistory 
		(
			UserId, 
			[Password], 
			PasswordSalt
		) 
		SELECT
			deleted.UserId,
			deleted.[Password], 
			deleted.PasswordSalt
		FROM 
			deleted INNER JOIN inserted ON deleted.UserId = inserted.UserId
		WHERE
			(deleted.[Password] <> inserted.[Password]) OR (deleted.PasswordSalt <> inserted.PasswordSalt)
	END

And then I created 2 sprocs – one to get the list of recent passwords for a particular user, and one to delete non-recent passwords, i.e. allow tidying up so that we only keep the last few. First – here’s the one to retrieve the recent passwords.

CREATE PROCEDURE dbo.spPasswordHistorySelect
	@userName NVARCHAR(256),
	@numberOfRecentPasswordsToRetrieve INT
AS
BEGIN
	
	DECLARE @userId UNIQUEIDENTIFIER
	
	SELECT @userId = NULL
	
	SELECT 
		@userId = UserId
	FROM
		dbo.aspnet_Users
	WHERE
		UserName = @userName
	
	IF(@userId = NULL)
	BEGIN
		DECLARE @errorMessage NVARCHAR(256)
		SELECT @errorMessage = 'User id not found for user ''' + @userName + '''.'
		RAISERROR (@errorMessage, 16, 1)
	END
	
	SELECT TOP(@numberOfRecentPasswordsToRetrieve)
		[Password],
		PasswordSalt
	FROM
		dbo.PasswordHistory
	WHERE
		UserId = @userId
	ORDER BY
		CreatedDate DESC
END

So we pass in the user name and the number of recent passwords to retrieve. On reflection, the number of passwords to retrieve is kind of redundant, as we delete non-recent passwords in the sproc below.

CREATE PROCEDURE dbo.spPasswordHistoryDeleteNonRecentPasswords
	@userName NVARCHAR(256),
	@numberOfRecentPasswordsToKeep INT
AS
BEGIN
	
	DECLARE @userId UNIQUEIDENTIFIER
	
	SELECT @userId = NULL
	
	SELECT 
		@userId = UserId
	FROM
		dbo.aspnet_Users
	WHERE
		UserName = @userName
	
	IF(@userId = NULL)
	BEGIN
		DECLARE @errorMessage NVARCHAR(256)
		SELECT @errorMessage = 'User id not found for user ''' + @userName + '''.'
		RAISERROR (@errorMessage, 16, 1)
	END
	
	DECLARE @minimumDate DATETIME
	
	SELECT 
		@minimumDate = MIN(CreatedDate)
	FROM
		dbo.PasswordHistory
	WHERE
		UserId = @userId
		AND CreatedDate IN
		(
			SELECT 
				TOP(@numberOfRecentPasswordsToKeep) CreatedDate
			FROM 
				dbo.PasswordHistory
			WHERE
				UserId = @userId
			ORDER BY
				CreatedDate DESC
		)
	
	IF(@minimumDate IS NOT NULL)
	BEGIN
		DELETE
		FROM
			dbo.PasswordHistory
		WHERE
			UserId = @userId
			AND CreatedDate < @minimumDate
	END
END

I think both sprocs are reasonably self-explanatory. So let’s move on to the C#.

Minimising the work to be done by the new Custom Membership Provider

I wanted to lean as heavily as I could on the out-of-the-box functionality provided by the SqlMembershipProvider. I only wanted to override that specific functionality that didn’t fit my needs. So I decided to derive a new CustomSqlMembershipProvider from the System.Web.Security.SqlMembershipProvider, and then only override the ChangePassword() method. Therefore most functionality is still really being provided by the System.Web.Security.SqlMembershipProvider.

Also, I did in fact want to call the SqlMembershipProvider.ChangePassword() from my overriding method – my custom provider is only doing some additional checks, so it’s not really replacing the default functionality, as much as extending it. The most obvious way to do this would have been to call base.ChangePassword() from the overriding method at the appropriate time, but doing this doesn’t lend itself to unit-testing. So instead of calling base.ChangePassword(), I instantiated another SqlMembershipProvider, and wrapped it in an adapter so that it could be mocked out for test purposes. Then I could test the interaction between my custom provider and the underlying provider. But more on all this later – we’ve just defined the sprocs above, so let’s see how they’re used in code.

The Data Access Layer

I used Entity Framework to invoke the sprocs, but the specific data access method used is unimportant here, so I’ll just show the interface:

public interface IPasswordHistoryRepository
	{
		IEnumerable<HashedPasswordDetails> GetRecentPasswordDetails(string userName);

		void DeleteNonRecentPasswords(string userName, int numberOfRecentPasswordsToKeep);
	}

public class HashedPasswordDetails
	{
		public string HashedPassword { get; set; }

		public string Salt { get; set; }
	}

Encrypting Passwords

The sprocs above let us see the hashed recent passwords. While the DAL above will retrieve all the recent passwords, we need to convert the password to validate into it’s hashed equivalent so that it can be compared with each of the recent hashed passwords. I figured out the details of the password encrypter by looking at the System.Web.Security.SqlMembershipProvider in Reflector. So I did the same thing here:

public class PasswordEncrypter : IPasswordEncrypter
	{
		public string EncodePassword(string pass, string salt, string hashingAlgorithm)
		{
			var bytes = Encoding.Unicode.GetBytes(pass);
			var src = Convert.FromBase64String(salt);
			var dst = new byte[src.Length + bytes.Length];
			Buffer.BlockCopy(src, 0, dst, 0, src.Length);
			Buffer.BlockCopy(bytes, 0, dst, src.Length, bytes.Length);
			var algorithm = HashAlgorithm.Create(hashingAlgorithm);
			var inArray = algorithm.ComputeHash(dst);
			return Convert.ToBase64String(inArray);
		}
	}

Comparing the new password with the list of recent passwords

Then, using the PasswordHistoryRepository and the PasswordEncrypter, I created a PasswordHistoryService, that would iterate over each password from the history table, combine the proposed new password with the recent-password-salt, encrypt it, and check if that value matched the hashed recent password.

public bool HasNewPasswordBeenUsedRecently(string userName, string proposedNewPassword)
		{
			var hashingAlgorithm = Membership.HashAlgorithmType;
			var hashedPasswordDetails = passwordHistoryRepository.GetRecentPasswordDetails(userName);
			foreach(var passwordDetails in hashedPasswordDetails)
			{
				var encodePassword = passwordEncrypter.EncodePassword(proposedNewPassword, passwordDetails.Salt, hashingAlgorithm);
				if (ProposedNewPasswordMatchesAPreviousPassword(passwordDetails, encodePassword))
				{
					return true;
				}
			}

			return false;
		}

		public void DeleteNonRecentPasswords(string userName)
		{
			var numberOfRecentPasswordsToKeep = configurationRepository.GetAppSetting<int>(NUMBER_OF_OLD_PASSWORDS_TO_CHECK_KEY);
			passwordHistoryRepository.DeleteNonRecentPasswords(userName, numberOfRecentPasswordsToKeep);
		}

		private bool ProposedNewPasswordMatchesAPreviousPassword(HashedPasswordDetails passwordDetails, string encodePassword)
		{
			return encodePassword == passwordDetails.HashedPassword;
		}

You can see that the actual hashing algorithm used is the one found in the Membership section of the config. Also, I’ve shown the DeleteNonRecentPasswords method – I use a wrapper to read the number of recent passwords to keep from the config – this allows me to test different configurations in different tests easily, but you could just use the ConfigurationManager directly. Or you could hard-code it. Only joking – don’t hard-code it. PLEASE don’t hard-code it!

And this (finally!) brings us on to the CustomSqlMembershipProvider!

The Custom Membership Provider (I know – there’s not a lot to it, is there?)

The CustomSqlMembershipProvider derives from the System.Web.Security.SqlMembershipProvider, overriding 2 methods. All other methods are inherited from the SqlMembershipProvider. The obvious method that needs to be overridden is the ChangePassword method:

public class CustomSqlMembershipProvider : SqlMembershipProvider
	{
...
public override bool ChangePassword(string username, string oldPassword, string newPassword)
		{
			passwordHistoryService.DeleteNonRecentPasswords(username);
			if (passwordHistoryService.HasNewPasswordBeenUsedRecently(username, newPassword))
			{
				return false;
			}

			return underlyingProvider.ChangePassword(username, oldPassword, newPassword);
		}
...
}

So what’s this underlyingProvider then?

Making the interaction between the custom membership provider and SqlMembershipProvider testable.

After deleting the non-recent passwords, and then checking if the new password matches any of the existing passwords, we delegate to the underlying provider. The underlying provider is, essentially, a System.Web.Security.SqlMembershipProvider. To be more precise, it’s an adapter class that makes a SqlMembershipProvider implement a custom interface. I do this for testability – by having an interface, I can mock out this underlying provider in tests. The interface is as follows:

public interface IMembershipProvider
	{
		bool ChangePassword(string username, string oldPassword, string newPassword);

		void Initialize(System.Collections.Specialized.NameValueCollection config);
	}

I’ll discuss the Initialize method shortly, but first, let’s look at the implementation of this interface:

public class MembershipProviderAdapter : IMembershipProvider
	{
		private readonly MembershipProvider membershipProvider;

		public MembershipProviderAdapter(MembershipProvider membershipProvider)
		{
			this.membershipProvider = membershipProvider;
		}

		public void Initialize(NameValueCollection config)
		{
			membershipProvider.Initialize(membershipProvider.GetType().ToString(), config);
		}

		public bool ChangePassword(string username, string oldPassword, string newPassword)
		{
			return membershipProvider.ChangePassword(username, oldPassword, newPassword);
		}
	}

This is very straightforward – it just makes a MembershipProvider (as provided by Microsoft) “look like” my custom interface. This then allows me to write tests like:

[Test]
		public void WhenChangingPassword_IfNewPasswordHasNotBeenUsedRecently_DelegateToUnderlyingProvider()
		{
			passwordHistoryService.Stub(phr => phr.HasNewPasswordBeenUsedRecently(null, null)).IgnoreArguments().Return(false);

			sut.ChangePassword(TestConstants.USER_NAME, TestConstants.OLD_PASSWORD, TestConstants.NEW_PASSWORD);

			underlyingProvider.AssertWasCalled(up => up.ChangePassword(TestConstants.USER_NAME, TestConstants.OLD_PASSWORD, TestConstants.NEW_PASSWORD));
		}

Here, I’m using Rhino Mocks and the Arrange-Act-Assert syntax. First I Arrange, i.e. set up a stub of the PasswordHistoryService to always return false – this sets up the particular circumstances of this test, i.e. the IfNewPasswordHasNotBeenUsedRecently bit of the test name. Then I Act, i.e. call the method I’d like to test (sut here stands for system-under-test, i.e. the thing whose behaviour we want to test). And then I Assert that the underlying SqlMemberhipProvider has it’s ChangePassword method called. Hopefully you can see the benefit of this kind of test, and therefore the reason why I couldn’t just delegate to base.ChangePassword().

Initialising the Custom Membership Provider

Next, I think I should clarify why we override the Initialize method. Before I wrote the code below, I was getting the following error:

Object reference not set to an instance of an object.
   at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation)
   at System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate)
   at System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat)
   at System.Web.Security.SqlMembershipProvider.ChangePassword(String username, String oldPassword, String newPassword)
   at...

Now, let’s look at how the CustomMemberhipProvider deals with this. I’ll explain afterwards.

public override void Initialize(string name, NameValueCollection config)
		{
			var clone = GetCloneAsNameValueCollectionIsEmptyAfterInitializingUnderlyingProvider(config);
			EnsureUnderlyingProviderCanGetTheRelevantConnectionString(config);
			base.Initialize(name, clone);
		}

		private NameValueCollection GetCloneAsNameValueCollectionIsEmptyAfterInitializingUnderlyingProvider(NameValueCollection config)
		{
			return new NameValueCollection(config);
		}

		private void EnsureUnderlyingProviderCanGetTheRelevantConnectionString(NameValueCollection config)
		{
			underlyingProvider.Initialize(config);
		}

In the web.config, we indicate that the membership provider to be used is my custom membership provider. Before any method on any membership provider is invoked for the first time, the Initialize method is called so that all the properties from the web.config can be assigned to the provider, before the provider tries to use those properties. But the built-in Membership class doesn’t know that there’s an underlying SqlMembershipProvider being used as well – to keep the web.config simple, I only added configuration for the CustomSqlMemberhipProvider.

<membership defaultProvider="CustomSqlMembershipProvider" hashAlgorithmType="SHA512">
			<providers>
				<clear />
				<add name="CustomSqlMembershipProvider" 
					  type="[namespace of custom membership provider].CustomSqlMembershipProvider, [your assembly name], Version=[version number], [culture], [public key token]" 
					  connectionStringName="[connection key]" 
					  enablePasswordRetrieval="false" 
					  enablePasswordReset="true" 
					  requiresQuestionAndAnswer="false" 
					  applicationName="/" 
					  requiresUniqueEmail="false" 
					  passwordFormat="Hashed" 
					  maxInvalidPasswordAttempts="5" 
					  minRequiredNonalphanumericCharacters="1" 
					  passwordAttemptWindow="10"
				/>
			</providers>
		</membership>

And so it’s this CustomSqlMembershipProvider that gets passed all the properties from the web.config. In order for the underlying SqlMembershipProvider to function correctly though, it needs access to that same config – otherwise it won’t even know what database to try to connect to! So we override the Initialize method in the CustomMembershipProvider to ensure that we can copy those values and share them between the custom membership provider and the underlying membership provider. The SqlMembershipProvider deletes values from the name value collection when it’s finished though, so they can’t be reused by the CustomMembershipProvider – hence the cloning – this is essentially a workaround to allow the same name value collection to be used by both CustomMembershipProvider and the underlying SqlMemberhipProvider.

What’s left to do?

The above implementation does have an issue with deleting a user that has changed their password – we get a foreign key constraint violation as per the foreign key created above. It’s a small fix that I’m planning on doing tomorrow – it should be as simple as adding an on-delete-cascade clause to the foreign key constraint.

I hope you’ve found this post to be of use. As always, any thoughts / comments / suggestions would be very welcome.

Advertisements

8 responses to “Implementing Password History using a Custom Membership Provider

  1. Sebastien ORGEAS

    Great post Ron, I’m just facing the same requirement of password history check and I’m surprised that this feature is not directly supported by the SqlMembershipProvider! Anyway, your solution is very interesting and I was wondering if you had any feedback to provide me, specifically about the issue you mention at the end of your post? Thank you in advance

    • Hi Sebastien,

      Glad to hear you liked the post!

      I too was a bit surprised that this feature isn’t already supported by SqlMembershipProvider – it seems like an obvious enough thing to want to do.

      I assume you mean the issue about deleting users causing a foreign key constraint? I solved it by adding an “on delete cascade” clause as follows:


      ALTER TABLE [dbo].[PasswordHistory] WITH CHECK ADD CONSTRAINT [FK_PasswordHistory_aspnet_Users_UserId] FOREIGN KEY([UserId])
      REFERENCES [dbo].[aspnet_Users] ([UserId])
      ON DELETE CASCADE

      Hope this helps!

      Ronan

  2. Dear Ronan,

    I had a hard time trying to find some information regarding this requirement.

    It’s an important feature that should be part of the Membership provider IMHO.

    Your extensive post helped me a lot! 😀 Thank you very much!

    I used option 2…

    Keep sharing your findings. That’s the secret to learn even more…

    All the best,

    Leniel

  3. Ronan,

    Something I forgot to mention in my previous comment:

    The code you pasted in the post is missing some bits (characters). When I copied it using the SynthaxHighlighter I noted that: the trigger code is missing != in the where clause. The DeleteNonRecentPasswords SP has a < when it should be <.

    Hope it helps,

    Leniel

    • Thanks for your comments, Leniel! I’ve corrected those issues – I think it might be an issue with the WordPress editor with angle brackets (I tend to use <> instead of !=) – I’m still fairly new to WordPress, so I’ll keep an eye on it for next time. It takes a long time to create posts like this, so I don’t do it frequently, but I might write more getting such encouraging feedback! Much appreciated!

      • Ronan,

        I knew there was something wrong with the password history since it was allowing me to change the password to the same one (current one) I’m already using.

        Only today I took the time to analyze what was going on: you have to change the SELECT on the trigger code. You must use the inserted value and not the deleted value.

        SELECT
        inserted.UserId,
        inserted.[Password],
        inserted.PasswordSalt

        Hope it helps,

        Leniel

  4. Great post Ron, I am able to implement the Password history except that we have configured “passwordformat” as “encrypt” instead of “hashed”. Its works for “hashed” passwordformat but not for “encrypt”. If you could help with password format with “encrypt” it will be great help.

    • Hi Vamsee,

      Glad you liked the post but I wrote that post a long time ago, so I’m afraid I can’t really help. If you’re asking someone else about it going forward, you should probably include a bit more detail about the way it failed when using the encrypt option – that would probably help someone help you more. Sorry I can’t be of more help to you.

      Ronan (not Ron! 🙂 )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s