Data Encryption and Decryption in SQL Server

--Step 1: Create a Master Key
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='Password!2';

GO
--Step 2: Create Certificate
CREATE CERTIFICATE Cert_Password
ENCRYPTION BY PASSWORD = 'Password!2'
WITH SUBJECT = 'Password protection',
EXPIRY_DATE = '12/31/2099';

--Step 3: Create Symmetric Key
CREATE SYMMETRIC KEY Sym_password
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert_Password;

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 256

GO
--Step 4: Encrypt Data

OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';

DECLARE @tempSecurity AS TABLE (UserID VARCHAR(200), Password NVARCHAR(1000))
INSERT INTO @tempSecurity (UserID, Password)
VALUES ('schinna',ENCRYPTBYKEY(KEY_GUID(N'Sym_password'), 'PASSWORD'))
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

--Step 5: Decrypt Data
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE Cert_Password WITH PASSWORD = 'Password!2';
SELECT UserID, CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM @tempSecurity
CLOSE SYMMETRIC KEY Sym_password;

SELECT * FROM @tempSecurity

GO
--DROP ALL THE KEY
DROP SYMMETRIC KEY Sym_password
GO
DROP CERTIFICATE  Cert_Password
GO
DROP MASTER KEY