Rajendra Gupta

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

October 12, 2020 by

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: SQLNode1\INST1
  • Secondary Replica: SQLNode2\INST1
  • 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 SQLNode2\Inst1 after the failover.

Perform an AG failover

AG dashboard is pleasing on the new replica SQLNode2\INST1 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 SQLNode2\INST1
  • 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 – SQLNode1\INST1) 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 SQLNode1\INST1 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 SQLNode2\INST1 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 SQLNode2\INST1.

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

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views