SQL server encrypt data at column level

1. SQL server master key is the root of SQL server encryption hierarchy. Check it exists or not.

SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';

2. Create master key - "encrypt by password" argument, it defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption. keep the encryption password at safe place or you can take the backup of same.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PA55w04D!';
GO


CREATE CERTIFICATE encrypt_certificate
WITH SUBJECT = 'Encrypt Data';
GO


3. Create Symmetric Key it is used for both encryption and decryption.
CREATE SYMMETRIC KEY Symmetric_Key1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE encrypt_certificate;
GO


create table dummy (id int , name varbinary(2000));

We need to open symmetric key before use:
OPEN SYMMETRIC KEY Symmetric_Key1 DECRYPTION BY CERTIFICATE encrypt_certificate;

insert into dummy values (1, EncryptByKey(Key_GUID('Symmetric_Key1'),'data'));

select * from dummy;

we we can decrypt the data in table?
SELECT CONVERT(varchar, DecryptByKey(name)) AS 'Decrypted name'