Sample Header Ad - 728x90

Error 15581 & 9001 when migrating TDE database to new server

0 votes
1 answer
154 views
We are migrating a database with TDE enabled from a SQL 2016 SP3 machine to SQL 2022 CU17 machine. New Server Setup: to prepare the new server, I have restored the service master key and master database master key (with private key), as well as the TDE certificate stored in master. I ran into an issue very early on with the service master key, but was able to resolve it by having the new SQL service running as the same Windows AD account as the previous server. I can provide the scripts I used to do any/all of this setup, but didn't want to clutter the post too much When I restore the database to the new server, I am running:
USE [master]
GO

Open Master Key Decryption by password = '8675309'

RESTORE DATABASE mysecuredb FROM
DISK = '\\myshare\mysecuredbbackup.bak'
WITH REPLACE

USE [mysecuredb]

OPEN MASTER KEY DECRYPTION BY PASSWORD = '12345'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '54321'
Both commands succeed, so it appears that the key is accessible as expected after the restore. DBCC CHECKDB also comes up clean. When I restore the database, it runs the upgrade steps to 2022 and comes online, but when I run ALTER DATABASE mysecuredb SET ENCRYPTION ON I get the following set of errors >Info: Beginning database encryption scan for database 'mysecuredb'. >Error 15581: Please create a master key in the database or open the master key in the session before performing this operation. >Error 9001: The log for database 'mysecuredb' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database. The database does not become suspect as I would expect with corruption, but sys.dm_database_encryption_keys shows that the database is stuck in state 2 (Encryption in progress), with seemingly no way of continuing or reverting the encrypt action. It may be worth noting that the database also has cell encryption enabled for some columns, however I am able to decrypt that data using the SYM/ASYM keys after the restore (but while TDE is disabled). This is my first time interacting with TDE, as the previous owner of this infrastructure has sailed to greener pastures, but I am not sure what I am missing from the available documentation from MS. Any help is appreciated!
Asked by Ben Adams (3 rep)
Oct 21, 2024, 05:45 PM
Last activity: Oct 21, 2024, 06:29 PM