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.
- Primary Replica: SQLNode1\INST1
- Secondary Replica: SQLNode2\INST1
- Availability Group database: [MyNewDB]
- Failover Mode: Automatic
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.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE dbo.Customerinfo ( Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName VARCHAR(30) NOT NULL, BankACNumber VARCHAR(20) NOT NULL ); Insert into Customerinfo (CustomerName,BankACNumber) values('Raj','1X45694511') Insert into Customerinfo (CustomerName,BankACNumber) values('Mohan','2X69863214') Insert into Customerinfo (CustomerName,BankACNumber) values('Brij','3X29736941') |
Any user with the read-only permissions to this [Customerinfo] can view the bank account for the customers.
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.
|
1 2 3 |
USE MyNewDB; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Demo@123'; |
Verify the existence of the master key using the sys.symmetric_keys. It shows the ##MS_DatabaseMasterKey## in the query output.
|
1 2 3 4 5 6 |
USE MyNewDB; GO SELECT name KeyName, key_length KeyLength, algorithm_desc KeyAlgorithm FROM sys.symmetric_keys; |
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.
|
1 2 3 |
CREATE CERTIFICATE MySQLShackDemo WITH SUBJECT = 'Column Level Encryption for SQL Availability Group'; GO |
Verify the certificate using the sys.certificates.
|
1 2 3 4 |
SELECT name CertName, pvt_key_encryption_type_desc EncryptType, issuer_name Issuer FROM 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 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.
|
1 2 |
CREATE SYMMETRIC KEY SQLShackDemo_ColumnEncryption WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MySQLShackDemo; |
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).
|
1 2 |
ALTER TABLE Customerinfo ADD BankACNumber_Encrypt 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.
|
1 2 |
OPEN SYMMETRIC KEY SQLShackDemo_ColumnEncryption DECRYPTION BY CERTIFICATE MySQLShackDemo; |
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.
|
1 2 3 |
UPDATE Customerinfo SET BankACNumber_Encrypt = EncryptByKey (Key_GUID('SQLShackDemo_ColumnEncryption'), BankACNumber) FROM Customerinfo; |
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.
Close the symmetric key
Once the column-level encryption completes, we must close the symmetric key using the CLOSE SYMMETRIC KEY statement.
|
1 |
CLOSE SYMMETRIC KEY SQLShackDemo_ColumnEncryption; |
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.
It does not make sense to keep the unencrypted data column. We can drop the column using the below query.
|
1 2 3 4 5 |
Use MyNewDB Go ALTER TABLE Customerinfo DROP COLUMN BankACNumber; GO |
In the primary replica, we have encrypted data, as shown below.
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.
Now, run the select statement on the readable secondary replica. It shows the encrypted data from the secondary replica as well as shown below.
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.
AG dashboard is pleasing on the new replica SQLNode2\INST1 after the failover as well.
Check the data in the [CustomerInfo] table, and it shows the encrypted data in the new primary replica as well.
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
|
1 2 3 4 5 |
OPEN SYMMETRIC KEY SQLShackDemo_ColumnEncryption DECRYPTION BY CERTIFICATE MySQLShackDemo; SELECT ID, CustomerName,BankACNumber_encrypt AS 'Encrypted data', CONVERT(varchar, DecryptByKey(BankACNumber_encrypt)) AS 'Decrypted Bank account number' FROM CustomerInfo; |
We get an error message when we access the decrypt data, as shown below.
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.
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.
|
1 2 3 |
BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQL\Primary_SQLNode1_Service_master_key' ENCRYPTION BY PASSWORD = 'Demo@123'; |
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.
|
1 2 3 4 |
RESTORE SERVICE MASTER KEY FROM FILE = '\\SQLNode1\C$\SQL\Primary_SQLNode1_Service_master_key' DECRYPTION BY PASSWORD = 'Demo@123'; GO |
Now, you can decrypt data in the new primary replica SQLNode2\INST1.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023