Column-level SQL Server encryption with SQL Server Always On Availability Groups

It is the 30th article in the SQL Server Always On Availability Groups series and explores column-level SQL Server encryption with AG groups.

Introduction

We might have sensitive data in our SQL database such as customers’ credit card details, bank account details,
social security numbers, and medical history. The sensitive data should be prevented from unauthorized access. We
can secure data at multiple levels, for example, physical data security, user principals and securable, auditing.
Data encryption is also a useful terminology in protecting user data. First, you should understand your data and
classify the data at various levels. You can refer to the article SQL data classification – Add sensitivity classification in SQL Server 2019 for data classification.

Suppose you have a customer table holding the customer credit card number. You want to encrypt only the sensitive
column, i.e. credit card number in the table. In the article, An overview of the column level SQL Server encryption, I explored the column level encryption using the symmetric keys in a standalone
SQL Server.

In this article, we will explore column level encryption for the database part of the SQL Server Always On
Availability Group.

Environment details

In this article, I use the two-node availability group in synchronous commit mode.

SQL Server Always On Availability Group

  • Primary Replica: SQLNode1INST1
  • Secondary Replica: SQLNode2INST1
  • Availability Group database: [MyNewDB]
  • Failover Mode: Automatic

AG dashboard

Column-level SQL Server encryption with SQL Server Always On Availability Groups

To configure the column-level encryption in an availability group, create the following table in the [MyNewDB]
database on the primary replica instance. Insert a few sample records as well.

Any user with the read-only permissions to this [Customerinfo] can view the bank account for the customers.

Sample data

Let’s implement the column level SQL Server encryption for the [BankACNumber] column.

Create a master key

Create the master key on the primary replica in SQL Server Always On Availability Group database. It requires a
password for the encryption.

Verify the existence of the master key using the sys.symmetric_keys. It shows the
##MS_DatabaseMasterKey## in the query output.

Create a master key

Create a self-signed certificate on the primary replica of SQL Server Always On Availability Group

In the next step, we create a self-signed certificate for the primary replica availability group database. The
database master key protects the self-signed certificate. Specify a subject to define the metadata of the
certificate. It should not be more than 64 characters. You can read more about it over here, SQL Server certificates.

Verify the certificate using the sys.certificates.

In the query output, you can verify the certificate name, Issuer (a subject that we specified in the CREATE
CERTIFICATE statement).

Create a self-signed certificate

Create a symmetric key on the primary replica

Now, we create a symmetric key on the primary replica database using the self-signed certificate, and we created
earlier. The self-signed certificate encrypts the symmetric key. The below query uses the AES 256 algorithm.

Encrypt the [BankACNumber] column in the [CustomerInfo] table

At this step, we are ready for column-level SQL Server encryption on the primary replica database in SQL Server
Always On Availability Group.

Add a new column in the [Customerinfo] table of VARBINARY data type

To do this, add a new column of VARBINARY(max) data type because the Encrypted column must have datatype VARBINARY (max).

Apply column-level SQL Server encryption for the newly created column

Open the symmetric key

First, open the symmetric key we created earlier using the self-signed certificate.

Encrypt the [BankACNumber_Encrypt] column in the [CustomerInfo] table

In this step, we use the EncryptByKey function to encrypt data using the symmetric key. This function takes input as the symmetric key we created earlier.

We inserted 3 rows in our sample table. In the update command output, it shows 3 rows affected. If you have a table
with a large number of rows, it may take a while to apply the column-level encryption.

update command

Close the symmetric key

Once the column-level encryption completes, we must close the symmetric key using the CLOSE SYMMETRIC KEY statement.

Data Validation

Now, run a select statement on the CustomerInfo table on the primary replica. It returns the four columns. We can
see the encrypted and unencrypted data in this table.

SQL Server encryption: View encrypted data

It does not make sense to keep the unencrypted data column. We can drop the column using the below query.

In the primary replica, we have encrypted data, as shown below.

Remove unencrypted data column

Read data from the secondary replica in a SQL Server Always On Availability Group

We can read data from the synchronized secondary replica in a SQL Server Always On Availability Group. To verify it,
right-click on the availability group and check the value for the Readable Secondary column. Its value should be
“Yes” so that the user can connect to the secondary and run the select statements.

Read data from the secondary replica

Now, run the select statement on the readable secondary replica. It shows the encrypted data from the secondary
replica as well as shown below.

readable secondary replica

Perform an AG failover and verify the column-level data encryption

It is always advisable to perform the availability group failover and check the encrypted data from the new primary.

As shown below, the new primary replica is SQLNode2Inst1 after the failover.

Perform an AG failover

AG dashboard is pleasing on the new replica SQLNode2INST1 after the failover as well.

New replica

Check the data in the [CustomerInfo] table, and it shows the encrypted data in the new primary replica as well.

View data from new primary replica

Decrypt data in the new primary replica

You might want to decrypt the data essentially at the application end. To read the actual data, we need to apply the
reverse mechanism, i.e. decrypt column data.

In the new primary replica, do the following tasks.

  • Open the symmetric key in the new primary replica SQLNode2INST1
  • Earlier to encrypt data, we used the EncryptByKey() function. Now, to read data, we use the DecryptByKey() function

We get an error message when we access the decrypt data, as shown below.

SQL Server encryption: Decrypt data

Let’s connect to the new secondary replica (earlier primary replica – SQLNode1INST1) and rerun the above query.
Here, it works fine. We get the decrypted data, as shown below.

Connect to the new secondary replica

To resolve the issue on the new primary replica, let’s perform a failover again. After the failover, we have the
SQLNode1INST1 as the primary replica. It is the replica where we configured the column-level SQL Server encryption.

Now, take the backup of the master key and encrypt it with a password.

Now, again perform a failover and promote the SQLNode2INST1 as the new primary replica. In this replica, restore
the service master key. You can either copy the backup file in the new primary replica or access the file using the
network path. In the restore key statement, specify the encryption password the same as we used in the backup master
key statement.

Now, you can decrypt data in the new primary replica SQLNode2INST1.

decrypt data in the new primary replica

Conclusion

In this article, we did the integration of column-level SQL Server encryption for the AG database in a SQL Server
Always On Availability Group. You might use the encryption for the AG database, and this article helps you
implement, encrypt and decrypt data before and after failover as well.

Table of contents

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Author: admin

Leave a Reply

Your email address will not be published.