2

The information I have so far is that I can simply do the following to create a valid certificate to use for tde. What good or advantage is the Encrypt with Password = option if I can do it without that?

Conversely, why not just make the certificate with this simple syntax instead of using a password or doing it using the FROM BINARY syntax?

Create Certificate tdeCert WITH Subject = 'My tde certificate'
6
  • 1
    Does this answer your question? How do I generate the private key needed to restore the encrypted backup?
    – AMtwo
    Commented Jul 18, 2022 at 19:33
  • Thanks Andy, but that's not what I'm asking. Let me try again. Why use WITH ENCRYPT BY PASSWORD option when I make the certificate? That's what I'm asking. Why do I need that, or why should I use it if the cert I make is encrypted by the Master Key anyway.
    – Lee M
    Commented Jul 18, 2022 at 19:45
  • Were you able to successful restore that certificate on a different server and restore the database that was encrypted with it? remember that you could end up locked out of that database in case you need to recover from any kind of disaster on a different server.
    – Ronaldo
    Commented Jul 18, 2022 at 21:45
  • @Lee, from the linked answer: "To make your certificate backup useful for restoring encrypted backups you must backup the certificate WITH PRIVATE KEY" I believe that answer addresses exactly what you are asking.
    – AMtwo
    Commented Jul 18, 2022 at 22:14
  • 1
    I'm not asking about backing up the certificate to restore to a different SQL Server. I'm asking specifically about the CREATE CERTIFICATE statement. There are options to create it using ENCRYPTION BY PASSWORD, but if I don't use that, I still end up with a valid certificate that works for TDE. So, my question is, what's the advantage to using teh ENCRYPTION BY PASSWORD = or even the FROM BINARY syntax.
    – Lee M
    Commented Jul 19, 2022 at 15:15

1 Answer 1

3

What good or advantage is the Encrypt with Password = option if I can do it without that?

If you omit this it'll encrypt it with the database master key, so really, it's up to you to decide how you would like the private key protected, though since this is for TDE the best course of action is to use the DMK as it will then be able to transparently decrypt it for use (via the transparent decryption hierarchy).

[...] or even the FROM BINARY syntax.

FROM BINARY is an easy way to transfer keys around without needing to move the physical certificate. This way, a certificate can be created on one machine and then an identical copy created on another machine all via TSQL and no need to remotely copy files.

1
  • 1
    Thank you Sean. This is the information I was looking for.
    – Lee M
    Commented Jul 21, 2022 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.