SQL Column level Encryption / Decryption

----------- Prerequisites to be done --------------------Start----------

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

--CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';

--CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data';

--CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1;

----------- Prerequisites to be done --------------------End----------

OPEN SYMMETRIC KEY SymmetricKey1

DECRYPTION BY CERTIFICATE Certificate1;

Declare @Card_number as varchar(25)

set @Card_number = '1234'

select @Card_number OrginalVale, EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number) Encryptedvalue, CONVERT(varchar, DecryptByKey(EncryptByKey (Key_GUID('SymmetricKey1'),@Card_number))) Decryptedvalue

CLOSE SYMMETRIC KEY SymmetricKey1;