SQL Server - Encrypting passwords with the symmetric encryption functions ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE

Views: 1.567
Reading Time: 8 minutes

Hey guys!!
In this article, I would like to share with you a very interesting solution to protect and encrypt passwords in SQL Server that have the possibility of recovering the original password (as long as you know the salt used), which are the functions ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE, available since SQL Server 2008.

Do you like to study about password and encryption security? See other articles on this subject:

A Password Security Summary

When it comes to password security, we need to keep in mind that EVERY algorithm that allows retrieving the original string is not the safest option possible, as these methods are often easier to crack than using HASH-based algorithms. To improve your understanding, I will create a summary of the 5 basic types of password security we can find, in order of least secure to most secure.

1) None

The most mode unsafe possible, where the password is stored without any encryption and anyone who has access to the bank / file can view the original password.

2) Conversion

Method that adds a degree of security to the password when converting the original characters to other characters according to the algorithm used and this technique has the following characteristics:

  • One of the most used algorithms using this technique is the Base64
  • The length of the converted string is different from the size of the original string.
  • A password after converted will always return the same result.
  • Algorithm conversion patterns are easy to identify visually, which makes it easy to identify which algorithm is used


3) Custom Conversion

Method that adds another degree of security to the previous method by applying custom shuffles over known conversion algorithms, such as looping N times using Base64 conversion multiple times and applying a function like REVERSE in each iteration.

This technique is not the most secure in the world, but it certainly makes it difficult for someone trying to crack the security of your password.

Some features of this method:

  • Uses conversion algorithms as well as string functions
  • The length of the converted string is different from the size of the original string and the original conversion algorithm.
  • A password after converted will always return the same result.
  • Algorithm conversion patterns are not easy to identify visually (and can confuse who is trying to break the code)


4) Symmetric encryption algorithms

Symmetric key algorithms are encryption algorithms that use the same cryptographic key for plaintext encryption and ciphertext decryption. The key, in practice, represents a secret shared between two or more parties that can be used to maintain a private information link. This requirement that both parties have access to the same secret key is one of the main disadvantages of symmetric key encryption compared to public key encryption (also known as asymmetric key encryption), as they use two keys (public and private). ).

Some features of this method:

  • Popular Algorithms: AES, Twofish, Serpent, Blowfish, CAST5, RC4, 3DES (based on DES), IDEA
  • The length of the encrypted string is different from the size of the original string (but it is always the same length according to the algorithm used).
  • The password can only be recovered if you know the private key (Salt)
  • A password when encrypted will return different results with each execution.
  • Difficult to identify which algorithm using hashing only


5) HASH Algorithms

A password encrypted with some hash function is virtually impossible to retrieve the original value and so we can say that they are one way. In this type of algorithm, it is not possible to recover the original password and the validation of its identity is made by comparisons between the encrypted hash and a new encrypted hash, generated in real time, from the password being tested.

Although extremely safe, there is a remote possibility of 2 different passwords producing the same hash (called collision). The greater the complexity and security of the algorithm used, the less likely this situation is to occur and also the longer it takes to encrypt the message and the size of the hash generated. Remember that this method is also subject to brute force attacks (as all previous ones).

Some features of this method:

  • Most known algorithms: MD4, MD5, SHA-1, SHA-2 (SHA-224, SHA-256, SHA-384, SHA-512, SHA-512/224, SHA-512/256), SHA-3 (Keccak ), HMAC
  • The length of the encrypted string is different from the size of the original string (but it is always the same length according to the algorithm used).
  • The password CAN'T be recovered
  • A password when encrypted will always return the same hash
  • Difficult to identify which algorithm using hashing only



Now that I've briefly summarized password security, let's talk about how to apply it to SQL Server:

So I was just missing demonstrating Method 4 (Symmetric Encryption Algorithms) here on the blog. And that is why we are analyzing the functions ENCRYPTBYPASSPHRASE e DECRYPTBYPASSPHRASE, which allow you to encrypt and decrypt the data using a private key (salt) and the TRIPLE DES algorithm (3DES).

In addition to these functions, you can also use the ENCRYPTBYASYMKEY, ENCRYPTBYCERT e ENCRYPTBYKEY, but these functions are based on certificates and symmetric keys that need to be installed on the server for their use.

The use of these functions is very simple and practical:


Just pay attention to NVARCHAR. Key, Message, and return format must be the same type: Either everything is VARCHAR or NVARCHAR. You cannot mix, or you will not be able to use these functions:

SQL 2017+ and Backward Compatibility

Available since SQL Server 2008, the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions are great simple, easy-to-implement solutions for improving the security of password and sensitive information storage, and they internally use the SHA1 algorithm to generate password hash and 3DES-128. for encryption, which is already good protection. From 2017 onwards, Microsoft changed the code for these functions for added security, and now they use SHA256 for password hashing and AES-256 for encryption.

Although it is a good thing from a password security point of view, it has created a problem for those who have older versions of SQL Server and interact with the newer versions, which I will now demonstrate.

In SQL 2008, I will generate a hash for the password “Dirceu Resende”:

With hash generated â € œ0x01000000940908D92A0826928234D7DD6E3FA27AF5D818FC7654AE39AC090D30DFE5C43Câ € I will try to recover the original message in SQL Server 2017:

Well, all right! Wonderful! Apparently, the function of SQL Server 2017 gets to “read” the new and old format. Now let's do the reverse: I will generate a hash in SQL Server 2017:

Wow, what a great hash .. I'll use the hash â € œ0x02000000F955D4A111500CD5F9287228B3FC927FB7640202F2EC85F6BBA2074CD2434F39FE98CFFAD46718119ABC20AFAE058E2B” para recuperar a senha original do SQL Server 2008:

Oh god.. Returned NULL! What i'm going to do?? Well, it has no backward compatibility. And SQL Server 2008 is no longer supported by Microsoft and probably will not have an update to include it. Is there a solution ??

But of course YES! And the responsible for that is our powerful SQLCLR! Using a function written in C #, we can provide a new function in SQL Server 2008 that can recover the password from both new and old versions:

The hash generated in SQL Server 2008 itself is also compatible with this function:


Want to create this SQLCLR in your environment? Notice that you will have to create the assembly as UNSAFE (Unrestricted)due to the use of the library System.Security.Cryptography.Aesbecause the host protection attribute (HPA) â € œMayLeakOnAbort" not allowed in Safe or External Access mode.

Given the warning, let's go to assembly and function creation fncDecryptByPassphrase:

Function source code if you want to compile and deploy the project yourself:

Well guys, I hope you enjoyed this very technical and complicated post .. lol
A big hug and see you in the next article!