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.
