Wednesday 28 November 2018

TDE encrypted database won't mount on SQL Server Standard after license upgrade, decrypt TDE database and migrate to SQL Server Standard

This is a continuation to my post on the TechNet forum, it’s effectively my notes on how to resolve the problem I initially asked to the community.

I had a SQL Server deployed in production which was installed using evaluation licenses. This was because at the time of install the volume license agreements were not signed off. As the evaluation licenses were due to expire I looked at the process of activating SQL Server with the customer’s volume license keys. If you have ever done this, you will know the process is basically to use the volume license media to upgrade the evaluation installation to the edition your volume license covers.
SQL Server, when installed in evaluation mode makes all the features of SQL Server Enterprise available. This caused a problem as one of the production databases had been encrypted with TDE.

TDE is a SQL Server feature only available in Enterprise edition. Once the license upgrade to Standard was complete the TDE encrypted database refused to mount as the feature was no longer licensed.

The process to resolve this is as follows (at a high level)
  • ·         Install new evaluation instance of SQL Server (which will give Enterprise).
  • ·         Export the original encryption key from the source instance.
  • ·         Create the new master key on the new instance.
  • ·         Import the original encryption key into the new instance.
  • ·         Copy the original database file and log to a new location.
  • ·         Attach the original database file and log to the new instance.
  • ·         Reconfigure application to utilise new SQL Server instance.

The above steps resolve the issue and will get the database back online. However, unless you want to run on unlicensed software, or pay to upgrade to SQL Server Enterprise, you will have to get the database running on the production SQL Server Standard instance. To do this the database will need decrypted, backed up and restored in order to remove the TDE encryption.

USE master
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

You should see the certificate used to encrypt the database listed below. In my case it was called “TDECert”, this is needed for the next command.

Use the following command to export the encryption key with the private key. In the BACKUP CERTIFICATE statement remember and list the name of your certificate which is in the output above.

USE master;
TO FILE = 'C:\Users\ryan.betts\Desktop\Cert Export\Cert.cer'                                                         
WITH PRIVATE KEY (file='C:\Users\ryan.betts\Desktop\Cert Export\CertPRVT.pvk',

Once the query has successfully completed the folder will be populated with the certificate and corresponding private key. 

Install a new SQL Server instance, I have selected Developer. The Developer edition of SQL Server is for testing purpose, but has all the features of SQL Server Enterprise.

To ensure your database mounts on the new temporary instance, ensure you install the instance with the correct database collation. I don’t believe the collation can be changed without reinstalling the instance.

 Once you have a new instance of SQL Server installed, a new Master Database Key must be created. This can be done using the following code, this should be run as a query from the new instance.

USE master;
       BY PASSWORD='SecretPassword';

Once you have the new SQL instance installed, you must use the following command to import the certificate and private key which was used to encrypt the TDE database.

USE master;
  FROM FILE = 'C:\Users\da.ryan.betts\Desktop\Cert Export\Cert.cer'
    FILE = N'C:\Users\da.ryan.betts\Desktop\Cert Export\CertPRVT.pvk',

Now copy the original database and log files to a new location. This will protect the originals in case you need to revert back to them.

Open SQL Server Management Studio, expand the instance, then expand Databases. Right click and select Attach. 

Click Add and browse for the MDF file which was copied to the new location. If the log file is available, it should automatically detect that. Click Ok.

If all has gone well the database should now mount. 

If you have moved the database to a new SQL Server instance, your application front end will need reconfigured to point to the new instance name. Although all applications are different this is typically done with an ODBC connector in Windows.

The instance name comes after the server name for example SQLServer01\Instance01.

Now that the database is mounted and accessible, we can disable the TDE encryption and begin the database encryption process. The following command can be used to do this.

USE master;
USE dbname;

Once this has completed, you can check its worked by right clicking on the database and selecting Tasks > Manage Database Encryption. If the database is decrypted the Set Database Encryption On option should be unticked. 

Now run a standard SQL backup job, copy the backup file to the original production instance and perform a restore. Reconfigure the application to point to the original instance and you have now disabled TDE and the database will run on SQL Server Standard.