Encryption demo updated

USE master
GO
CREATE DATABASE EncryptionDemo
GO
Use   EncryptionDemo
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


 --Create test table
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
('Nathan', 'Jones','1111-1111-1111-1111'),
('Mike', 'Mitchell','2222-2222-2222-2222'),
('Dan', 'Straton', '3333-3333-3333-3333')
GO

--view data
SELECT * from Customer
go


 ----FROM HERE Actuall Step Start
ALTER TABLE Customer
ADD NewCreditCardNumber Varbinary(256)
GO

--CHECK DATA
SELECT * from Customer


 --Must open a key to update data
OPEN SYMMETRIC KEY EncryptColumn DECRYPTION BY CERTIFICATE CertificateforColumns
UPDATE Customer
SET NewCreditCardNumber = ENCRYPTBYKEY(KEY_GUID('EncryptColumn'),CreditCardNumber)
GO

--CHECK DATA
SELECT * from Customer

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

CLOSE SYMMETRIC KEY   EncryptColumn;
GO



 --cannot view the Card details : as we did not open the key
 SELECT CustomerID,FirstName,LastName,CreditCardNumber,CONVERT(VARCHAR(50),DECRYPTBYKEY(NewCreditCardNumber)) AS  NewCreditCardNumber
from Customer



OPEN SYMMETRIC KEY EncryptColumn DECRYPTION BY CERTIFICATE CertificateforColumns
SELECT * FROM Customer---cannot view NewCreditCardNumber even if we open, we have to use DECRYPTBYKEY for the coulumn to view the data

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






--Test INSERT:1
OPEN SYMMETRIC KEY EncryptColumn DECRYPTION BY CERTIFICATE CertificateforColumns

DECLARE @CreditCardNumber VARCHAR(128)
SET @CreditCardNumber ='123456789087'
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber,NewCreditCardNumber)
VALUES ('Tom', 'Hanks',  @CreditCardNumber,EncryptByKey( Key_GUID('EncryptColumn'), CONVERT(varchar, @CreditCardNumber)))

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

CLOSE SYMMETRIC KEY   EncryptColumn;
GO

--Test INSERT:2: This will have NUll as we closed the key and inserting the data
--: WE MUST HAVE THE KEY OPEN WHEN WE UPDATE OR INSERT DATA

DECLARE @CreditCardNumber VARCHAR(128)
SET @CreditCardNumber ='098-989-5423'
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber,NewCreditCardNumber)
VALUES ('Brad', 'Pitt',  @CreditCardNumber,EncryptByKey( Key_GUID('EncryptColumn'), CONVERT(varchar, @CreditCardNumber)))

OPEN SYMMETRIC KEY EncryptColumn DECRYPTION BY CERTIFICATE CertificateforColumns

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

CLOSE SYMMETRIC KEY   EncryptColumn;
GO



--Test INSERT:2: OPEN and INSERT AGAIN
--: WE MUST HAVE THE KEY OPEN WHEN WE UPDATE OR INSERT DATA

 OPEN SYMMETRIC KEY EncryptColumn DECRYPTION BY CERTIFICATE CertificateforColumns
DECLARE @CreditCardNumber VARCHAR(128)
SET @CreditCardNumber ='098-098-0098'
INSERT INTO dbo.Customer(FirstName,   LastName, CreditCardNumber,NewCreditCardNumber)
VALUES ('Morgan', 'Freeman',  @CreditCardNumber,EncryptByKey( Key_GUID('EncryptColumn'), CONVERT(varchar, @CreditCardNumber)))



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

CLOSE SYMMETRIC KEY   EncryptColumn;
GO




Notes:
--Application must use EncryptByKey( Key_GUID('EncryptColumn'), CONVERT(varchar, @CreditCardNumber)) TO insert / Update Data

--need to check if any triggers update Encrypted Columns, also Jobs

--Replication Handles Encryption, we must eput the keys on Every subscribed server if we ant to view the data

-- WE WILL have to discuss what to do with Existing original CreditCardNumber ???

--To decrypt the encrypted data we need to use another inbuilt function called DecryptByKey

--When we try encrypting or decrypting data without opening the symmetric key, the command will not fail-- but it will not work

--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.







--ALTER TABLE Customer
--DROP COLUMN CreditCardNumber;
--GO
--SELECT CustomerID,FirstName,LastName,  CONVERT(VARCHAR(50),DECRYPTBYKEY(NewCreditCardNumber)) AS NewCreditCardNumber
--from Customer

----rename back to original
--Sp_rename 'Customer.NewCreditCardNumber', 'CreditCardNumber'

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

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