SQL Server – Risks of Setting ‘Encryption Enabled’ to FALSE

log-shippingmigrationsql servertransparent-data-encryption

I'm migrating a database from SQL Server 2012 to SQL Server 2014 on the same server.

I'm configuring LOG SHIPPING on a database that is apparently encrypted:

enter image description here

When setting the log shipping I got this message (when it started to restore the database from a backup):

enter image description here

I'm the new DBA here and there's no report of master keys, encrypted keys etc.

I'm setting log shipping from SQL2012 to SQL2014 (with norecovery). What could go wrong if I set this database to Encryption enabled - false and try to restore the database with the log shipping wizard?

There are some questions about this problem , like This one, but all of them say I need the password.

I Just found this certificate on master > security > certificates on the primary server (2012):

enter image description here

And there are some code samples over the Interet but I really can't trust them (I don't think it's too simple):

`USE MASTER
GO
ALTER DATABASE DatabaseName
SET ENCRYPTION OFF
GO
USE DatabaseName
GO
DROP DATABASE ENCRYPTION KEY
GO`

Edit1:

In this question Dylon says:

If you are receiving the thumbprint error the certificate was not created properly from the Source Server's Cert/Key backups.

I'm confused about this topic and all I want is to disable encryption, restore the database on the other server to log shipping, and some day, activate encryption.

Edit2:

On Database > tasks > manage database encryption there are some options too:

enter image description here

Disabling this, I would be able to backup the database again, and restore it on the new instance?

Best Answer

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE.

Rather set up log shipping properly in the presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the TDE certificate from the original server and then import it on the standby server, making sure it is encrypted with the master database master key and the database master master key is in turn encrypted with the service master key. After that, if done correctly, restore operations should succeed as the required TDE certificate is present.

To copy the certificate: The certificate is in the master database of the primary instance. You need to run:

BACKUP CERTIFICATE TDE_CERT 
 TO FILE <where to save cert> 
 WITH PRIVATE KEY (FILE = <where to save private key>, 
  ENCRYPTION BY PASSWORD = <password for the private key file>);

Then copy both cert and private key files to the secondary and restore it in master using:

 CREATE CERTIFICATE TDE_CERT 
  FROM FILE = <your copied cert file> 
  WITH PRIVATE KEY (FILE = <your copied private key file>,
   DECRYPTION BY PASSWORD = <password that protects private key file>);

TDE_CERT in master on the secondary needs to be encrypted with master database master key, which will likely need to be created.

Related Question