A couple of months ago I talked about moving a login from one server to another without the password. The basis behind this is creating the login using the hashed version of the password. Using the same idea we can also “back up” a password.

The first thing to do is to get the hashed password. In this case into a backup table. This way I could easily back up multiple passwords in one place.

SELECT name, LOGINPROPERTY(name, 'PasswordHash') HashedPassword
INTO HashedBackup
FROM sys.server_principals
WHERE name = 'BackupPasswordTest'

Now that the password is backed up we change the password.

ALTER LOGIN BackupPasswordTest WITH PASSWORD = 'N3wP@ssw0rd'

Since I backed up the password to a table I’m going to dynamically generate the script to change it back. I could just as easily have backed up the hashed password in a text file and then manually generated my “restore” script. Of course I could also just as easily copy the hashed value out of the table.

While writing my test script I noticed that the CHECK_POLICY property has to be turned off when changing the password with a hashed password. To make my life easier I’m turning it off in my code just in case. After the “restore” you can always turn CHECK_POLICY back on easily enough.

SELECT 'ALTER LOGIN '+name+
' WITH CHECK_POLICY = OFF, PASSWORD = '+
CONVERT(varchar(max), HashedPassword,1)+' HASHED'
FROM HashedBackup
WHERE name = 'BackupPasswordTest'

And last but not least here is an example of the script to change the password back.

ALTER LOGIN BackupPasswordTest WITH CHECK_POLICY = OFF, 
PASSWORD = 0x0100FDBEC2638674B6DFF60E0B3530A94077A0682D7DAED52E6A HASHED 

Just in case someone needs it here is the script to change the CHECK_POLICY property back on.

ALTER LOGIN BackupPasswordTest WITH CHECK_POLICY = ON

Notice that at no point did the DBA need to know the old password.

You might be asking “Why would I want to back up a password?” Well I have to admit it isn’t something I have had to do often. But it does happen every now and again.

Just last week I had a high priority application go down. The application was getting a login failure. Turns out the password they were using wasn’t correct. Of course no one had changed anything, either on the application side or the database side. Got to love it when that happens don’t you? So the quick fix was to change the password to the one they were using. Personally I really don’t like that type of solution. If the password on the database side hadn’t changed then I might be breaking another process while I’m fixing this one. So the first thing I did was to back up the password, then I changed it. About an hour later new evidence came to light. Someone on the application team had changed something on their end, causing them to use an old set of passwords. I restored the password from my backup, they fixed their code, and we were off and running again.

Like I said, not something I do often, but it’s always nice to have a back out plan!

Filed under: Dynamic SQL, Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: code language, dynamic sql, language sql, microsoft sql server, security, sql statements, T-SQL