Environment

-> Always ON Availability group configuration below,

-> JBDB Database is already created and added to Always ON Availability group.

-> Object Table1 contains column Customer_ID and Customer_Password. Column Customer_Password will be encrypted to secure the password.

-> Column level encryption will be enabled on object Table1 in Database JBDB using below scripts,

USE JBDB;
GO
                 
CREATE TABLE Table1
(Col1        INT IDENTITY (1,1) PRIMARY KEY, 
Customer_ID     NVARCHAR(30) NOT NULL, 
Customer_Password NVARCHAR(100) NOT NULL
)
GO

Insert into Table1 values ('ABC123','Password')
Insert into Table1 values ('ABC456','Password1')
Insert into Table1 values ('ABC789','Password2')

SELECT * FROM TABLE1

-> Create a database master key

USE JBDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passw0rd';

-> Execute below query to check the master key,

-> Create a self-signed certificate for SQL Server

USE JBDB;
GO
CREATE CERTIFICATE Table1_Certificate WITH SUBJECT = 'Table1 Column Level Encryption';
GO

-> Configure a symmetric key for encryption

CREATE SYMMETRIC KEY Table1_Sym_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Table1_Certificate;

-> Encrypt Column Customer_PWD

ALTER TABLE Table1
 ADD Customer_PWD varbinary(MAX)

OPEN SYMMETRIC KEY Table1_Sym_Key
 DECRYPTION BY CERTIFICATE Table1_Certificate;

UPDATE Table1
 SET Customer_PWD = EncryptByKey (Key_GUID('Table1_Sym_Key'), Customer_Password)
 FROM Table1;

CLOSE SYMMETRIC KEY Table1_Sym_Key;

select * from  Table1

-> Remove column Customer Password,

ALTER TABLE TABLE1 DROP COLUMN Customer_Password;
GO
SELECT * FROM TABLE1

-> Below query can be used to Decrypt the encrypted column,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;

CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> This requires quiet a planning when performing on a production environment that has many rows. The demo above just has 3 rows, so everything completed fine.

-> Everything works fine till now without any issues. A failover happens wherein the Availability group fails over from JBSAG1 to JBSAG2.

-> JBSAG2 is PRIMARY now. Executing below query to fetch the decrypted data,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;

CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> Below error is encountered,

Msg 15581, Level 16, State 7, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.


Msg 15315, Level 16, State 1, Line 4
The key 'Table1_Sym_Key' is not open. Please open the key before using it.

-> To solve this problem execute below query on JBSAG2,

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Passw0rd' -- Password should be same as what was used with query "CREATE MASTER KEY ENCRYPTION BY PASSWORD"
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

-> Execute below query to fetch the decrypted data,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;
CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> This issue will reoccur when Availability group fails over from JBSAG2 to JBSAG1 and vice versa. Executing below query on current primary replica solves the issue,

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Passw0rd' -- Password should be same as what was used with query "CREATE MASTER KEY ENCRYPTION BY PASSWORD"
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

-> But the problem is, this is a manual step and it will be very difficult to do it manually during every failover. Hence below action should be performed.

-> Execute below query to backup the Service Master Key on primary replica. In my case it is JBSAG1,

BACKUP SERVICE MASTER KEY
 TO FILE = 'C:\temp\JBSAG1_Service_master_key'
ENCRYPTION BY PASSWORD = 'Passw0rd';

-> Copy the backup of Service Master Key from JBSAG1 to JBSAG2 in location C:\temp.

-> Manually failover availability group from JBSAG1 to JBSAG2 and execute below query,

RESTORE SERVICE MASTER KEY
FROM FILE = 'C:\temp\JBSAG1_Service_master_key'
DECRYPTION BY PASSWORD = 'Passw0rd';
GO

-> Once above tasks are completed. Any further failover and failback will not have any issues querying the encrypted data.

-> Check this article if you are encountering this issue while restoring a database backup on a different SQL Server instance.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.