USE BikeStores;
GO

/* Create a database master key for column level SQL Server encryption */

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';
GO

/* Use the sys.symmetric_keys catalog view to verify the existence of this database master key in SQL Server encryption */

SELECT name KeyName, 
    symmetric_key_id KeyID, 
    key_length KeyLength, 
    algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;
GO

/* Create a self-signed certificate for column-level SQL Server encryption */

CREATE CERTIFICATE BikeStores_Cert 
WITH SUBJECT = 'BikeStores data protection';
GO

/* Verify the certificate using the catalog view sys.certificates */

SELECT name CertName, 
    certificate_id CertID, 
    pvt_key_encryption_type_desc EncryptType, 
    issuer_name Issuer
FROM sys.certificates;
GO

/* Establish a symmetric key, which entails a single key for encryption and decryption */

CREATE SYMMETRIC KEY BikeStores_SymKey 
WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE BikeStores_Cert;

/* Check the existing keys using catalog view for column-level SQL Server Encryption */

SELECT name KeyName, 
    symmetric_key_id KeyID, 
    key_length KeyLength, 
    algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;

/* Establish an encrypted column to store encrypted credit card numbers */

ALTER TABLE Sales.customer_credit_cards
ADD credit_card_number_encrypt varbinary(MAX);
GO

/* Open the symmetric key and decrypt using the certificate */

OPEN SYMMETRIC KEY BikeStores_SymKey
DECRYPTION BY CERTIFICATE BikeStores_Cert;
GO

/* Use the following UPDATE statement to encrypt the customer credit card numbers. 
It uses EncryptByKey function and uses the symmetric function for encrypting the 
credit_card_number column and updates the values in the newly created 
credit_card_number_encrypt column */

UPDATE Sales.customer_credit_cards
SET credit_card_number_encrypt = EncryptByKey (Key_GUID('BikeStores_SymKey'), credit_card_number)
FROM Sales.customer_credit_cards;
GO

/* Close the symmetric key */

CLOSE SYMMETRIC KEY BikeStores_SymKey;
GO

/* Remove the unencrypted customer credit card numbers */

ALTER TABLE Sales.customer_credit_cards
DROP CONSTRAINT UQ_customer_credit_cards;
GO  

ALTER TABLE Sales.customer_credit_cards
DROP COLUMN credit_card_number;
GO

/* Open the symmetric key and decrypt using the certificate */

OPEN SYMMETRIC KEY BikeStores_SymKey
DECRYPTION BY CERTIFICATE BikeStores_Cert;
GO

/* Use the SELECT statement to show data decrypted using the DecryptByKey() function */

SELECT first_name, last_name, credit_card_number_encrypt AS 'Encrypted Data',
CONVERT(VARCHAR, DecryptByKey(credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM Sales.customers C INNER JOIN Sales.customer_credit_cards CCC
ON C.customer_id = CCC.customer_id;

/* Close the symmetric key */

CLOSE SYMMETRIC KEY BikeStores_SymKey;
GO