January 27, 2015 at 2:59 am
Hi there,
here is the situation : I have a primary instance, who host three databases, and (this is the trick) one got encrypted column by symmetric key (the path of encryption : master key is created with password, then asymmetric key is created with this master key and then a symmetric key is created with this asymmetric key) (we are using SQL2008, Enterprise Edition).
The goal is to setup database mirroring with a secondary instance for theses three databases, and begin able to decrypt encrypted data on secondary after failover.
As I'm more or less new in data encryption, what should I consider to setup this mirroring ?
This is what i'm thinking for the case of the database with encrypted column :
- Restore database with encrypted data on secondary with norecovery, and its subsequent logs backup file
- Restore master key (backuped from primary) on secondary database in norecovery mode
- Set up mirroring
Do I forget something ?
Some article speak about Transparent Data Encryption. Is it related to this case ? (I understand that TDE works with a certificate in master database, and all keys in users database are created with this certificate. And finally, encryption has to be set ON on primary database).
Actually, a secondary already exists for this instance but we have to replace the server. I guess TDE is not currently used, because encryption is OFF on primary databases.
Thanks a lot !
Dam
January 27, 2015 at 9:08 am
Dam_DBA (1/27/2015)
Hi there,here is the situation : I have a primary instance, who host three databases, and (this is the trick) one got encrypted column by symmetric key (the path of encryption : master key is created with password, then asymmetric key is created with this master key and then a symmetric key is created with this asymmetric key) (we are using SQL2008, Enterprise Edition).
Dam_DBA (1/27/2015)
- Restore master key (backuped from primary) on secondary database in norecovery mode
The database master key for this instance is not required. You do need to transfer any certificates or other security mediums to the new server to be able to decrypt the data.
It's not entirely clear here from your description exactly what's been implemented so would need more detail.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 27, 2015 at 9:24 am
Ok, so for our database we want to mirror, with have a master key, encrypted by Service Master Key.
Then we have created an asymmetric key, and finally we have created a symmetric key, encrypted with the previous asymmetric key.
We have a column encrypted with this symmetric key.
After about a day of research, I figured out that sp_control_dbmasterkey_password probably solved my problem.
This procedure consists in stock the master key password for any database.
For example : sp_control_dbmasterkey_password 'myDB', 'myMasterKeyPassword', 'add'
My test was the following :
- I restored the database on another server WITH NORECOVERY
- I executed sp_control_dbmasterkey_password with the good parameters for this database : sp_control_dbmasterkey_password 'myDB', 'myMasterKeyPassword', 'add'
- I restored the database WITH RECOVERY
and decryption by opening asymmetric key worked !
Then I dropped the database (not replace, since it seems to keep master key decryption), and made the following test :
- Drop the previously restored database
- Drop the master key password by executing this statement : sp_control_dbmasterkey_password 'myDB', 'myMasterKeyPassword', 'drop'
- I restored the database WITH NORECOVERY
- I restored the database WITH RECOVERY
And decryption didn't work....until I execute sp_control_dbmasterkey_password 'myDB', 'myMasterKeyPassword', 'add' !
March 31, 2016 at 11:30 am
When I restore a database with encryption to a different server, I have to reencrypt the DB Master Key using the Server Key.
I use this script:
Use <DATABASENAME>
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<INSERT YOUR KEY HERE>';
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY;
Donald
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply