This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
Problem
I was recently helping a customer with an issue where he was using PolyBase external objects in an AlwaysOn Availability Database. After setting up a readable secondary replica, when trying to query an external table on the readable secondary, the query would return the following error:
Msg 15581, Level 16, State 7, Line 11
Please create a master key in the database or open the master key in the session before performing this operation.
Why does this happen?
As described in the CREATE MASTER KEY article, the database master key is encrypted by the service master key of the instance where the database was present when the database master key was created. The service master key is used to decrypt the database master key. When the database is active on another instance (e.g. a different Availability Replica), the master key is not automatically decrypted because the service master key of the instance is different.
Why does that matter for PolyBase?
This encryption hierarchy is relevant because access to the PolyBase external data source is controlled through a database scoped credential, which is encrypted by the database master key. If the master key isn't open, then the database scoped credential (and anything dependent on it) is inaccessible. One can execute OPEN MASTER KEY to resolve this, but if there's any failover to the secondary replica and then back to original primary replica, the master key must be opened again. This can be very disruptive to achieving high availability of the PolyBase objects in the availability database.
The Solution
This behavior is observed in availability databases that utilize encryption; other examples are TDE Encryption or Stretch Database. The good news is that the following stored procedure can be utilized to automatically open database master keys that were encrypted with a service master key from another SQL Server instance.
sp_control_dbmasterkey_password (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-control-dbmasterkey-password-transact-sql?view=sql-server-ver15
Remarks
When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails, SQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.
Therefore, the solution is to execute sp_control_dbmasterkey_password on all of the availability replicas that may host a readable secondary or be a failover target for the availability database. This will create the credential needed to decrypt the database master key of the availability database and avoid the error.
NOTE: Usage of sp_control_dbmasterkey_password doesn't lessen the security of the environment. To execute the stored procedure requires CONTROL permission on the database. The database master key is still protected by the same password on the replica host where it was originally created and on the other replicas where sp_control_dbmasterkey_password is executed. sp_control_dbmasterkey_password just provides a mechanism for which to encrypt the database master key by a service master key other than the service master key of the instance where the database master key was originally created.