How to implement Encryption in Sensitive data column

USE master
GO
CREATE DATABASE CellLevelEncryptionDemo
GO
Use   CellLevelEncryptionDemo
GO
--create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'F#4*KN^7$';
GO

--create a certificate for use as the database encryption key   (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE CertificateforColumns
WITH SUBJECT = 'Certificate for Column Level encryption';
GO


--Create a symmetric key and encrypt it by using above created   certificate
CREATE SYMMETRIC KEY   EncryptColumn
     WITH ALGORITHM   = AES_256 --Supported encryption algorithms are AES with 128-bit,   192?bit, or 256?bit keys or 3 Key Triple DES
     ENCRYPTION BY   CERTIFICATE CertificateforColumns;
GO


--To decrypt the encrypted data you need to use another inbuilt   function called DecryptByKey
--When you try encrypting or decrypting data without opening the symmetric key, the command will not fail but also, it will not work; for example the command given below will not fail as symmetric is not open but it will return NULLs instead of data:
--It’s very important and essential to take a backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.
--Please note, enabling encryption at cell level has overhead and performance penalties (as it is a resource intensive operation and salts the data differently, which causes a table scan) as discussed above and hence it’s recommended to first evaluate the need and
--then plan for its implementation.



-- drop table Customer
--go
CREATE TABLE dbo.Customer
(
CustomerID   INT IDENTITY PRIMARY KEY,
FirstName   VARCHAR(100),
LastName   VARCHAR(100),
CreditCardNumber   Varchar(128) NULL
)
GO
 INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Steve', 'Savage','1111-1111-1111-1111'),
('Ranjit', 'Srivastava','2222-2222-2222-2222'),
('Akram', 'Haque', '3333-3333-3333-3333')
GO

SELECT * from Customer
go


-----------FROM HERE Actually Steps Start

ALTER TABLE Customer
ADD DecCreditCardNumber VARBINARY(256)
GO


UPDATE Customer
SET DecCreditCardNumber = ENCRYPTBYKEY(KEY_GUID('EncryptColumn'),CreditCardNumber)
GO

--CHECK DATA
SELECT * from Customer

SELECT CustomerID,FirstName,LastName,CreditCardNumber,DecCreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(DecCreditCardNumber)) AS DecryptSecondCol
from Customer

CLOSE SYMMETRIC KEY   EncryptColumn;
GO

 --CHeck any Indexes, FK, Constraints
 --Backups

--Backup Database or table
Alter table Customer
drop Column CreditCardNumber


 Alter table Customer
 --sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

 sp_RENAME 'Customer.DecCreditCardNumber' , 'CreditCardNumber', 'COLUMN'

 --CHeck any Indexes, FK, Constraints

SELECT * from Customer
go

 SELECT CustomerID,FirstName,LastName,CreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer








--if you're using cell encryption then you need to call the encryptby or decryptby methods for each insert\select. The app code would need to reflect this
 --Insert method: Joe need to change
Declare @CreditCardNumber Varchar(128)
SET @CreditCardNumber ='123456789087'
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber)
VALUES
('Kari', 'Singh', EncryptByKey(Key_GUID('EncryptColumn'),@CreditCardNumber ))




OPEN SYMMETRIC KEY EncryptColumn DECRYPTION
BY CERTIFICATE CertificateforColumns
SELECT CustomerID,FirstName,LastName, CreditCardNumber,CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer

SELECT *
from Customer
CLOSE SYMMETRIC KEY   EncryptColumn;
GO