--// Step 1 Create a Master Key CREATE COLUMN MASTER KEY [MasterKey1] WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/B6601D804D204FFF47F5155D827ECD639AAC9DAD' ) GO --// Step 2 Encrypt Key CREATE COLUMN ENCRYPTION KEY [EncryptKey1] WITH VALUES ( COLUMN_MASTER_KEY = [MasterKey1], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006200360036003000310064003800300034006400320030003400660066006600340037006600350031003500350064003800320037006500630064003600330039006100610063003900640061006400B327F3D2ECC9AA6611222FF39D4134D4732FB35B83109428821D4BECB39DA098CD5863B8EB3F20BF0A3D36F6514A409BBE5FC12198B1959165A89DC7B51D5FF07847085EF4ADA36CD7FE237DA95CE603FC04D2CAC692D52CB2FB4020DBCCB6F7938341394168DFA0924B7938A2A60E4C56E450AE89126BEFBE7E63FB550F8689078892968DF5EC06321B2FF70B8F2BEEBE775F9452623CF257412FB25C3BCC665ED87C078AC5E286465A46D25E0AFD053D7045CE5393AC4ED8D8AC18C6A9241D219107F2543423116F27493E68C56008CABF79EA8A537F53A136C9D0C2521BC779CBC5EEE7C8AB8F4D6E709E66EC3B0F75B83B437E51ED339AADACAC631E120C8D0A10ED84BA760A12A83E7C8A4DEE96142B45C659A879AB33616B63EEEDDCFF7EA61422B8E11A3C3B4C220FCA8C3FBB1E2861ED12C5BFBA9DD429CA7AD934B35481FF6D8D66AF541FB9846CA87C157991AC37AA8474087756C97A33F51A69729D881D60170CE53CA52D42F24D1F6BE57F1FE50FA3B2B5FC03ECA188983206AA9FC9D581C3D31BEC19C6A0C89A590A5DD2D815E8B3BB8262FD865DD6339E70489FB3568C7D0A8615F228C384C8AA5A6A5F477E8A1870A084821DF75DFDED474F5E140AE3E68BC5B467B0C0073A596EBA9D27277488519284DFD8F7B443F4141F23571DE65817CD948321A76E25AD4853F887DCEB7926468ECA89504BF2FE455D ) GO --// Step 3 Sample Table --// Sample Deterministic and Randomized encyption types CREATE TABLE [dbo].[_Test] ( Id uniqueidentifier default (newid()) PRIMARY KEY, LastName VARCHAR(32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = EncryptKey1 ) NOT NULL, FirstName NVARCHAR(32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = EncryptKey1 ) NULL, Ssn INT ENCRYPTED WITH ( ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = EncryptKey1 ) NOT NULL, SsnText CHAR(11) ENCRYPTED WITH ( ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = EncryptKey1 ) NOT NULL ) GO --// Step 4 Sample Insert declare @lastName VARCHAR(32) = 'test1'; declare @firstName NVARCHAR(32) = 'tester'; declare @ssn INT = 123456789; declare @ssnText CHAR(11) = '123-45-6789'; INSERT INTO [dbo].[_Test]([LastName],[FirstName],[Ssn],[SsnText]) values (@lastName,@firstName,@ssn,@ssnText); GO declare @lastName VARCHAR(32) = 'test2'; declare @firstName NVARCHAR(32) = 'tester'; declare @ssn INT = 987654321; declare @ssnText CHAR(11) = '987-65-4321'; INSERT INTO [dbo].[_Test]([LastName],[FirstName],[Ssn],[SsnText]) values (@lastName,@firstName,@ssn,@ssnText); GO --// Step 5 Sample Select --// --// SELECT * FROM [dbo].[_Test] GO