column level encryption in SQL Server

Create a new database and create CustomerInfo table

USE encrypt_test;

GO
-- Create Table
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)
-- Populate Table
INSERT INTO dbo.Customer_data 
VALUES (74112,'MSSQLTips2','2147-4574-8475')
GO
INSERT INTO dbo.Customer_data 
VALUES (74113,'MSSQLTips3','4574-8475-2147')
GO
INSERT INTO dbo.Customer_data 
VALUES (74114,'MSSQLTips4','2147-8475-4574')
GO
INSERT INTO dbo.Customer_data 
VALUES (74115,'MSSQLTips5','2157-1544-8875')
GO.

column level encryption steps:

1.Create a database master key

2.Create a self-signed certificate for SQL Server

3.Configure a symmetric key for encryption

4.Encrypt the column data

Create a database master key for column level SQL Server encryption :

-- Create database Key
USE encrypt_test;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

We can use 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;

Create a self-signed certificate for Column level SQL Server encryption :

-- Create self signed certificate
USE encrypt_test;
GO
CREATE CERTIFICATE Certificate1  WITH SUBJECT = 'Protect Data';
GO

We can verify the certificate using the catalog view sys.certificates:

SELECT name CertName,certificate_id CertID,pvt_key_encryption_type_desc EncryptType,issuer_name IssuerFROM sys.certificates;

• Encrypt Type: In this column, we get a value ENCRYPTED_BY_MASTER_KEY, and it shows that SQL Server uses the database master key created in the previous step and protects this certificate
• CertName: It is the certificate name that we defined in the CREATE CERTIFICATE statement
• Issuer: We do not have a certificate authority certificate; therefore, it shows the subject value we defined in the CREATE CERTIFICATE statement.

Configure a symmetric key for column level SQL Server encryption :

We use CREATE SYMMETRIC KEY statement for it using the following parameters:

• ALGORITHM: AES_256
• ENCRYPTION BY CERTIFICATE: It should be the same certificate name that we specified earlier using CREATE CERTIFICATE statement.

-- Create symmetric Key
USE encrypt_test;
GO
CREATE SYMMETRIC KEY SymmetricKey1  WITH ALGORITHM = AES_128  ENCRYPTION BY CERTIFICATE Certificate1;
GO

Execute below command check database master key and key alrgorthem

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

Data encryption :

SQL Server encrypted column datatype should be VARBINARY.

USE encrypt_test;
GO
ALTER TABLE Customer_data  ADD Credit_card_number_encrypt varbinary(MAX) NULL
GO

Let’s encrypt the data in this newly added column,

open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier

USE encrypt_test;
GO
-- Opens the symmetric key for use
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
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Remove old column :

USE encrypt_test;
GO
ALTER TABLE Customer_data
DROP COLUMN Credit_card_number;
GO

Reading the SQL Server Encrypted Data :

reading data using the decrypt by key option. As we indicated before, make sure you open and close symmetric key as shown earlier.

USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
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 the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
Adding Records to the Table :
Make sure we can follow the varbinary format which we have encrypted the column.
USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
-- Performs the update of the record
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) );    
GO
All the read access users will see the encrypted values while they do a select on table