title: SQL Server Migration and Encryption issue
body: <p>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.  </p>
  
  <p>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.  </p>
  
  <p>Symmetric encryption created using the following code:</p>
  
  <pre><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;
  </code></pre>
  
  <p>I then take the following steps in my attempt to restore the master key and symmetric keys to the new servers.</p>
  
  <p>On the SQL Server 2014 instance:</p>
  
  <pre><code>OPEN MASTER KEY DECRYPTION BY PASSWORD = N'password';
  GO
  BACKUP MASTER KEY
      TO FILE = N'...\MSSQL\DATA\masterkey.dmk'
      ENCRYPTION BY PASSWORD = 'password'
  GO
  BACKUP CERTIFICATE AUPW
      TO FILE ='...\MSSQL\DATA\AUPW.cer'  
        WITH PRIVATE KEY   
        (FILE ='...\MSSQL\DATA\AUPW.pk', ENCRYPTION BY PASSWORD ='password')   
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>USE NewDB
  GO
  RESTORE MASTER KEY 
  FROM FILE = N'...\MSSQL\DATA\Masterkey.dmk'
  decryption by password = 'password'
  encryption by password = 'password'
  GO
  
  CREATE CERTIFICATE AUPW FROM FILE ='...\MSSQL\DATA\AUPW.cer'
  WITH PRIVATE KEY(FILE='...\MSSQL\DATA\AUPW.pk',
  DECRYPTION BY PASSWORD='password',
  ENCRYPTION BY PASSWORD='password');
  </code></pre>
  
  <p>However, when I run the RESTORE MASTER KEY command, I get the following error:</p>
  
  <blockquote>
    <p>Msg 15329, Level 16, State 30, Line 43<br>
    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.</p>
  </blockquote>
  
  <p>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. </p>
  
  <blockquote>
    <p>Msg 15313, Level 16, State 1, Line 41<br>
    The key is not encrypted using the specified decryptor.</p>
  </blockquote>
  
  <p>The force option doesn't work either, nor does creating a new master key.  </p>
  
  <p>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.</p>
  
  <p>Appreciate the help.</p>