Home
%3CLINGO-SUB%20id%3D%22lingo-sub-662602%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2386%3A%20Encrypting%20columns%20data%20in%20Azure%20SQL%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662602%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Team%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20where%20our%20customer%20needs%20to%20encrypt%20some%20columns.%20Besides%20the%20options%20that%20we%20have%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsecurity%2Fencryption%2Falways-encrypted-database-engine%3Fview%3Dsql-server-2017%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ealways%20encrypted%3C%2FA%3E%20or%20other%26nbsp%3Blike%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsecurity%2Fencryption%2Fencrypt-a-column-of-data%3Fview%3Dsql-server-2017%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esymmetric%20key%3C%2FA%3E%20and%20don't%20forget%20that%20Azure%20SQL%20Database%20by%20default%20the%20database%20is%20created%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsecurity%2Fencryption%2Ftransparent-data-encryption%3Fview%3Dsql-server-2017%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETDE%3C%2FA%3E%20enable%20and%20other%20security%20options%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsecurity%2Fazure-database-security-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ethat%20we%20have%20connecting%20to%20the%20database%3C%2FA%3E.%20I%20suggested%20to%20use%20%3CSPAN%20data-ttu-id%3D%2225c42-102%22%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fencryptbypassphrase-transact-sql%3Fview%3Dsql-server-2017%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EENCRYPTBYPASSPHRASE%3C%2FA%3E%20or%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fdecryptbypassphrase-transact-sql%3Fview%3Dsql-server-2017%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDECRYPTBYPASSPHRASE%3C%2FA%3E%20as%20another%20alternative%20to%20encrypt%20the%20data%20for%20this%20service%20request.%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ttu-id%3D%2225c42-102%22%3EFollowing%20I%20would%20like%20to%20share%20with%20you%20this%20example%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ttu-id%3D%2225c42-102%22%3ETable%20definition%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ECREATE%20TABLE%20Users%20(UserName%20varbinary(256)%2C%20Password%20varbinary(256))%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ttu-id%3D%2225c42-102%22%3ETo%20encrypt%20the%20data%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3EDECLARE%20%40PassphraseEnteredByUser%20nvarchar(128)%3B%20%20%0ASET%20%40PassphraseEnteredByUser%20%20%20%0A%20%20%20%20%3D%20'%C2%A1SQL%20Blog%20Azure%20SQL%20Database%20is%20the%20best!'%3B%20%20%0A%20%20%0AINSERT%20INTO%20Users%20(UserName%2CPassword)%20%0Avalues(%20EncryptByPassPhrase(%40PassphraseEnteredByUser%2C%20'UserPower1'%2C%201%2C%20CONVERT(%20varbinary%2C%20'Administrator'))%2C%0A%20%20%20%20%20%20%20%20EncryptByPassPhrase(%40PassphraseEnteredByUser%2C%20'P0!as%25%C3%B1worD'%2C%201%2C%20CONVERT(%20varbinary%2C%20'Administrator')))%0AGO%20%20%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20data-ttu-id%3D%2225c42-102%22%3ETo%20decrypt%20the%20data%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EDECLARE%20%40PassphraseEnteredByUser%20nvarchar(128)%3B%20%20%0ASET%20%40PassphraseEnteredByUser%20%20%20%0A%20%20%20%20%3D%20'%C2%A1SQL%20Blog%20Azure%20SQL%20Database%20is%20the%20best!'%3B%20%0A%20%20%0A--%20Decrypt%20the%20encrypted%20record.%20%20%0ASELECT%20CONVERT(varchar(128)%2CDecryptByPassphrase(%40PassphraseEnteredByUser%2C%20UserName%2C%201%2C%20CONVERT(%20varbinary%2C%20'Administrator')))%2C%0A%20%20%20%20%20%20%20CONVERT(varchar(128)%2CDecryptByPassphrase(%40PassphraseEnteredByUser%2C%20Password%2C%201%2C%20CONVERT(%20varbinary%2C%20'Administrator')))%0AfROM%20Users%3C%2FPRE%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-662602%22%20slang%3D%22en-US%22%3E%3CP%3EBesides%20the%20options%20that%20we%20have%20using%26nbsp%3Balways%20encrypted%20or%20other%26nbsp%3Blike%20symmetric%20keys%20and%20don't%20forget%20that%20Azure%20SQL%20Database%20by%20default%20the%20database%20is%20created%20with%20TDE%20enable.%20I%20suggested%20to%20use%20%3CSPAN%20data-ttu-id%3D%2225c42-102%22%3EENCRYPTBYPASSPHRASE%20or%20DECRYPTBYPASSPHRASE%20as%20another%20alternative%20to%20encrypt%20the%20data%20for%20this%20service%20request.%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-662694%22%20slang%3D%22en-US%22%3ERe%3A%20Lesson%20Learned%20%2386%3A%20Encrypting%20columns%20data%20in%20Azure%20SQL%20Database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-662694%22%20slang%3D%22en-US%22%3EPasswords%20should%20not%20be%20reversible.%20Ever.%20I'm%20sure%20this%20is%20just%20an%20example%2C%20but%20it%20should%20be%20changed%20to%20use%20something%20other%20than%20user%2Fpassword%20for%20the%20columns%20so%20that%20people%20don't%20take%20this%20example%20and%20think%20it's%20OK%20to%20roll%20into%20production%20with%20something%20like%20this.%3C%2FLINGO-BODY%3E

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!

1 Comment
Senior Member
Passwords should not be reversible. Ever. I'm sure this is just an example, but it should be changed to use something other than user/password for the columns so that people don't take this example and think it's OK to roll into production with something like this.