August 27, 2013 at 9:01 am
Hello all,
I am testing TDE in a lab environment for a proof of concept for roll out. I have 3 servers, a primary, a disaster recovery using AlwaysOn Availability Groups, and a report server subscriber using transaction replication from the primary. I set up the primary first using the scripts below:
USE MASTER
GO
-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password1';
-- Backup the Master Key
BACKUP MASTER KEY
TO FILE = 'Server_MasterKey'
ENCRYPTION BY Password = 'Password2';
-- Create Certificate Protected by Master Key
CREATE Certificate SQLCertTDEMaster
WITH Subject = 'Certificate to protect TDE key';
-- Backup the Certificate
BACKUP Certificate SQLCertTDEMaster
TO FILE = 'SQLCertTDEMaster_cer'
WITH Private KEY (
FILE = 'SQLCertTDEMaster_key',
ENCRYPTION BY Password = 'Password3'
);
Then I encrypted the Primary DB with the following scripts:
USE TestDB
GO
-- Create a Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH Algorithm = AES_128
ENCRYPTION BY Server Certificate SQLCertTDEMaster;
-- Enable the Database for Encryption by TDE
ALTER DATABASE TestDB
SET ENCRYPTION ON;
When I run the following select statement (select * from sys.dm_database_encryption_keys
). It lists the database in an encryption_state = 2, with 0 percent_complete. It has been that way for 24 hours and the database is only 2GB.
Any suggestions?
Thanks,
DK
August 27, 2013 at 2:10 pm
What is the user_access_desc from:
select * from sys.databases
August 27, 2013 at 3:41 pm
And what was when you executed:
ALTER DATABASE TestDB SET ENCRYPTION ON;
Because I can simulate this when during executing set encryption on the DB is in single_user. Then even after set the db to multi_user the state remain 2, but after execute again the ALTER DATABASE TestDB SET ENCRYPTION ON; the state go to 3.
August 28, 2013 at 2:52 pm
Thanks for the replies. I did not have the database in single-user mode, as far as I am aware of. I ended up having to remove the master key and certificate. Then I added them again, this time without Availability Groups and transactional replication, and it worked. I will be trying to get the whole configuration up again tomorrow.
Thanks again,
DK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy