Encryption and Decryption in SQL Server

Encryption and decryption

Encryption and Decryption
* To provide security for data we need these features.
* To encrypt or decrypt we need algorithm.
* SS supports windows algorithms like DES,SHA1, MD5
* Steps

 1. create master key
 2. create certificate
 3. Create symmetric key
 4. Encrypt data using EncryptByKey function.

Example

--Creating table
create table customers
(
CustomerID int identity(1,1) NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
CreditCardNumber nvarchar(20) NULL,
EncryptedCreditCardNumber varbinary(128) null,
Constraint Pk_customers primary key clustered(CustomerID)
)

--Inserting Sample data
insert into customers values('Jetson','George','1234-4335-4356',null)
insert into customers values('Ravi','Kim','1211-4222-4344',null)

--Viewing data
select * from customers

--creating master key
create master key encryption by password='P@ssw0rd'

--creating certificate
create certificate CreditCardCert with subject ='Customer Credit Card Numbers'

--creating symmetric key
create symmetric key CreditCardKey1 WITH
ALGORITHM=DES encryption by certificate CreditCardCert

--Opening key
open symmetric key CreditCardKey1
decryption by Certificate CreditCardCert

--Encrypting data of CreditCardNumber column
update customers set encryptedcreditcardnumber=
encryptbykey(key_guid('CreditCardKey1'),CreditCardNumber)

--Verifying
select * from customers

--Close symmetric key
close symmetric key CreditCardKey1

-- Opening symmetric key
open symmetric key CreditCardKey1
decryption by Certificate CreditCardCert

--Decrypt data
select customerid,lastname,firstname,creditcardnumber,
convert(nvarchar,decryptbykey(encryptedcreditcardnumber)) as
'Decrypted Credit Card Number' from customers