July 12, 2013 at 9:32 am
KWymore (7/12/2013)
MSDN confused me on this one. Oh well, learned something new. Thanks for the question Steve!
Same here! The TDE article did seem to imply you needed both backups...ah well, always good to learn something.
July 12, 2013 at 9:49 am
I also went to MSDN and came up with the wrong answer, oh well.
July 12, 2013 at 10:34 am
I got this wrong. Looking at the reference provided (which was one of the pages that I read carefully before choosing an option) I found that the code example for restoring on a different machine included
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO
-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.
CREATE CERTIFICATE TestSQLServerCert
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKeyFile',
DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO[/CODE]
That second comment line very clearly states that the password is required as well as the certificate; the password is used twice, once for MKE and once in restoring the certificate. So it's pretty clear that either the "correct" answer is wrong or the page referenced contains a big error. I also guessed that the SQLPrivateKeyFile was an encrypted file also required, since the qualifier of the private key description is DECRYPTION BY PASSWORD and not ENCRYPTION BY PASSWORD.
The explanation provided for the answer is just a simple restatement of the answer, which makes it an utterly pointless explanation - it would have been nice to have an explanation that made some attempt to explain the answer rather than just repeat it.
Probably I'm misinterpreting something here, but I can't see what. Is anyone willing and able to explain it to me, please?
Tom
July 12, 2013 at 10:50 am
L' Eomot Inversé (7/12/2013)
I got this wrong. Looking at the reference provided (which was one of the pages that I read carefully before choosing an option) I found that the code example for restoring on a different machine included
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';GO
-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.
CREATE CERTIFICATE TestSQLServerCert
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKeyFile',
DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO[/CODE]
That second comment line very clearly states that the password is required as well as the certificate; the password is used twice, once for MKE and once in restoring the certificate. So it's pretty clear that either the "correct" answer is wrong or the page referenced contains a big error. I also guessed that the SQLPrivateKeyFile was an encrypted file also required, since the qualifier of the private key description is DECRYPTION BY PASSWORD and not ENCRYPTION BY PASSWORD.
The explanation provided for the answer is just a simple restatement of the answer, which makes it an utterly pointless explanation - it would have been nice to have an explanation that made some attempt to explain the answer rather than just repeat it.
Probably I'm misinterpreting something here, but I can't see what. Is anyone willing and able to explain it to me, please?
+1
July 12, 2013 at 11:21 am
IgorMi (7/12/2013)
Nice question!What if you already have a master key that is used by a certificate aimed for another database (dbA) on the instance you're moving the dbB?
Just for clarification.
I think you should drop the dbA certificate (backup before) using the old master service key, then drop the master key and recreate with another password (same as for dbB certificate), and then create the new certificate from the cert and key files you moved on the new instance, using the new master key?
Regards,
IgorMi
the master key is part of the encryption hierarchy. It can protect more than one certificate or asym key.
If you already have a master key on the new instance, you just restore the certificate, and have it protected by the master key. No need to drop anything.
July 12, 2013 at 11:22 am
OCTom (7/12/2013)
I must be missing something. I'm sure someone can put me straight. This link says that you need to restore both the DEK and the certificate http://msdn.microsoft.com/en-us/library/bb934049.aspx. I chose the first answer because of this.When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.
Thanks,
Tom
DEK is in the backup of the TDE database.
July 12, 2013 at 11:50 am
Steve Jones - SSC Editor (7/12/2013)
OCTom (7/12/2013)
I must be missing something. I'm sure someone can put me straight. This link says that you need to restore both the DEK and the certificate http://msdn.microsoft.com/en-us/library/bb934049.aspx. I chose the first answer because of this.When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate or asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.
Thanks,
Tom
DEK is in the backup of the TDE database.
It's Friday. My mind must have shut down. What you are saying here, Steve, makes no sense to me. I will research this and see if I can clear it up. I have not worked with encryption. It will be good to look into it, though on another day.
Thanks,
Tom
July 12, 2013 at 12:06 pm
It's worded poorly in BOL.
Essentially on the source you:
- create master key (protected by SMK) in master.
- create cert, protected by DMK (master key).
- you backup the cert, decrypting it using the password from the previous step, and assign a (new hopefully) password to the backup. You do this first. Backup certs/keys before you do anything else!
- you create a DEK in the db you are encrypting, protected by the cert.
- you enable TDE
- you backup the TDE database. The DEK is inside this backup as part of the meta data, but it's encrypted and protected by the cert, which is NOT in the backup.
On a new instance, say in a DR situation or movement to a new instance.
- you create a master key if there isn't one. If there is, you just need a password protecting this.
- you create (from file, as restore) the certificate from the backup above. You need the password protecting the files.
- Now you restore the TDE database. The cert exists, so the instance uses this to decrypt the DEK in the database, subsequently decrypting the data when requested by the new instance.
July 12, 2013 at 12:22 pm
Steve Jones - SSC Editor (7/12/2013)
It's worded poorly in BOL.Essentially on the source you:
- create master key (protected by SMK) in master.
- create cert, protected by DMK (master key).
- you backup the cert, decrypting it using the password from the previous step, and assign a (new hopefully) password to the backup. You do this first. Backup certs/keys before you do anything else!
- you create a DEK in the db you are encrypting, protected by the cert.
- you enable TDE
- you backup the TDE database. The DEK is inside this backup as part of the meta data, but it's encrypted and protected by the cert, which is NOT in the backup.
On a new instance, say in a DR situation or movement to a new instance.
- you create a master key if there isn't one. If there is, you just need a password protecting this.
- you create (from file, as restore) the certificate from the backup above. You need the password protecting the files.
- Now you restore the TDE database. The cert exists, so the instance uses this to decrypt the DEK in the database, subsequently decrypting the data when requested by the new instance.
So what you need is a backup of the certificate that protects the DEK plus the password protecting that backup. Of course if you have the certificate without needing that password you have done something horribly insecure somewhere, but the QotD doesn't ask what you need in the absence of horrible insecurity so maybe it's fair enough not to mention a need for that password? ?? ??? Or am I still misunderstanding?
Tom
July 12, 2013 at 12:47 pm
I'm not sure what you mean about insecure if you have the cert without the password. You could have a blank password, but in that case you'd technically have the password.
July 12, 2013 at 5:32 pm
Steve Jones - SSC Editor (7/12/2013)
I'm not sure what you mean about insecure if you have the cert without the password. You could have a blank password, but in that case you'd technically have the password.
OK, that sounds as if you are saying that if you have the cert then of course you have the password, because there's no way you can have the cert without having the password. That of course is true, but whether it's a justification for saying you just need the cert, no need to mention the password, is maybe debatable. As for insecure - if you have a blank password I think that's insecure; it's one of the first things I would try, even before I started trying to find out wife's maiden name, first school, favourite porn star, and so on; I have come across blank passwords far too often to think that it's pointless trying them first, and that makes them utterly insecure.
Tom
July 14, 2013 at 2:54 pm
In the explanation it was mentioned the following: "The DEK is inside the backup", to be honest I am not an expert of (Transparent Data Encryption), but I am not sure about this I guess the Database Encryption Key (known as DEK) has to be backed up separately not within the Certificate backup even if it was in the same statement like that:
backup certificate ServerCertificate to file = 'SomePath'
with private key (file = 'SomePath', encryption by password = 'SomePassword');
So I am waiting for a reply from the experts.
Warm Regards,
Hany Helmy
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
July 15, 2013 at 3:21 am
New thing to me...Thanks Steve....
July 15, 2013 at 9:38 am
hany.helmy (7/14/2013)
In the explanation it was mentioned the following: "The DEK is inside the backup", to be honest I am not an expert of (Transparent Data Encryption), but I am not sure about this I guess the Database Encryption Key (known as DEK) has to be backed up separately not within the Certificate backup even if it was in the same statement like that:
backup certificate ServerCertificate to file = 'SomePath'with private key (file = 'SomePath', encryption by password = 'SomePassword');
So I am waiting for a reply from the experts.
Warm Regards,
Hany Helmy
The DEK is inside the database backup. It is not backed up separately, and actually cannot be.
July 15, 2013 at 1:31 pm
Steve Jones - SSC Editor (7/15/2013)
hany.helmy (7/14/2013)
In the explanation it was mentioned the following: "The DEK is inside the backup", to be honest I am not an expert of (Transparent Data Encryption), but I am not sure about this I guess the Database Encryption Key (known as DEK) has to be backed up separately not within the Certificate backup even if it was in the same statement like that:
backup certificate ServerCertificate to file = 'SomePath'with private key (file = 'SomePath', encryption by password = 'SomePassword');
So I am waiting for a reply from the experts.
Warm Regards,
Hany Helmy
The DEK is inside the database backup. It is not backed up separately, and actually cannot be.
Ok, Got it thanx Steve.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply