An overview of the column level SQL Server encryption (2024)

This article gives an overview of column level SQL Server encryption using examples.

Introduction

Data security is a critical task for any organization, especially if you store customer personal data such as Customer contact number, email address, social security number, bank and credit card numbers. Our main goal is to protect unauthorized access to data within and outside the organization. To achieve this, we start by providing access to relevant persons. We still have a chance that these authorized persons can also misuse the data; therefore, SQL Server provides encryption solutions. We can use these encryptions and protect the data.

It is a crucial aspect in classifying the data based on the information type and sensitivity. For example, we might have customer DOB in a column and depending upon the requirement, and we should classify it as confidential, highly confidential. You can read more about in the article SQL data classification – Add sensitivity classification in SQL Server 2019.

We have many encryptions available in SQL Server such as Transparent Data Encryption (TDE), Always Encrypted, Static data masking and Dynamic Data Masking. In this article, we will explore column level SQL Server encryption using symmetric keys.

Environment set up

Let’s prepare the environment for this article.

• Create a new database and create CustomerInfo table

  1 2 3 4 5 6 7 8 9 10 11

  CREATE DATABASE CustomerData; Go USE CustomerData; GO CREATE TABLE CustomerData.dbo.CustomerInfo (CustIDINT PRIMARY KEY, CustName VARCHAR(30) NOT NULL, BankACCNumber VARCHAR(10) NOT NULL ); GO

• Insert sample data into CustomerInfo table

  1 2 3 4 5 6

  Insert into CustomerData.dbo.CustomerInfo (CustID,CustName,BankACCNumber) Select 1,'Rajendra',11111111 UNION ALL Select 2, 'Manoj',22222222 UNION ALL Select 3, 'Shyam',33333333 UNION ALL Select 4,'Aksh*ta',44444444 UNION ALL Select 5, 'Kashish',55555555

• View the records in CustomerInfo table

  

We use the following steps for column level encryption:

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
5. Query and verify the encryption

We will first use these steps and later explain the overall process using Encryption Hierarchy in SQL Server using the following image (Reference – Microsoft Docs):

Create a database master key for column level SQL Server encryption

In this first step, we define a database master key and provide a password to protect it. It is a symmetric key for protecting the private keys and asymmetric keys. In the above diagram, we can see that a service master key protects this database master key. SQL Server creates this service master key during the installation process.

We use CREATE MASTER KEY statement for creating a database master key:

We can use sys.symmetric_keys catalog view to verify the existence of this database master key in SQL Server encryption:

In the output, we can notice that it creates a ##MS_DatabaseMasterKey## with key algorithm AES_256. SQL Server automatically chooses this key algorithm and key length:

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

In this step, we create a self-signed certificate using the CREATE CERTIFICATE statement. You might have seen that an organization receives a certificate from a certification authority and incorporates into their infrastructures. In SQL Server, we can use a self-signed certificate without using a certification authority certificate.

Execute the following query for creating a certificate:

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

In the output, we can note the following fields:

• 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

Optionally, we can use ENCRYPTION BY PASSWORD and EXPIRY_DATE parameters in the CREATE CERTIFICATE; however, we will skip it in this article.

Configure a symmetric key for column level SQL Server encryption

In this step, we will define a symmetric key that you can see in the encryption hierarchy as well. The symmetric key uses a single key for encryption and decryption as well. In the image shared above, we can see the symmetric key on top of the data. It is recommended to use the symmetric key for data encryption since we get excellent performance in it. For column encryption, we use a multi-level approach, and it gives the benefit of the performance of the symmetric key and security of the asymmetric key.

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

Once we have created this symmetric key, check the existing keys using catalog view for column level SQL Server Encryption as checked earlier:

We can see two key entries now as it includes both the database master key and the symmetric key:

We have created the required encryption keys in this demo. It has the following setup that you can see in the image shown above as well:

• SQL Server installation creates a Service Master Key (SMK), and Windows operating system Data Protection API (DPAPI) protects this key
• This Service Master Key (SMK) protects the database master key (DMK)
• A database master key (DMK) protects the self-signed certificate
• This certificate protects the Symmetric key

Data encryption

SQL Server encrypted column datatype should be VARBINARY. In our CustomerData table, the BankACCNumber column data type is Varchar(10). Let’s add a new column of VARBINARY(max) datatype using the ALTER TABLE statement specified below:

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

• In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier

  1 2	OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY CERTIFICATE Certificate_test;

• In the same session, use the following UPDATE statement. It uses EncryptByKey function and uses the symmetric function for encrypting the BankACCNumber column and updates the values in the newly created BankACCNumber_encrypt column

  1 2 3 4

  UPDATE CustomerData.dbo.CustomerInfo SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), BankACCNumber) FROM CustomerData.dbo.CustomerInfo; GO

  See Also

  When You Should Use TDE vs Always Encrypted

• Close the symmetric key using the CLOSE SYMMETRIC KEY statement. If we do not close the key, it remains open until the session is terminated

  1 2	CLOSE SYMMETRIC KEY SymKey_test; GO

• Verify the records in the CustomerInfo table

We can see the encrypted records in the newly added column. If the user has access to this table also, he cannot understand the data without decrypting it:

Let’s remove the old column as well:

Now, we have only an encrypted value for the bank account number:

Decrypt column level SQL Server encryption data

We need to execute the following commands for decrypting column level encrypted data:

• In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier

  1 2	OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY CERTIFICATE Certificate_test;

• Use the SELECT statement and decrypt encrypted data using the DecryptByKey() function

  1 2 3

  SELECT CustID, CustName,BankACCNumber_encrypt AS 'Encrypted data', CONVERT(varchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number' FROM CustomerData.dbo.CustomerInfo;

We can see both encrypted and decrypted data in the following screenshot:

Permissions required for decrypting data

A user with the read permission cannot decrypt data using the symmetric key. Let’s simulate the issue. For this, we will create a user and provide db_datareader permissions on CustomerData database:

Now connect to SSMS using SQLShack user and execute the query to select the record with decrypting BankACCNumber_encrypt column:

In the output message, we get the message that the symmetric key does not exist, or the user does not have permission to use it:

Click on the results, and we get the NULL values in the decrypted column, as shown below:

We can provide permissions to the Symmetric key and Certificate:

• Symmetric key permission: GRANT VIEW DEFINITION
• Certificate permission: GRANT VIEW DEFINITION and GRANT CONTROL permissions

Execute these scripts with from a user account with admin privileges:

Now, go back and re-execute the SELECT statement:

Conclusion

In this article, we explored column level SQL Server encryption using the symmetric key. We can use the same key for encrypting other table columns as well. You should explore the encryption and decryption mechanism; however, you should consider the requirements first and then consider the appropriate encryption mechanism as per your need.