CODEWITHSUNDEEP
sql-server-2012
user2073209
user2073209

Reputation: 21

sql2012 availability group readonly replica and database certificate use issue

I have trying to research a problem I am running into with read intent secondary replicas in my sql2012 availability group and can't seem to find anything yet, I did see some things about instance level TDE but not for database level cert.

Overview, I have a database that has a table with an encrypted column that uses a database cert for the encryption/decryption. The setup of the availability group and creating the database and replicas all seem to have gone well (three separate nodes). However when I try to connect via the Listener with a read intent connection to the 2nd node's read only secondary replica, and query this table with the encrypted column the data returned is all NULL. If I open the master key with the password first then issue the query I have the data returned decrypted as normal but only lasts for that session connection. I should mention that everything else other than this encrypt issue with the primary and secondary replicas work fine. And if I query with readWrite to the primary database the data retrieves just fine. Please let me know if there is other info you'd like to know about this setup.

Does anyone know if this is now something with querying as read intent to a readonly replica that each session connection must open the master key first? Or did I set up something incorrect and what I can do to fix this?

the open master key command:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password';

query used to view encrypted column:

SELECT CAST(DECRYPTBYCERT(CERT_ID('CertName'), ColName) AS VARCHAR(200)) aliasColName
  FROM [DBName].[dbo].[tableName]

Below is the sql used to create the cert on the primary:

USE DBName
DROP CERTIFICATE CertName;
GO

USE DBName
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
WHERE [name] LIKE '%DatabaseMasterKey%')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
END
GO 

USE DBName
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

USE DBName
CREATE CERTIFICATE CertName FROM FILE = 'C:\Cert.cer'
  WITH PRIVATE KEY ( FILE = 'C:\CertKey.pvk' , 
  DECRYPTION BY PASSWORD = 'password' );
GO

CLOSE MASTER KEY
GO

Thanks all!

Upvotes: 1

Views: 832

Answers (1)

user2073209
user2073209

Reputation: 21

After going through the process I found the solution and will post it here in case someone else runs into this same thing.

Since the DB Master Key is encrypted using the Master database's Service Master Key I had to restore Force a backup of the primary server's Service Master Key to all secondary servers in order to not have to explicitly 'open' the DB Master Key at each session. So not only does the Master Key need to go but so does the Service Master Key in order to keep the encryption hierarchy identical for the Read Only Routing to work properly. Below is the code.

First backup the Service Master Key from the primary server and copy it to all secondary servers.

BACKUP SERVICE MASTER KEY TO FILE = 'C:\service_master_key' 
  ENCRYPTION BY PASSWORD = 'password';

Then restore it to all secondary servers with the FORCE option.

RESTORE SERVICE MASTER KEY FROM FILE = 'C:\service_master_key' 
  DECRYPTION BY PASSWORD = 'password' FORCE

Thanks

Upvotes: 1

Related Questions