SQL SERVER database encryption.

  • Hello All,

    I am in a bind here and hoping someone will be able to help me out in this regard.

    There is a database in our environment that is using database encryption. The database server cert file and servercerthkey is avialable but I cant find the password. I am totally confused, of all the materiel red online I cant seems to find the answer.

    how can I restore this database?

    All the material online explain how to do it will password. what is the password going to be. I don't know who created the password at the time of setting up the encryption, all I have is the database, backup and servercert file and servercertkey file. what are my option. Many thanks in this regard.

    Is it possible to change/reset the password?

    Thanks a lot.

    B

  • qur7 (3/4/2013)


    Hello All,

    I am in a bind here and hoping someone will be able to help me out in this regard.

    There is a database in our environment that is using database encryption. The database server cert file and servercerthkey is avialable but I cant find the password. I am totally confused, of all the materiel red online I cant seems to find the answer.

    how can I restore this database?

    You can't. That's the entire point of transparent database encryption. Without the certificates and the password that was used on backing the cert up you cannot restore that DB to another server or move to another server

    If all you have is the backup, there's no online version of this database, then without the password that database is not going to be restorable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • But if you have it online, you as a sysadmin still can backup the SMK and DMK and provide new password without knowing the old one to protect the backups of keys, and then...

  • Thanks,

    Yes I am the admin, the database in online. please also note that i do have database certificate files. i just don't have the password. please let me know how to take a new backup with the password, and restore it on a different server. Please point me in the right direction.

    Thanks in advance

  • If the database is online, the password on the certificate backups is irrelevant, you can take new backups with any password you want (see Books Online for details).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Move a TDE Protected Database to Another SQL Server http://technet.microsoft.com/en-us/library/ff773063%28v=sql.105%29.aspx

  • OK, I think I get it now, so as long as the database is up , i can take a new backup with a new password and restore it on the new server. I am going to try it now and get back here if any issues.

    Thanks a lot for all's help.

  • Yes, as long as the DB is online, you have enough rights, the certificate is protected by DMK not by a password (which you don't know) you can backup it and protect the private key of that certificate, in that backup, by a password you provide.

    http://msdn.microsoft.com/en-us/library/ms178578.aspx

  • qur7 (3/5/2013)


    OK, I think I get it now, so as long as the database is up , i can take a new backup with a new password and restore it on the new server.

    Correct.

    It's when all you have is the database backup and the backups of the certificates that you have a major problem if you don't have the passwords.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply