One Way Password Encryption in T-SQL

Best practices for storing passwords to a database call for the process to be one way encrypted, meaning that, once encrypted, there should be no way to unencrypted it. So easy, write an encryption function, store the encrypted password, then when next someone logs in encrypt the provided password and check that encryptions against the stored encrypted password.

The issue here, however, is that someone (read malicious hacker/state entity) might grab the entire database then start comparing encryptions that are known. Or worse, change the unknown encryptions to a known encrypted password, thus giving the bad actor access to whatever account they target.

There is a simple T-SQL alternative called EncryptByPassPhrase (see ENCRYPTBYPASSPHRASE | Microsoft Docs for details). It uses an encryption key provided by the programmer (called Pass Phrase) to aid in the encryption, and even if the same key is used for the same password, the result will be different across records, preventing the ability to replace passwords with known encryptions.

To unencrypt the you would use the companion T-SQL function DecryptByPassPhrase (see DECRYPTBYPASSPHRASE | Microsoft Docs for details). But wait, didn’t this article talk about one-way encryption, so doesn’t use of the decryption function contradict that? No, not if you use the password as part of the encryption key itself (referred to as an authenticator key), which means, without the correct password you cannot unencrypt the encrypted password.

This article presents three simple functions to do this:

1. admn.fn_GetPassPhrase (for security reasons, this is discussed below but an actual implementation is not provided).
2. admn.fn_EncryptPassword (function to encrypt the password and return a var binary value of that encryption).
3. admn.fn_DoesPasswordMatch (function which indicates whether the password passed in matches the encrypted password).

All three use a schema (admn) that is assigned to the database owner with execution access limited to logged-in users granted the appropriate rights or called within stored procedures created by a user assigned those same rights. The use of restrictive permissions is employed to limit access, thus reducing the possibility of misusing the functions.

The actual encryption function that returns the encrypted value is a short one:

The function takes two parameters, the customer ID and the password to encrypt. The customer ID is passed into a function (admn.fn_GetPassPhrase) that returns one of several possible pass phrases, with the modulus of the ID determining which one is used. The actual implementation of Get Pass Phrase function can vary with the security needs of the application, from a single short simple sentence (not recommended) to dozens of possible values of varying complexity.

The returned pass phrase is the first parameter of the EncryptByPassPhrase function, with the password provided being the second. The third parameter of the built-in T-SQL encryption function is set to one (1), which is an indicator that an authenticator key is being used as the fourth parameter (in this case is the password itself). Using the password as the authenticator key means that without the original password, the returned value cannot be decrypted.

Assuming there is a table called sales.Customers with a CustomerID field, an EMail field (used also as the login) and a ThePassword field, the UPDATE command for customer 1000 would be:

Unless the user has the same privileges assigned as the admn schema, this cannot be called directly, rather, must be within a stored procedure belonging to a different schema but created by a user with the admn schema privilege.

When the custom goes to log in next, they will be prompted for their email address and password as part of a login screen. A stored procedure would be used to retrieve the Customer ID and the encrypted password based on the login name (such as a unique email address). The “Does Password Match” function would then be used, again, within a stored procedure with a different schema accessible to the user, to determine if the correct password was applied. There is that function:

The passed in Customer ID is used in the “Get Pass Phrase” function to return the appropriate encryption key (Pass Phrase). The passed in encrypted password is used as the second parameter of the “Decrypt By Pass Phrase” T-SQL function, along with the unencrypted password as the fourth parameter. The third parameter is just one (1), again to indicate that an authenticator key is being used. The returned string by the “Decrypt By Pass Phrase” T-SQL function is then compared to the password being checked using a binary collation directive to take into consideration upper and lower case and special characters.

BUT

As mentioned, the functions provided are intended to be used within stored procedures, not directly themselves. The “Does Password Match” function needs to be within a stored procedure where there is a lookup for the customer ID and the stored encrypted password from a login page to work. Obscuring how the actual decryption works is important to add a layer of isolation to the database. As with all security related functions and stored procedures, the WITH ENCRYPTION is an important item to add to declaration of said same programmable to prevent prying eyes.

Great care must be given to how the security and permissions on the functions and stored procedures are worked out. There are other encryption methods which could be used, but again, simplicity with thought towards security must be the guiding principle.

It would be nice if people used different passwords for different website and applications but that is not the case. With an ever-increasing litigious population, it is the organization owning the database that is responsible to take maximum effort to protect digital data with strong encryption.

If you have questions about encrypting data in a database, please use our contact page to submit them.