SQL server Encryption – Transact SQL Functions.

In the previous blogs we came across various encrypt functions like ENCRYPTBYKEY(),ENCRYPTBYASYMKEY(),ENCRYPTBYCERT().

We also saw the corresponding decrypt functions like DECRYPTBYKEY(),DECRYPTBYCERT(),DECRYPTBYASYMKEY(),DECRYPTBYCERT(),

We shall be covering a few more in this blog 

1)  ENCRYPTBYPASSPHRASE() 

This function accepts the data of nvarchar type and after encryption the data is returned in the varbinary format

Syntax : https://msdn.microsoft.com/en-us/library/ms190357.aspx

You could look up the syntax and arguments on the Microsoft website. @passphrase & @cleartext are the mandate arguments. The passphrase is an important argument here. Until now all the functions we have seen require an alphanumeric password for either the keys or the certificates that we created. In scenarios where we do not wish to implement the windows policy of password rather we go for a phrase that will be used for encryption this function comes in handy. After all we are good with mnemonics like “My very educated mother just showed us nine planets “ .... rather than recalling planets like this

Planet 1 = Mercury

Planet 2 = Venus

and so on so forth.

2)  DECRYPTBYPASSPHRASE()

This fucntions access the varbinary encrypted data that was derived from ENCRYPTBYPASSPHRASE() function. And when the same phrase is passed the data is decrypted in the nvarchar form.

Syntax : https://msdn.microsoft.com/en-us/library/ms188910.aspx

Not much of rocket science required here to use this function. It’s similar to DECRYPTBYKEY() or DECRYPTBYCERT() function. We are however spared from opening any certificates or keys, rather we just recall the phrase that we used. Simple enough just like ENCRYPTBYPASSPHRASE() function's the mandate arguments are first 2. @passphrase & @ciphertext.

Let’s see this with a use of example

USE TESTDB

GO

DECLARE @DATAforENCRYPTION NVARCHAR(4000)

DECLARE @ENCRYPTEDdata VARBINARY(8000)

DECLARE @PHRASE VARCHAR(MAX)

DECLARE @DECRYPTEDdata NVARCHAR(4000)

SET @DATAforENCRYPTION = 'SECRET CODES ARE NEVER REVEILED'

SET @PHRASE = 'ILL ACT AS THE ENCRYPTING KEY'

SET @ENCRYPTEDdata = ENCRYPTBYPASSPHRASE(@PHRASE,@DATAforENCRYPTION)

       PRINT 'DATA AFTER ENCRYPTION IN VARBINARY FORM'

       PRINT @ENCRYPTEDdata

SET @DECRYPTEDdata = DECRYPTBYPASSPHRASE(@PHRASE,@ENCRYPTEDdata)

       PRINT 'DATA AFTER ENCRYPTION IN NVARCHAR FORM AGAIN'

       PRINT @DECRYPTEDdata

The only drawback here is if you know the location or column within the table where data is encrypted and stored and if someone gets hold of your passphrase.....; well nothing more is left to decrypt then. Often you may want to club this with TDE.

That leaves us with an explanation of 2 functions

3)  DECRYPTBYKEYAUTOASYMKEY()

Syntax: https://msdn.microsoft.com/en-us/library/ms365420.aspx

This function is used to decrypt data that is encrypted using symmetric keys which in turn is protected by an asymmetric key. 

Its combines the functionality of code segments OPEN SYMMETRIC KEY & DecryptByKey() in one function DECRYPTBYKEYAUTOASYMKEY()

4)  DECRYPTBYKEYAUTOCERT().

Syntax: https://msdn.microsoft.com/en-us/library/ms182559.asp 

This function is used to decrypt data that is encrypted using symmetric keys which in turn is protected by certificate. 

Its combines the functionality of code segments OPEN SYMMETRIC KEY & DecryptByKey() in one function DECRYPTBYKEYAUTOCERT().

None other than MVP K Brian Kelly has already described them beautifully in the below blogs.

https://www.mssqltips.com/sqlservertip/3081/using-views-to-expose-encrypted-data-in-sql-server/

https://www.mssqltips.com/sqlservertip/1760/using-authenticators-with-sql-server-encryption/

 I'd suggest you refer to them once. 

This being the last blog for this series covers the 5 techniques to encrypt data within SQL server. However that's not all. Its the responsibility of the person encrypting the data to ensure the keys, certificates are backed up accordingly and in a timely fashion. Just like stored procedures and tables access will have to be granted for users to view the encrypted data or decrypt the data. The very sound mechanism provided by SQL server to protect data can become a nuisance if access is not granted wisely and keys or certificates are lost during decrypt or restore operations.