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
GO
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;
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Users\ryan.betts\Desktop\Cert
Export\Cert.cer'
WITH PRIVATE KEY (file='C:\Users\ryan.betts\Desktop\Cert
Export\CertPRVT.pvk',
ENCRYPTION BY PASSWORD='SecretPassword');
|
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.
USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='SecretPassword';
GO
|
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;
GO
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\Users\da.ryan.betts\Desktop\Cert
Export\Cert.cer'
WITH PRIVATE KEY
(
FILE = N'C:\Users\da.ryan.betts\Desktop\Cert
Export\CertPRVT.pvk',
DECRYPTION BY PASSWORD = 'SecretPassword'
);
GO
|
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;
GO
ALTER DATABASE dbname SET ENCRYPTION OFF;
GO
USE dbname;
GO
DROP DATABASE ENCRYPTION KEY;
GO
|
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.
