June 3, 2012 at 1:55 am
Probably a easy one,
I've inherited a new SQL 2008 Enterprise environment which uses encrypted databases.
I don't have the passwords for the certificate.
Can I simply backup the existing cert. with a new password.
Backup the LIVE db.
Copy the CERT and .bak file to TEST server.
Restore CERT. on test server using new password and .bak file
June 3, 2012 at 2:17 am
Providing the certificate is protected by the database master key and not just password and you have rights on the database master key (which as a sysadmin you do), that should work.
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
June 3, 2012 at 3:25 am
Bobby Glover (6/3/2012)
Probably a easy one,I've inherited a new SQL 2008 Enterprise environment which uses encrypted databases.
I don't have the passwords for the certificate.
Your statement implies that the certificate is not encrypted by the database mater key but by a password, which you do not have. Is this correct?
Verify how the certificate is encrypted using
USE master
Select name, certificate_id, principal_id,
pvt_key_encryption_type, pvt_key_encryption_type_desc,
pvt_key_last_backup_date
From sys.certificates
Bobby Glover (6/3/2012)
Can I simply backup the existing cert. with a new password.
Nt if it's encrypted by a password, you need the original password to decrypt the cert 😉
See this link for more on moving TDE protected databases.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 3, 2012 at 3:46 am
Thanks both of you I'll try it tomorrow.
June 3, 2012 at 4:48 am
If the cert is only protected by a password that you don't have, I would recommend ASAP decrypting the DB, creating a cert you do have the password for and then re-encypting with the new cert (though note that makes all historical backups you have near-useless as you don't have a backup of the cert they were encrypted with)
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
June 3, 2012 at 5:40 am
Ok Gail. Thanks I thought that would be the case but nice to have it confirmed.
Regards
Rob
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply