Sample Code For Sql Server Cell Level Encryption

Sample Code below for Sql Server cell level encryption shows verified code

--drop table t_employees

--drop symmetric key sk_employees

-- Creates an asymmetric key encrypted by password 'Ivp@India'

CREATEASYMMETRICKEY Asym_Raptor

WITHALGORITHM=rsa_2048

ENCRYPTIONBYPASSWORD='Ivp@India'

-- Verify Asymmetric Key

select*from [sys].[asymmetric_keys]

-- create a symmetric key encrypted by asymmetric key to protect the employee sensitive data, in this case - the salary

createsymmetrickey sk_employees withalgorithm=aes_256ENCRYPTIONBYASYMMETRICKEY Asym_Raptor         --encryption by password = 'Kamal@1887';

-- Verify Key Created

select*from [sys].[symmetric_keys]

-- Create Sample Table

createtable t_employees(id intidentity(1,1), name varchar(300), salary varbinary(300));

-- open the key so that we can use it

--open symmetric key sk_employees decryption by password = 'Kamal@1887';

OPENSYMMETRICKEY sk_employees

DECRYPTIONBYASYMMETRICKEY Asym_Raptor

WITHPASSWORD='Ivp@India'

-- verify key was opened

select*fromsys.openkeys;

-- insert some data without using authenticator

insertinto t_employees values ('Alice Smith',encryptbykey(key_guid('sk_employees'),'$200000'));

insertinto t_employees values ('Bob Jones',encryptbykey(key_guid('sk_employees'),'$100000'));

-- see the result; salary is encrypted

select*from t_employees;

update t_employees set salary='$900000'where id=1

-- see decrypted result;

select id, name,convert(varchar(10),decryptbykey(salary))as salary from t_employees

-- Update Table Data

update t_employees set salary=encryptbykey(key_guid('sk_employees'),'$400000')where id=1

-- see decrypted result;

select id, name,convert(varchar(10),decryptbykey(salary))as salary from t_employees

-- close the key

closesymmetrickey sk_employees