Step 1 - Create a sample SQL Server table
Let’s use an example where we create the dbo.Customer_data table which contains credit card details for customers. Our task is to protect this data by encrypting the column, which contains the credit card number. I will populate it will some sample data as shown below.
CREATE TABLE dbo.Customer_data( Customer_id INT CONSTRAINT Pkey3 PRIMARY KEY NOT NULL , Customer_Name VARCHAR(100) NOT NULL , Credit_card_number VARCHAR(25) NOT NULL ) INSERT INTO dbo.Customer_data SELECT 1,'Cust1','1111-2222-3333' UNION ALL SELECT 2,'Cust2','1452-2563-1526' UNION ALL SELECT 3,'Cust3','2147-4526-4587' SELECT * FROM dbo.Customer_dataOUTPUT
Step 2 - SQL Server Service Master Key
The Service Master Key is the root of the SQL Server encryption hierarchy. It is created during the instance creation. Confirm it's existence using the query below. If it does not exist we need to manually create it.
USE master GO SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##'
Step 3 - SQL Server Database Master Key
The next step is to create a database master key. This is accomplished using the CREATE MASTER KEY method. The "encrypt by password" argument is required and defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption. It is important that you keep the encryption password in a safe place and/or keep backups of your SQL Server Database Master Key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pwd@123'
Step 4 - Create a Self Signed SQL Server Certificate
The next step is to create a self-signed certificate that is protected by the database master key. A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. An optional argument when creating a certificate is ENCRYPTION BY PASSWORD. This argument defines a password protection method of the certificate's private key. In our creation of the certificate we have chosen to not include this argument; by doing so we are specifying that the certificate is to be protected by the database master key.
CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Secure Data'
Step 5 - SQL Server Symmetric Key
A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database
CREATE SYMMETRIC KEY SymmetricKey1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1
Step 6 - Schema changes
An Encrypted column can only be of datatype varbinary and since the column we want to encrypt is of datatype varchar, we have to create a new column and populate it with encrypted values.
ALTER TABLE Customer_data ADD Credit_card_number_encrypt VARBINARY(MAX) NULL
Step 7 - Encrypting the newly created column
To encrypt the data we will use the EncryptByKey Command. Below is a sample code which can be used to encrypt the data. Please note that symmetric key needs to opened before we can encrypt data and be sure you manually close the key else it will remain open for the current session.
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; GO UPDATE Customer_data SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number) FROM dbo.Customer_data; GO CLOSE SYMMETRIC KEY SymmetricKey1;OUTPUT
Step 8 - Remove old column
To finalize this process, let's remove the old column so that the table is left only with the encrypted data.
ALTER TABLE Customer_data DROP COLUMN Credit_card_number;
Step 9 - Reading the SQL Server Encrypted Data
Let's take a look at an example of reading data using the decrypt bykey option. As we indicated before, make sure you open and closesymmetric key as shown earlier.
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; GO SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number' , CONVERT(VARCHAR, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number' FROM dbo.Customer_data; CLOSE SYMMETRIC KEY SymmetricKey1;OUTPUT
Step 10 - Adding Records to the Table
Below is the sample code to insert values into the newly created encrypted column.
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt) VALUES (4, 'Cust4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'2539-2222-1245')))OUTPUT