Click on the banner to learn about and purchase my database training on Azure

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

Views: 9.206 views
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 best known algorithms that use this technique is the Base64
  • The length of the converted string is different from the size of the original string.
  • A password when 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

A method that adds another degree of security to the previous method by applying custom scrambles on top of known conversion algorithms, such as creating a looping that iterates N times by applying the Base64 conversion multiple times and applying a function like REVERSE to 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 when 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:

  • Best 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 NO can be retrieved
  • 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 the hash generated “0x01000000940908D92A0826928234D7DD6E3FA27AF5D818FC7654AE39AC090D30DFE5C43C ”I will try to recover the original message on a SQL Server 2017:

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

Wow, what a big hash .. I will use the hash “0x02000000F955D4A111500CD5F9287228B3FC927FB7640202F2EC85F6BBA2074CD2434F39FE98CFFAD46718119ABC20AFAE058E2B” para recuperar a senha original do SQL Server 2008:

Geez.. Returned NULL! And now?? Well, it doesn't have backwards compatibility. And SQL Server 2008 is no longer supported by Microsoft and probably won't have an update to include it. Do you have 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 HPA (host protection attribute) “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!