Error initializing transaction logs on restore

8 03 2014

An issue created through the use of Transparent Data Encryption (TDE)

To sandbox a set of databases for a specific application a named instance was created on the SQL server. The Original instance had TDE encryption enabled and all databases were encrypted. After creating the new instance, the databases to be moved were decrypted.

ALTER DATABASE <DATABASE NAME>  SET ENCRYPTION OFF

A backup of each database was then taken to be restored on to the new instance. This practice was used over a detach / re-attach method as testing was needed against the new instance in preparation to migrate the application to the new instance.
Attempting to restore the databases I received the following errors in the SQL error log, the restored databases did not become functional due to this issue.

Error: 3283, Severity: 16, State: 1.
The file “XXXXX_log” failed to initialize correctly. Examine the error logs for more details.

After investigation it was reported that this was a known error that occurred on databases that were encrypted by TDE upon restoring them to a new server or instance. Microsoft recommends applying Cumulative Update 4 to resolve the issue. After applying the latest Cumulative Update from Microsoft, Update 11, the problem still persisted.
Further research indicated that although the databases had been decrypted they would require the original certificate they were encrypted with to be restored into the new instance or server. The solution is to implement TDE on the new instance. In implementing TDE on the new instance the standard procedure was followed except with the cvaveat of the certificate. A new one was not genereated, instead the originating database servers certificate was was imported during the configuration.
It is important to note that when TDE is used, backups of keys and certificates are necessary. Store those backups in multiple locations. Failure to take appropriate precautions will result in catastrophic data loss.

/* create a backup of the service master key */
BACKUP SERVICE MASTER KEY TO FILE = 'C:\temp\service_master_key_YYYY-MM-DD.bak' ENCRYPTION BY PASSWORD = ''
GO

/* Create master key for the new instance */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO

/* Backup the new master key */
BACKUP MASTER KEY TO FILE = ' C:\temp\master_key_YYYY-MM-DD.bak' ENCRYPTION BY PASSWORD = ''
GO

/* Here we import the certificate from the original server */

CREATE CERTIFICATE
FROM FILE = ' C:\temp\certificate.cer'

/* We need to use the original private key we used to encrypt the certificate */
WITH PRIVATE KEY (FILE = ' C:\temp\private_key.pvk', DECRYPTION BY PASSWORD = '');
GO

/* Backup the certificate for the new instance or server */
BACKUP CERTIFICATE TO FILE = ' C:\temp\certificate_YYYY-MM-DD.cer' WITH PRIVATE KEY (FILE = ' C:\temp\private_key_YYYY-MM-DD.pvk', ENCRYPTION BY PASSWORD = '')
GO

After configuring TDE and using the original certificate restores from the original database server to the new instance work without any issue.





SQL Server 2008 R2 Disaster Recovery After Transparent Data Encryption (TDE) Implementation Re-Encrypt Master Key

16 07 2013

Being new to TDE I decided that my first task after encrypting all the data is to test restoring the data. First I restored the Service Master Key and the Master Key without issue. When I attempted to restoer the Certificate I recieved an error:

“Please create a master key in the database or open the master key in the session before performing this operation”
Microsoft SQL Server, Error: 15581

A quick google search revealed that I needed to issue the following command

OPEN MASTER KEY DECRYPTION BY PASSWORD='<STRONG PASSWORD>'

After accomplishing this I was able to restore the certificate to the server.

When I attempted to restore, using the GUI, the first database again I recieved the same error previously encountered. I ran the command to open the master key again to no avail.

Next I performed a SQL Command restore

USE master
RESTORE DATABASE dbname
FROM DISK = ''
WITH RECOVERY,
MOVE '' TO '',
MOVE '' TO ''
GO

This approach did restore the database and I was able to select data out of it.

This query was then ran to determine the encryption status of the restored database:

SELECT sys.databases.name, sys.dm_database_encryption_keys.encryption_state FROM sys.dm_database_encryption_keys 
JOIN sys.databases ON sys.dm_database_encryption_keys.database_id = sys.databases.database_id

The result was a little disapointing as it did not list the database as being encrypted, and I was still unable to use the GUI to perform restores!

As it turns out the Master key nust be re-encrypted by the service master key

OPEN MASTER KEY DECRYPTION BY PASSWORD='<STRONG PASSWORD>'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
CLOSE MASTER KEY
GO

Once these commands were issued I was able to restore again from the GUI and the databases restored, by either SQL or GUI, restored encrypted.

Apparently once the master key is re-encrypted after a restore it will automatically be opened as opposed to manually having to be opened. It appears the msater key should be re-encrypted prior to restoring the certificate.





SQL Server 2008 R2 – Implementing Transparent Data Encryption (TDE)

12 07 2013

I struggled through figuring out the ins and outs of implementing TDE in SQL Server 2008 R2 and decided it would be nice to post this guide to help poeple new to TDE get their databases encrypted!
It is importnat to mention more than once that by implementing TDE it is extremely important to take backups of all the keys and certificates created to help endusre that no data is lost. Failure to perform backups of these items is almost a gurantee that you will lose data, it will be rendered unrecoverable. you have been warned!

This scripts prepares your SQL Server to encrypt datatabases

USE master
GO

BACKUP SERVICE MASTER KEY TO FILE = '<path and file name to backup use .bak>' ENCRYPTION BY PASSWORD = '<RECOVERY PASSWORD>'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<STRONG PASSWORD>';
GO

BACKUP MASTER KEY TO FILE = '<path and file name to backup use .bak>' ENCRYPTION BY PASSWORD = '<RECOVERY PASSWORD>'
GO

CREATE CERTIFICATE <Certificate Name> WITH SUBJECT = '<Server Name> DEK Certificate';
GO


BACKUP CERTIFICATE <Certificate Name> TO FILE = '<path and file name to backup use .cer>' WITH PRIVATE KEY (FILE = '<path and file name to backup use .pvk>', ENCRYPTION BY PASSWORD = '<RECOVERY PASSWORD>')
GO

Now to encrypt the database

USE <database to encrypt>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <Certificate Name>;
GO
ALTER DATABASE <database to encrypt>
SET ENCRYPTION ON;
GO

And now we can validate that the database is indeed encrypted or in the process of using this command

SELECT sys.databases.name, sys.dm_database_encryption_keys.encryption_state FROM sys.dm_database_encryption_keys 
JOIN sys.databases ON sys.dm_database_encryption_keys.database_id = sys.databases.database_id

* Status of 1 indicates that the database is not encrypted
* Status of 2 indicates that the database is currently performing encryption of the database
* Status of 3 indicates that the database is encrypted

An Actual example:

USE master
GO
BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key_07-12-13.bak' ENCRYPTION BY PASSWORD = 'P@$$w0rd!!'
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd!!2';
GO
BACKUP MASTER KEY TO FILE = 'c:\master_key_07-12-13.bak' ENCRYPTION BY PASSWORD = 'P@$$w0rd!!3'
GO
CREATE CERTIFICATE encCer WITH SUBJECT = 'Database DEK Certificate';
GO
BACKUP CERTIFICATE encCer TO FILE = 'c:\enCer_certificate_07-12-13.cer' WITH PRIVATE KEY (FILE = 'c:\private_key_07-12-13.pvk', ENCRYPTION BY PASSWORD = 'P@$$w0rd!!4')
GO
USE test
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE encCer;
GO
ALTER DATABASE test
SET ENCRYPTION ON;
GO

Helpful Tips

1. By implementing TDE and not following these guidelines will result in data loss.

2. Give meaningful names to your backup files.

3. Use different complex passwords for each key, certificate, and recovery of these items.

4. Store all your passwords and backup files in a secure location

5. Keep multiple copies of the passwords and backup files.

6. I keep all my passwords in an encrypted file on my computer, a backup of this on tape, and on a printed out hardcopy located in the safe!

7. keep a copy of the backed up keys and certificate on the local server, on a network share, and on tape!

8. No this is not overkill!