T-SQL: column-level encryption

--step 1
USE TESTDB ;
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '123Alex!@#$789';
--DROP MASTER KEY
--step 2
CREATE TABLE TABLE3
(ID int,
PERSONNAME nvarchar(200),
AGE int,
HOBBY nvarchar(200),
SALARY varbinary(128))
--step 3
CREATE CERTIFICATE MyCertificate1
WITH SUBJECT = 'Secret info - Salary';
GO
--drop CERTIFICATE MyCertificate1
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MyCertificate1;
GO
--drop SYMMETRIC KEY SSN_Key_01
--step 4
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE MyCertificate1;
INSERT INTO TABLE3 VALUES
(1,'Alex',40,'skateboarding', EncryptByKey(Key_GUID('SSN_Key_01'), '1000')),
(2,'John',32,'wrestling',EncryptByKey(Key_GUID('SSN_Key_01'), '1500')),
(3,'Asti',25,'jogging',EncryptByKey(Key_GUID('SSN_Key_01'), '24560')),
(4,'Jay',53,'football',EncryptByKey(Key_GUID('SSN_Key_01'), '502')),
(5,'Selena',40,'yoga',EncryptByKey(Key_GUID('SSN_Key_01'), '2500'))
--truncate table TABLE3
--DROP TABLE TABLE3
--step 5
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE MyCertificate1;
SELECT
*,
CONVERT(varchar, DecryptByKey(SALARY)) AS [Decrypted Salary]
FROM TABLE3
CLOSE SYMMETRIC KEY SSN_Key_01

[@superaijaz]

Best Video with all the steps/sample code provided. Thanks a lot Alex!

[@AlexTverskoy]

Great video. Simple and clear. Alex, thank you very much!

[@aleksejspopovics3473]

it's awesome!!! It's really helpful. Please continue to make such excellent videos. I have question, Can encrypt whole SQL table?

[@emo10001]

Nice video and good explanation. I assume this only encrypts the data at rest correct? Not in transit?

[@stephenremillard9462]

Great explanation. Is it still relevant with recent version of SQL Server?

[@ayasmin11]

Really nice video :)

[@akashchugh9121]

Hi Alex,
If we need to remove encryption on Salary column.
Then what will have to do ?
Please suggest .
Thanks

[@ConanNYC]

great vidoe , what if i backup and restore the db to another server?> thanks

[@farisahmed3524]

Thanks for sharing this but, I've a problem when I run Step 5. I'm inserting 5 digits into the Salary column and only the first digit is displayed when I run Step 5. any idea why is that happening? Thanks!

[@anilparmar195]

Try to use varchar(10) instead of varchar

[@NALLELYlINdA1]

Maybe you aren't doing the righ convertion, remember that you need to put a lenght

[@bn6942]

It doesn't work for me