Lesson Learned #86: Encrypting columns data in Azure SQL Database

[Jose_Manuel_Jurado]

Hello Team,

Today, I worked on a service request where our customer needs to encrypt some columns. Besides the options that we have using always encrypted or other like symmetric key and don't forget that Azure SQL Database by default the database is created with TDE enable and other security options that we have connecting to the database. I suggested to use ENCRYPTBYPASSPHRASE or DECRYPTBYPASSPHRASE as another alternative to encrypt the data for this service request.

Following I would like to share with you this example:

Table definition:

CREATE TABLE Users (UserName varbinary(256), Password varbinary(256))

To encrypt the data:

DECLARE @PassphraseEnteredByUser nvarchar(128);  
SET @PassphraseEnteredByUser   
    = '¡SQL Blog Azure SQL Database is the best!';  
  
INSERT INTO Users (UserName,Password) 
values( EncryptByPassPhrase(@PassphraseEnteredByUser, 'UserPower1', 1, CONVERT( varbinary, 'Administrator')),
        EncryptByPassPhrase(@PassphraseEnteredByUser, 'P0!as%ñworD', 1, CONVERT( varbinary, 'Administrator')))
GO  

To decrypt the data:

DECLARE @PassphraseEnteredByUser nvarchar(128);  
SET @PassphraseEnteredByUser   
    = '¡SQL Blog Azure SQL Database is the best!'; 
  
-- Decrypt the encrypted record.  
SELECT CONVERT(varchar(128),DecryptByPassphrase(@PassphraseEnteredByUser, UserName, 1, CONVERT( varbinary, 'Administrator'))),
       CONVERT(varchar(128),DecryptByPassphrase(@PassphraseEnteredByUser, Password, 1, CONVERT( varbinary, 'Administrator')))
fROM Users

Enjoy!