How To Manually Change ASP.NET MembershipProvider Passwords

How To Manually Change ASP.NET MembershipProvider Passwords

Don’t ask me why you find yourself working in ASP.NET. I know there are more effective ways to build a site.

Don’t ask me why you’re maintaining an app written in the style of 2005. I know, but it happens occasionally frequently.

Don’t ask me why your ASP.NET app is using the MembershipProvider system. I know it’s a poor match for the needs of almost all apps and encourages security holes by design.

Don’t ask me what reason could possibly explain needing to change some passwords. Why isn’t this functionality built in to the app? I know, I know…

But you’re there. Your app is using the MembershipProvider system, which saves the passwords in the database in some kind of encrypted form. And now you have to change some passwords quickly, probably for multiple embarrassing reasons, yet the app doesn’t offer you the functionality to do so, and you don’t have the time to add that functionality and re-build and re-deploy the app.

If only it were possible to go into SSMS and change the passwords using only T-SQL.

Now you can.

First you need these two helper functions. Well, you don’t really need them, but they’ll help make the rest of your T-SQL look like MySQL more civilized.

-----------------------------------------------------------------------------
IF OBJECT_ID('BinFromBase64') IS NOT NULL DROP FUNCTION BinFromBase64;
GO
-- This function is just here to encapsulate the ugly trick for converting from Base64.
CREATE FUNCTION BinFromBase64
(
   @base64 VARCHAR(MAX)
)
RETURNS VARBINARY(MAX)
BEGIN
   RETURN CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@base64"))', 'VARBINARY(MAX)')
   /* alternate:
   RETURN CAST(@base64 AS XML).value('.', 'VARBINARY(MAX)')
   */
END;
GO
-----------------------------------------------------------------------------
IF OBJECT_ID('Base64FromBin') IS NOT NULL DROP FUNCTION Base64FromBin;
GO
-- This function is just here to encapsulate the ugly trick for converting to Base64.
CREATE FUNCTION Base64FromBin
(
   @bin VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
   RETURN CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@bin"))', 'VARCHAR(MAX)');
END;
GO

You’ll notice I’m using the Spolsky™ Naming Convention.

Anyway, the following sproc is the meat.

-----------------------------------------------------------------------------
IF OBJECT_ID('aspnet_SetPassword') IS NOT NULL DROP PROCEDURE aspnet_SetPassword;
GO
CREATE PROCEDURE aspnet_SetPassword
   @username NVARCHAR(256),
   @newPassword NVARCHAR(128),
   @test BIT = 0
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @userNameFound NVARCHAR(256);
   DECLARE @oldPassword NVARCHAR(128);
   DECLARE @saltBytes VARBINARY(MAX);
   SELECT @userNameFound = UserName, @oldPassword = Password, @saltBytes = dbo.BinFromBase64(PasswordSalt)
      FROM aspnet_Users AS u JOIN aspnet_Membership AS m ON m.UserId = u.UserId
      WHERE UserName = @username
   IF @userNameFound IS NULL
   BEGIN
      RAISERROR('Username not found: %s.', 18, 1, @username);
      --RETURN;
   END;
   IF @saltBytes IS NULL OR DATALENGTH(@saltBytes) != 16
   BEGIN
      RAISERROR('Unexpected salt for: %s.', 18, 1, @username);
      --RETURN;
   END;
   SET @newPassword = dbo.Base64FromBin(HASHBYTES('SHA1', @saltBytes + CAST(@newPassword AS VARBINARY(256))));
   IF @test != 0
   BEGIN
      PRINT 'Preview: ' + @username + '  ' + @newPassword + ' (' + (CASE WHEN @newPassword = @oldPassword THEN 'matches' ELSE 'previously ' + @oldPassword END) + ')';
      SELECT @username AS username, @newPassword AS newPassword, @oldPassword AS oldPassword;
   END
   ELSE
   BEGIN
      PRINT 'Setting: ' + @username + '  ' + @newPassword + ' (' + (CASE WHEN @newPassword = @oldPassword THEN 'matches' ELSE 'previously ' + @oldPassword END) + ')';
      UPDATE aspnet_Membership
         SET
            Password = @newPassword,
            PasswordFormat = 1,
            LastPasswordChangedDate = SYSUTCDATETIME()
         FROM aspnet_Users AS u JOIN aspnet_Membership AS m ON m.UserId = u.UserId
         WHERE u.UserName = @username;
   END;
END;
GO

Then you call it like this.

EXEC aspnet_SetPassword 'Somebody', 'SomeSecret', @test = 1;

That “test” parameter is important. It makes the sproc just show you what it was going to do without doing it. You first call the sproc with “test” true and credentials that you know to be already saved in the database and working. You check to make sure the output of the sproc matches the hash previously stored in aspnet_Membership.Password. This is important because Microsoft lets you choose from a few encryption options when you set up your web app, and you want to make sure this sproc is doing the same thing as the Framework. In particular, this sproc only works when MembershipProvider.PasswordFormat is set to “Hashed” (not “Encrypted”), though “Hashed” ought to be what you’re using. I hope.

Also, in spite of what Microsoft & followers say, the MachineKey is irrelevant to the hashing formula.

Naturally, you can build on this to do the same thing in a loop to affect many users, or whatever you need. Here’s one example of that.

-----------------------------------------------------------------------------
IF OBJECT_ID('aspnet_SetAllPasswords') IS NOT NULL DROP PROCEDURE aspnet_SetAllPasswords;
GO
CREATE PROCEDURE aspnet_SetAllPasswords
   @newPassword NVARCHAR(128),
   @test BIT = 0
AS
BEGIN
   SET NOCOUNT ON;
   DECLARE c1 CURSOR FOR SELECT UserName FROM aspnet_Users ORDER BY UserName;
   DECLARE @username NVARCHAR(256);
   DECLARE @users INT = 0;
   OPEN c1;
   WHILE 1=1
   BEGIN
      FETCH NEXT FROM c1 INTO @username;
      IF @@FETCH_STATUS != 0 BREAK;
      SET @users = @users + 1;
      EXEC aspnet_SetPassword @username, @newPassword, @test;
   END;
   CLOSE c1;
   DEALLOCATE c1;
   PRINT 'Found ' + CAST(@users AS VARCHAR) + ' users.';
END;
GO

Again, I’m sorry if you must ask why someone should ever need this. Let’s just say you’re lucky if you don’t know.

Good luck.

* * *

This post was brought to you by ILSpy. Use it to take apart the crappy DLL somebody gave you and replace it with your own code. Even if that DLL came from Microsoft.

Header image via Terrapin Flyer

Bradley Macomber

Bradley Macomber

Bradley is one of those guys who's been programming computers since childhood. Now he's a Senior Software Engineer at Art & Logic. Bradley gets a little too excited about OpenGL, C++, Python, Berkeley sockets, and mechanics/physics simulation.
Bradley Macomber

Latest posts by Bradley Macomber (see all)

Tags:

Creative Commons License

This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.