January 28, 2016 at 8:40 am
I thought that you need the Private Key to restore a TDE Database?
All that I have is the following:
tde.key
tde.cert
Master.Key
Service Master.Key
Is this enough to restore a TDE Database?
I tried looking up the syntax to do the restore but I'm not clear on the steps that are needed.
I did this a long time ago and I got it right.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2016 at 9:14 am
Are you restoring on the same instance or a different/new one?
The SMK and DMK do not need to match. SQL creates the SMK for the instance, and this can be unique to each instance. If you're trying to ensure everything works in a DR situation where you are re-creating the instance, then you probably want a backup of the SMK to restore. Otherwise, you don't need this.
The DMK is in master (in this case) and is only needed to protect the certificate.
The DEK is inside your database backup, and you can't separate this, so you'll have this.
The certificate is the item you need. This is used to encrypt/decrypt the DEK, so this is what you restore on your new instance (after creating a DMK) and this is used by the restore process. Use CREATE CERTIFICATE FROM FILE in master.
You can create a certificate without the private key, but I'm not sure if this will allow you to restore and decrypt a TDE database. That's a good question. I've got VM issues at the moment, or I'd try this.
If you have TDE, you should be able to test with a small database. Create a new one, create a DEK protected by your cert, backup (cert + db). Now on a new instance, restore the cert, minus the private key, and then try to restore the backup.
January 29, 2016 at 5:16 am
Welsh Corgi (1/28/2016)
I thought that you need the Private Key to restore a TDE Database?All that I have is the following:
tde.key
tde.cert
Master.Key
Service Master.Key
Is this enough to restore a TDE Database?
I tried looking up the syntax to do the restore but I'm not clear on the steps that are needed.
I did this a long time ago and I got it right.
I'm assuming that tde.key is the private key backup and tde.cert is the certificate backup.
These are all you need to restore the TDE protected database to another server.
Please see my guide at this link[/url] for more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2016 at 7:09 am
The encryption keys are of the following type.
I'm not sure that I have the private key.
I was told that you do not need the private key.
Master Key Type = Key File
ServiceMaster.Key = Key File
tde.cer = Security Certificate
tde.key = key file
Do I have what I need to decrypt the Database?
What would be the syntax?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2016 at 10:55 am
to restore the database to another server you'll need to backup the cert with the private key. You then restore this on the target server, this link details the process
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2016 at 11:01 am
Perry Whittle (2/8/2016)
to restore the database to another server you'll need to backup the cert with the private key. You then restore this on the target server, this link details the process
Which file is the private key?
Someone else encrypted the database and he did not save the script.:unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2016 at 11:15 am
take a new backup of the cert
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2016 at 11:26 am
Perry Whittle (2/8/2016)
take a new backup of the cert
Don't I need a password? :unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2016 at 5:37 pm
You do in order to decrypt the cert for the backup on the main instance.
Do you not know the password of the cert? Is it not in any VCS or other code?
If not, you may be in trouble here. I'd bcp out all data from the db and script objects. Then I'd disable encryption and re-enable it with good password management.
February 8, 2016 at 6:12 pm
Steve Jones - SSC Editor (2/8/2016)
You do in order to decrypt the cert for the backup on the main instance.Do you not know the password of the cert? Is it not in any VCS or other code?
If not, you may be in trouble here. I'd bcp out all data from the db and script objects. Then I'd disable encryption and re-enable it with good password management.
Thanks. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2016 at 6:19 pm
Steve Jones - SSC Editor (1/28/2016)
quote]
I need to be able to do both. Thanks for your help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2016 at 4:40 am
Welsh Corgi (2/8/2016)
Perry Whittle (2/8/2016)
take a new backup of the certDon't I need a password? :unsure:
You only need a password for decryption during the backup if the cert is protected by a password.
If the cert is protected by the DMK (the default), just supply a new password for the private key encryption and you're good to go
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 9, 2016 at 8:50 am
Do you understand what needs to be done, or do you need more info?
The certificate is protected by something inside SQL Server. Whatever that protection is must be unlocked to perform a backup. As Perry mentioned, you may or may not need the password here.
The password on the backup is completely separate to, and unrelated to, the protection on the cert in SQL Server. You can use a new password with every backup, but you want to be sure that you match those up correctly, the backup with the password. Note that you can't overwrite the backup file. If I execute these together, they work, but if I do them again, I get a "can't write into file" error.
BACKUP CERTIFICATE MyCert
TO FILE = 'MyCert.cer'
WITH PRIVATE KEY (
FILE = 'MyCert.pvk',
ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%'
, DECRYPTION BY PASSWORD = 'MyCert')
GO
BACKUP CERTIFICATE MyCert
TO FILE = 'MyCert2.cer'
WITH PRIVATE KEY (
FILE = 'MyCert2.pvk',
ENCRYPTION BY PASSWORD = 'NextStr0ngB@ckUpP@ssw0rd4TDEcERT%'
, DECRYPTION BY PASSWORD = 'MyCert')
GO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply