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.

Advertisements




Easy Backup Script

11 09 2013

Here is a handy backup script for those that do not like to type much. It automatically enumerates the database names and creates a backup of that database in a user set location. Make sure the @path variable is set!


/* Backup all database except for tempdb
Created By: Christopher Provolt
Date: 11 September 2013 */

DECLARE @database VARCHAR(255)
DECLARE @cmd varchar(1000)
DECLARE @path varchar(255)

--set @path variable to the root of where backup files will be created. Ensure following backslash is used.

SET @path = 'E:\Backup\'

DECLARE DatabaseCursor

CURSOR FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('tempdb') AND state = 0

OPEN DataBaseCursor FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0

BEGIN  select 'Database: ', @database

SET @cmd = 'BACKUP DATABASE ['+@database+'] TO DISK = N'''+@path+@database+'.bak'''
EXEC(@cmd)

FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor





SQL Server Audit action_id List

2 08 2013

While reviewing some audit logs genereated in SQL Server 2008 auditing I came across a a few action_id values that were not completely obvious to me. I began the great google search in hopes of finding a table that mapped out this information and was somewhat unsuccessful. What I did manage to put together was a a query to determine what they all meant.

Select DISTINCT action_id,name,class_desc,parent_class_desc from sys.dm_audit_actions

The above query will list all the action_id’s and what they actually mean in the world of SQL.  In order to save time for everyone the trimmed down list is below.

action_id name
ACDO DATABASE_OBJECT_ACCESS_GROUP
ACO SCHEMA_OBJECT_ACCESS_GROUP
ADBO BULK ADMIN
ADDP DATABASE_ROLE_MEMBER_CHANGE_GROUP
ADSP SERVER_ROLE_MEMBER_CHANGE_GROUP
AL ALTER
ALCN ALTER CONNECTION
ALRS ALTER RESOURCES
ALSS ALTER SERVER STATE
ALST ALTER SETTINGS
ALTR ALTER TRACE
APRL ADD MEMBER
AS ACCESS
AUSC AUDIT SESSION CHANGED
AUSF AUDIT SHUTDOWN ON FAILURE
AUTH AUTHENTICATE
BA BACKUP
BAL BACKUP LOG
BRDB BACKUP_RESTORE_GROUP
C2OF TRACE AUDIT C2OFF
C2ON TRACE AUDIT C2ON
CCLG CHANGE LOGIN CREDENTIAL
CMLG CREDENTIAL MAP TO LOGIN
CNAU AUDIT_CHANGE_GROUP
CO CONNECT
CP CHECKPOINT
CR CREATE
D DENY
DBCC DBCC
DBCG DBCC_GROUP
DL DELETE
DPRL DROP MEMBER
DR DROP
DWC DENY WITH CASCADE
EX EXECUTE
FT FULLTEXT
FTG FULLTEXT_GROUP
G GRANT
GRDB DATABASE_PERMISSION_CHANGE_GROUP
GRDO DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
GRO SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
GRSO SERVER_OBJECT_PERMISSION_CHANGE_GROUP
GRSV SERVER_PERMISSION_CHANGE_GROUP
GWG GRANT WITH GRANT
IMDP DATABASE_PRINCIPAL_IMPERSONATION_GROUP
IMP IMPERSONATE
IMSP SERVER_PRINCIPAL_IMPERSONATION_GROUP
IN INSERT
LGB BROKER LOGIN
LGBG BROKER_LOGIN_GROUP
LGDA DISABLE
LGDB CHANGE DEFAULT DATABASE
LGEA ENABLE
LGFL FAILED_LOGIN_GROUP
LGIF LOGIN FAILED
LGIS LOGIN SUCCEEDED
LGLG CHANGE DEFAULT LANGUAGE
LGM DATABASE MIRRORING LOGIN
LGMG DATABASE_MIRRORING_LOGIN_GROUP
LGNM NAME CHANGE
LGO LOGOUT
LGSD SUCCESSFUL_LOGIN_GROUP
LO LOGOUT_GROUP
MNDB DATABASE_CHANGE_GROUP
MNDO DATABASE_OBJECT_CHANGE_GROUP
MNDP DATABASE_PRINCIPAL_CHANGE_GROUP
MNO SCHEMA_OBJECT_CHANGE_GROUP
MNSO SERVER_OBJECT_CHANGE_GROUP
MNSP SERVER_PRINCIPAL_CHANGE_GROUP
NMLG NO CREDENTIAL MAP TO LOGIN
OP OPEN
OPDB DATABASE_OPERATION_GROUP
OPSV SERVER_OPERATION_GROUP
PWAR APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
PWC CHANGE PASSWORD
PWCG LOGIN_CHANGE_PASSWORD_GROUP
PWCS CHANGE OWN PASSWORD
PWEX PASSWORD EXPIRATION
PWMC MUST CHANGE PASSWORD
PWPL PASSWORD POLICY
PWR RESET PASSWORD
PWRS RESET OWN PASSWORD
PWU UNLOCK ACCOUNT
R REVOKE
RC RECEIVE
RF REFERENCES
RS RESTORE
RWC REVOKE WITH CASCADE
RWG REVOKE WITH GRANT
SL SELECT
SN SEND
SPLN SHOW PLAN
STSV SERVER_STATE_CHANGE_GROUP
SUQN SUBSCRIBE QUERY NOTIFICATION
SVCN SERVER CONTINUE
SVPD SERVER PAUSED
SVSD SERVER SHUTDOWN
SVSR SERVER STARTED
TASA TRACE AUDIT START
TASP TRACE AUDIT STOP
TO TAKE OWNERSHIP
TODB DATABASE_OWNERSHIP_CHANGE_GROUP
TODO DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
TOO SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
TOSO SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
TRCG TRACE_CHANGE_GROUP
TRO TRANSFER
UP UPDATE
USAF CHANGE USERS LOGIN AUTO
USLG CHANGE USERS LOGIN
VDST VIEW DATABASE STATE
VSST VIEW SERVER STATE
VWCT VIEW CHANGETRACKING
XA EXTERNAL ACCESS ASSEMBLY
XU UNSAFE ASSEMBLY




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.