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
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. 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()
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().
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. 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.
