How to Re-Create a Login with only a Hashed Password

Introduction

Did you ever have to recreate a SQL login on a different server? To script an existing login, you can just right-click on the login and select Script Login as > CREATE TO > New Query Editor Window, as shown below.

However, for SQL Logins the output of that action is rarely usable, as SQL Server replaces the password with a random one. Your output might for example look like this:

To keep the password the same, you need to manually replace the random password with the existing one. But, to be able to do that, you need to know the password. In many situations that is not convenient and depending on your security setup, you might not even have access to the password.

However, there is a way to script the login together with its password.

Scripting a SQL Login's Password

You can script the password? What happened to all the security best practices of never storing a password in a decryptable manner?

Well, you cannot. However, what you can do is pretty close.

SQL Server does follow best practices and therefore only stores a salted hash of the password. In sys.sql_logins - Getting to know your SQL Logins and LOGINPROPERTY – Getting to know your SQL Logins even more I showed you two ways to retrieve the hashed password for a SQL Login.

That hash value is all that SQL Server needs to be able to check the password during authentication. Therefore, to copy a login to a new SQL Server instance we do not need to know the original password. All we really need is the hashed password value, and a way to tell SQL Server to create the new login with that password hash.

CREATE LOGIN WITH PASSWORD = HASHED

Let us look at an example. First, we need a login:

With that server principal in place we can use for example the LOGINPROPERTY function to get to the stored password hash:

When you execute that statement, you will get a result that looks like this:

With that value we can now compile a CREATE LOGIN statement that will reuse the same hash value. To do that, instead of specifying the password as a quoted string, we just have to provide the hash value followed by the keyword HASHED:

If you now execute this statement on another server, you will be able to login using that SQL Login and the same password.

SQL Server Versions

SQL Server has changed the password algorithm several times throughout the different versions. You can read a little bit about the history of password hashes before SQL Server 2005 in Laurentiu Cristofor's blog @microsoft.com. SQL Server 2005, 2008 and 2008 R2 use SHA-1 while SQL Server 2012 introduces SHA-512 as password hashing algorithm.

The password hashes are backwards compatible, but they are not forward compatible. That means that a password that was hashed on a pre-2012 instance can be used in a CREATE USER statement on a 2012 or later instance. However, a password that was hashed in 2012 or 2014 cannot be used on a pre-2012 instance.

Summary

While there is no way to decrypt a password of a SQL login, you can script out the password hash and use it to compile a CREATE LOGIN statement that contains the password in hashed form. With such a statement, you can recreate a login on a different SQL Server instance while preserving the password.