How do we implement encryption and hashing in SQL server?

Part2

In the part1 we discussed the basic difference between encryption and hashing. So now let’s look into how we can implement hashing and encryption using the SQL server functions.

We have a function called the “HashBytes” function which performs the hashing.

The syntax for it would be:

HashBytes ( '<algorithm>', { @input | 'input' } )

There are various hash algorithms that you can provide as an input to the following function. They are MD2, MD4, MD5, SHA and SHA1. The @input or ‘input’ would be the plain text that you would like to be converted to cipher text.

The output returned by this function is in the form of “Varbinary”. So if we are trying to assign the value of this function to a variable, it should be of the type “Varbinary”.

Let consider the following sample code:

DECLARE @HashText nvarchar, @cipher varbinary(MAX);

SELECT @HashThis = CONVERT(nvarchar,'dslfdkjLK85kldhnv$n000#knf');

Set @cipher=HashBytes('MD5', @HashThis);

Select @cipher;

GO

Here we have declared 2 variables i.e. HashText and cipher. The HashText would hold the plain text and the cipher variable would hold the cipher text that was returned by the “HashByte” function.

Before we could get into basic functions that SQL server provides to encrypt the data, I would like to give a brief introduction to the public key (asymmetric key) and symmetric key encryption. We will be using the terms interchangeably.

Let’s first talk about the public key encryption. In public key encryption there are two keys involved. The first key is the public key and the second key is the private key. The user who is encrypting the data uses the public key to encrypt the data and the corresponding private key has to be used to decrypt the data. Let’s consider the following example.

Let‘s consider that X has the public key and he want to encrypt the data and send it to Y. Now Y can only decrypt the data if he has the corresponding private key.

In the above example only y can decrypt the data. But the hacker Z cannot open the data. Now it has to be ensured by Y that his private key is not compromised.

When we talk about the symmetric key encryption or symmetric key cryptography, it works as follows. There is one single key and it’s called the symmetric key. The data is encrypted with a key and the same key is used to decrypt the data. Let’s consider the following example:

Let‘s consider that X want to encrypt the data and send it to Y. Here X and Y both need to have the same key to decrypt the data.

In the above example Y can decrypt the data if he has the symmetric key. But if the symmetric key is compromised, even Z would be able to decrypt the data.

After having a basic understanding of the two encryption techniques now let’s discuss the basic functions that SQL Server provides for encryption. We can encrypt a piece of text by using the following four functions:

1. EncryptByPassPhrase

2. EncryptByCert

3. EncryptByKey

4. EncryptByAsymKey

EncryptByPassPhrase is the most simplest and the easiest to use. It encrypts the text using a pass phrase provided by the user. The syntax for this function would be the following:

EncryptByPassPhrase( { 'passphrase' | @passphrase } ,{ 'cleartext' | @cleartext } [ , { add_authenticator | @add_authenticator } , { authenticator | @authenticator } ] )

That is pretty big. But it is even simpler to use. So let’s take a look at how could we use it. Here the ‘passphrase’ is the pass phrase provided by the user, using which the cipher text is generated. ‘cleartext’ is the plain text that has to converted to the cipher text. If we set ‘add_authenticator” to 1, the authenticator gets encrypted along with the plain text.

‘authenticator’ contains the data using which a authenticator is derived. Let consider an example here

This example is from SQL Server books online:

USE AdventureWorks;

GO

-- Create a column in which to store the encrypted data

ALTER TABLE Sales.CreditCard

ADD CardNumber_EncryptedbyPassphrase varbinary(256);

GO

-- First get the passphrase from the user.

DECLARE @PassphraseEnteredByUser nvarchar(128);

SET @PassphraseEnteredByUser = 'A little learning is a dangerous thing!';

-- Update the record for the user's credit card.

-- In this case, the record is number 3681.

UPDATE Sales.Creditcard SET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(@PassphraseEnteredByUser

, CardNumber, 1, CONVERT( varbinary, CreditCardID))

WHERE CreditCardID = '3681';

GO

In the above example we are encrypting the credit card number.

The second function would be EncryptByCert. This function is used to encrypt the plain text using a certificate that already exists. Let’s have a look at the syntax for this function:

EncryptByCert ( certificate_ID , { 'cleartext' | @cleartext } )

Certificate_ID is the ID of the certificate in the database. If the name of the certificate is known, the Certificate_ID can be found using the function “Cert_ID('Certificate_Name')”.

‘cleartext’ is the text which we want to encrypt.

This function encrypts the ‘cleartext’ using the public key of the certificate and the corresponding private key would be needed to decrypt it. Let’s consider the following example:

Declare @ciphertext varbinary(MAX);

Set @ciphertext= EncryptByCert(Cert_ID(‘cert1’), ‘My Name is John’);

Select @cipher

In the above example the cert1 is the name of the certificate and ‘My Name is John’ is the plain text that we would like to encrypt. So if we want to run this script we will have to create a certificate with the name cert1.

The next function about which we will be talking is the EncryptByKey function. This function allows a user to encrypt the data using a symmetric key. We use the same key to decrypt the cipher text. Let’s have a look at the syntax of this function:

EncryptByKey(key_GUID , { 'cleartext' | @cleartext }[, { add_authenticator | add_authenticator } , { authenticator | @authenticator } ] )

If we look at the parameters from this particular function, they are very similar to EncryptByPassPhrase function. Only the first parameter is different. The first parameter is the GUID of the symmetric key. If we know the name of the key the GUID can be found using the function Key_GUID(‘Key_Name’). Other than the first parameter the other parameters are the same as the EncryptByPassPhrase function. Let’s look at an example.

From Books online

USE AdventureWorks;

-- Create a column in which to store the encrypted data

ALTER TABLE Sales.CreditCard. ADD CardNumber_Encrypted varbinary(128);

GO

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09;

-- Encrypt the value in column CardNumber with symmetric

-- key CreditCards_Key11.

-- Save the result in column CardNumber_Encrypted.

UPDATE Sales.CreditCard

SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),

CardNumber, 1, CONVERT( varbinary, CreditCardID) );

GO

One thing that is different here is that we need to open the Symmetric key here before we can use it. If the symmetric key is already open in the current session, there is no need to open the key and we can directly use the function to encrypt the data.

Now let see how can EncryptByAsymKey function be used. This function uses the asymmetric key to encrypt the data. The operation involving the asymmetric key encryption and decryption are usually costly and is not recommended when dealing with large datasets. The syntax of the function would be

EncryptByAsymKey (Asym_Key_ID , { 'cleartext' | @cleartext } )

Here the Asym_Key_ID is the ID of the asymmetric key. If the name is know we can find out the ID using the function AsymKey_ID(‘Key_Name’) and the ‘cleartext’ would be the text that we need to encrypt.

Part 1 - Can’t understand the difference between hashing and encryption?

Gurpreet Singh
SE, Microsoft Sql Server.

Reviewed by
Gurwinderjit Singh
Technical Lead, Microsoft Sql Server.