Issue with Encryption in Log Shipping
We are working with a SQL Server 2008 R2 database that employs encryption and is set up for log shipping to a secondary server for backup. However, we are encountering issues with decryption on the secondary server, which seems to lack a master key.
Questions:
- What steps should be followed to configure encryption on the secondary server?
- Is it necessary to back up the master key from the primary server and restore it on the secondary server, or can we create a new master key directly on the secondary server?
Error Encountered:
We attempted the following command on the secondary server but received an error:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Error Messages:
- Msg 15151, Level 16: Unable to find the symmetric key ‘master key’; it may not exist or you may lack permissions.
- Msg 15151, Level 16: Unable to find the symmetric key ‘master key’; it may not exist or you may lack permissions.
The script was executed by a user with dbo admin privileges.
Hmm, interesting challenge you’re facing, Sam. I’m curious, have you considered exploring if any other permissions might be impacting the master key access? Also, what version are your servers on? Sometimes there are version-specific quirks that could mess things up. Let us know how it goes!
sounds like a common headache with encryption. you can back up the master key from the primary server and import it into the secondary using a tool like PowerShell or T-SQL scripts if you haven’t already. make sure you use the right encryption method to avoid compatibility hitches. good luck!
hey Sam, if the master key isn’t there on the secondary, it’ll need to be restored from the primary. to do this, back up the master key on the primary with ENCRYPTION BY PASSWORD, then restore it on the secondary using the same password. creating a new one won’t work for existing encrypted db content.
In my experience working with SQL Server log shipping, it’s crucial to have the master key backed up and restored on the secondary server to maintain encryption consistency. The encryption keys need to match between the primary and secondary servers for decryption to work correctly. After restoring the master key, ensure that permissions are correctly set for the login executing the commands. This often resolves issues with accessing encrypted data on secondary servers in log shipping setups.
In a scenario like this, you indeed need to have the same master key on both servers if encrypted databases are involved. From personal experience, directly creating a new master key on the secondary server will not work as it won’t decrypt the existing data copied over from the primary server. If you’ve backed up your master key from the primary server, be sure to restore it using the same password on the secondary server. Additionally, confirming that your service account has the necessary permissions to manage encryption keys can prevent access issues.