December 14, 2016 at 6:43 am
I have a new SQL Server 2014 instance that I need to import many older SQL 2012 databases to in an effort to refresh a development environment. The current 2012 databases are encrypted via TDE but the certificate that was originally used has since expired. I know that doesn't matter in terms of usability (you just get a warning message when it is used apparently). Still, since I am having to make this move I'd like to create a new certificate to encrypt the databases on their new home on SQL 2014 and eventually use this same certificate on the upcoming production server. The databases in question will remain in place on their SQL 2012 home until a new production server instance is created. I am thinking the best way to proceed is to:
- Create a new certificate on the 2014 server
- Back up (save) the newly created certificate for eventual restoration to the new prod server when available
Then:
- Un-encrypt the database(s) in place on the SQL 2012 server
- Make a copy only backup of the database(s) on the 2012 server
- Re-encrypt the database(s) on the 2012 server
- Restore the un-encrypted database(s) to the new 2014 server
- Re-encrypt the database(s) using the new certificate
- Repeat as necessary
Am I missing anything glaring here, or is my methodology otherwise flawed? Does anyone have any further advice?
December 14, 2016 at 7:33 am
Siberian Khatru (12/14/2016)
I have a new SQL Server 2014 instance that I need to import many older SQL 2012 databases to in an effort to refresh a development environment. The current 2012 databases are encrypted via TDE but the certificate that was originally used has since expired. I know that doesn't matter in terms of usability (you just get a warning message when it is used apparently). Still, since I am having to make this move I'd like to create a new certificate to encrypt the databases on their new home on SQL 2014 and eventually use this same certificate on the upcoming production server. The databases in question will remain in place on their SQL 2012 home until a new production server instance is created. I am thinking the best way to proceed is to:- Create a new certificate on the 2014 server
- Back up (save) the newly created certificate for eventual restoration to the new prod server when available
Then:
- Un-encrypt the database(s) in place on the SQL 2012 server
- Make a copy only backup of the database(s) on the 2012 server
- Re-encrypt the database(s) on the 2012 server
- Restore the un-encrypted database(s) to the new 2014 server
- Re-encrypt the database(s) using the new certificate
- Repeat as necessary
Am I missing anything glaring here, or is my methodology otherwise flawed? Does anyone have any further advice?
Very long winded. More like this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 14, 2016 at 9:15 am
Perry Whittle (12/14/2016)
Siberian Khatru (12/14/2016)
I have a new SQL Server 2014 instance that I need to import many older SQL 2012 databases to in an effort to refresh a development environment. The current 2012 databases are encrypted via TDE but the certificate that was originally used has since expired. I know that doesn't matter in terms of usability (you just get a warning message when it is used apparently). Still, since I am having to make this move I'd like to create a new certificate to encrypt the databases on their new home on SQL 2014 and eventually use this same certificate on the upcoming production server. The databases in question will remain in place on their SQL 2012 home until a new production server instance is created. I am thinking the best way to proceed is to:- Create a new certificate on the 2014 server
- Back up (save) the newly created certificate for eventual restoration to the new prod server when available
Then:
- Un-encrypt the database(s) in place on the SQL 2012 server
- Make a copy only backup of the database(s) on the 2012 server
- Re-encrypt the database(s) on the 2012 server
- Restore the un-encrypted database(s) to the new 2014 server
- Re-encrypt the database(s) using the new certificate
- Repeat as necessary
Am I missing anything glaring here, or is my methodology otherwise flawed? Does anyone have any further advice?
Very long winded. More like this
- Just create a new certificate on the 2012 server
- take a backup of new cert on 2012 server
- restore cert backup to the 2014 instance
- Change the 2012 TDE database to use the new certificate
- restore the 2012 db to 2014
Thank you for the reply. That makes sense, but in order to change the certificate on a database -- don't I need to un-encrypt it and then re-encrypt it with the new certificate? Or is there a command I am missing to simply change the encryption method?
December 14, 2016 at 9:56 am
Siberian Khatru (12/14/2016)
but in order to change the certificate on a database -- don't I need to un-encrypt it and then re-encrypt it with the new certificate? Or is there a command I am missing to simply change the encryption method?
No, you use the ALTER DATABASE ENCRYTION KEY command
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER
CERTIFICATE Encryptor_Name
Run it into a test environment\database first to get the feel for it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 14, 2016 at 10:21 am
Perry Whittle (12/14/2016)
Siberian Khatru (12/14/2016)
but in order to change the certificate on a database -- don't I need to un-encrypt it and then re-encrypt it with the new certificate? Or is there a command I am missing to simply change the encryption method?No, you use the ALTER DATABASE ENCRYTION KEY command
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER
CERTIFICATE Encryptor_Name
Run it into a test environment\database first to get the feel for it
Thank you very much for the nudge in the right direction!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply