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
