UPDATE encryped column in SQL table

Question

Hi
would you be able to explain to me how to update a record onto an encrypted column in SQL Table

i tried using the logic

OPEN SYMMETRIC KEY SymmetricKeyA
DECRYPTION BY CERTIFICATE CertificateA
DECLARE @Address VARBINARY(256) = '10 Coral Street'
UPDATE AdventureWorks.dbo.Address
SET PostalAddress = CONVERT(VARBINARY(256),@Address)
WHERE ID = 1
CLOSE SYMMETRIC KEY SymmetricKeyA

but when i check the column again it populating a "null" value
why would that be the case, did i miss something ?

Answer 1

I created a new column named AddressEncrypted and it works forme using EncryptByKey/DecryptByKey.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyTest!Mast3rP4ss';  
  
--verify by open/close.  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyTest!Mast3rP4ss';  
--CLOSE MASTER KEY;  
  
CREATE CERTIFICATE Sales09    
   WITH SUBJECT = 'Addresses';    
GO    
  
CREATE SYMMETRIC KEY Addresses_Key11    
    WITH ALGORITHM = AES_256    
    ENCRYPTION BY CERTIFICATE Sales09;    
GO    
  
-- Create a column in which to store the encrypted data.    
ALTER TABLE SalesLT.Address    
    ADD Address_Encrypted varbinary(160);     
GO    
  
-- Open the symmetric key with which to encrypt the data.    
OPEN SYMMETRIC KEY Addresses_Key11    
   DECRYPTION BY CERTIFICATE Sales09;    
  
-- Encrypt the value in column Address using the    
-- symmetric key Addresses_Key11.    
-- Save the result in column Address_Encrypted.      
UPDATE SalesLT.Address   
SET Address_Encrypted = EncryptByKey(Key_GUID('Addresses_Key11')    
    , AddressLine1, 1, HASHBYTES('SHA2_256', CONVERT( varbinary    
    , AddressID)));    
GO    
  
-- Verify the encryption.    
-- First, open the symmetric key with which to decrypt the data.    
  
OPEN SYMMETRIC KEY Addresses_Key11    
   DECRYPTION BY CERTIFICATE Sales09;    
GO    
  
-- Now list the original card number, the encrypted card number,    
-- and the decrypted ciphertext. If the decryption worked,    
-- the original number will match the decrypted number.    
  
SELECT AddressLine1, Address_Encrypted     
    AS 'Encrypted address', CONVERT(nvarchar,    
    DecryptByKey(Address_Encrypted, 1 ,     
    HASHBYTES('SHA2_256', CONVERT(varbinary, AddressID))))    
    AS 'Decrypted address' FROM SalesLT.Address;    
GO    

The results is:

Answer 2

I think the declaration of the variable @address may not be correct with the data type of VARBINARY. It should be VARCHAR.

Answer 3

Hi，@Ronald Van Der Westhuizen

Welcome to Microsoft T-SQL Q&A Forum!

Here I offer my thoughts

1. First check if your master key is created correctly Use the command: SELECT * FROM sys.symmetric_keys (if your version is 2008 or CTP6, please pay attention to the extra options of the command: key_length, name, etc.)
2. Execute the EncryptByKey command without opening the symmetric key, the EncryptByKey function will return a NULL value during the encryption call. ok here i see you have turned on, rule this out
3. Try to modify your code update yourtable set your_encryption_column=encryptbykey(key_GUID(''PasswordFieldSymmetricKey",@Address)))
4. You can refer to the code of Alberto Morillo above. I tried it without any problem. If there is still a problem, I suggest you provide us with some data of the table creation and the name of the certificate, and we will test it for you.

Best regards, Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.