January 23, 2014 at 2:56 am
Hi there - I have come across a few issue when migrate a database from one instance to another due to a previous DBA creating certificates on a database and not storing the passwords somewhere safe.
Anyway, when trying to restore a user database to my new server it fails because there is a certificate which isn't available. So, I have dropped the certificates from the source master database and detached the user database. I then try and restore the user database to the destination server but it fails due to there;s no certificate. Why is this, if I dropped the certificate before detaching the database.
To make my life even more difficult, I can't attach the user database back to the source server because.... there's no certificate! Now I have a database file which I can't do anything with!
What can I do about this? Thanks in advance all.
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 3:30 am
Was the database encrypted with transparent database encryption? If so, then without the certificate you won't be able to reattach the DB. Deleting the certificate would not have unencrypted the database.
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
January 23, 2014 at 3:57 am
Thanks for this. If I have dropped the certificate, would it be possible to then unencrypt the databases which are still attached to the server?
Thankfully the user database that I have already reattached isn't business critical however is there anything I can do to restore this db?
Thanks!
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 4:15 am
arrjay (1/23/2014)
Thanks for this. If I have dropped the certificate, would it be possible to then unencrypt the databases which are still attached to the server?
No idea. I suspect not.
Thankfully the user database that I have already reattached isn't business critical however is there anything I can do to restore this db?
If it was encrypted with TDE and you don't have a backup of the certificate, no. That's the point of TDE, can't attach/restore the database without the certificate.
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
January 23, 2014 at 4:25 am
Do you not have a backup of the cert anywhere
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2014 at 5:04 am
the certs exist on another server (which the db orginally came from), is there a way of scripting them out and copying them to the server?
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 5:08 am
I have a Master database backup which should contain the cert - Any advice on restore the Master db as I'm struggling to put the server into single user mode
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 5:13 am
might be worth first attempting to get the cert from the other server, leave the master restore as last resort
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2014 at 5:21 am
arrjay (1/23/2014)
the certs exist on another server (which the db orginally came from), is there a way of scripting them out and copying them to the server?
Can you post the output of this query when run against the instance
SELECT name
, issuer_name
, certificate_id
, principal_id
, pvt_key_encryption_type_desc
, issuer_name
, cert_serial_number
, sid
, subject
, expiry_date
, start_date
, pvt_key_last_backup_date
FROM sys.certificates
To prevent making a bad situation even worse do nothing else at this point
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2014 at 5:28 am
Hi there - Not sure if this is meaningful; (certs removed)
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 5:40 am
Can you attach the link please... Thanks
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 5:41 am
arrjay (1/23/2014)
Hi there - Not sure if this is meaningful;
First things first, edit your post and remove details of all other certs 😉
Leave just the detail on the cert you are concerned with. The good news is that none of the certs are encrypted by password, theyre all encrypted by the database master key
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2014 at 5:49 am
Thanks Perry - I have removed the cert information. As the certs are encrypted by the database master key, does this mean they will be easily re-created. What do I have to do to migrate the certs to where I want them? Thanks again!
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 6:08 am
arrjay (1/23/2014)
Thanks Perry - I have removed the cert information. As the certs are encrypted by the database master key, does this mean they will be easily re-created. What do I have to do to migrate the certs to where I want them? Thanks again!
yes, if you foloow the link posted by Greg you should be able to complete this. If you still have issues post back.
DO NOT drop any more certs or databases at this time 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply