SQL Server Migration and Encryption issue

I have a bit of a quandary that I could use some help with. We are in the process of migrating a SQL Server 2014 Enterprise edition to from on-premise to an AWS SQL Server 2017 Enterprise system.

The current system contains both TDE and Symmetric Key Encryption for column level encryption. This is where the fun begins. I am able to restore the database to the new 2017 instance, and TDE is working without issue after the restore. The Symmetric Key column level encryption isn’t working.

Symmetric encryption created using the following code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passwowrd'; 
CREATE CERTIFICATE AUPW WITH SUBJECT = 'AccountsUsers-Credentials'; 
CREATE SYMMETRIC KEY AccountsUsers_Credentials
    WITH ALGORITHM = AES_256 
    ENCRYPTION BY CERTIFICATE AUPW;

I then take the following steps in my attempt to restore the master key and symmetric keys to the new servers.

On the SQL Server 2014 instance:

OPEN MASTER KEY DECRYPTION BY PASSWORD = N'password';
GO
BACKUP MASTER KEY
    TO FILE = N'...MSSQLDATAmasterkey.dmk'
    ENCRYPTION BY PASSWORD = 'password'
GO
BACKUP CERTIFICATE AUPW
    TO FILE ='...MSSQLDATAAUPW.cer'  
      WITH PRIVATE KEY   
      (FILE ='...MSSQLDATAAUPW.pk', ENCRYPTION BY PASSWORD ='password')

On the SQL Server 2017 instance, after restoring the DB and ensuring that TDE is working and the DB is accessible, I attempt to run the following code:

USE NewDB
GO
RESTORE MASTER KEY 
FROM FILE = N'...MSSQLDATAMasterkey.dmk'
decryption by password = 'password'
encryption by password = 'password'
GO

CREATE CERTIFICATE AUPW FROM FILE ='...MSSQLDATAAUPW.cer'
WITH PRIVATE KEY(FILE='...MSSQLDATAAUPW.pk',
DECRYPTION BY PASSWORD='password',
ENCRYPTION BY PASSWORD='password');

However, when I run the RESTORE MASTER KEY command, I get the following error:

Msg 15329, Level 16, State 30, Line 43
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

I tried opening the master key, and get the error, yet I use the same password that I used to open it when backing up the DBMK.

Msg 15313, Level 16, State 1, Line 41
The key is not encrypted using the specified decryptor.

The force option doesn’t work either, nor does creating a new master key.

Anyone see what they heck I’m doing wrong here? The only other thing I can think to do right now is to turn off all encryption prior to the migration, but I’m not really a fan of this option.

Appreciate the help.

Answer

The reason for the error in the destination is due to service master key(at SQL Server Instance level) being different in the destination compared to source.

The database master key is encrypted by service master key at the source server. Refer to article

So, in the source, first you need to drop the encryption by service master key and add additional encryption by password , before you take the backup.

alter master key add encryption by password = 'Pass@1234'

alter master key drop encryption by service master key

Post the restore, in the destination, you have to follow below steps:

open master key decryption by password = 'Pass@1234'

alter master key add encryption by service master key

alter master key drop encryption by password = 'Pass@1234'

Refer to below articles:

Advertisement

Answer