February 23, 2016 at 5:09 pm
Hello guys,
What are the steps to follow if I need to migrate a db with column level encryption enabled.
Any additional steps to follow if this db is part of always on availability group so that the encrypted column can be decrypted from the readable secondary db.
I have a requirement to migrate this db to sql 2014 from sql 2008.
Many thanks.
February 24, 2016 at 1:35 am
On the destination server after migration. OPEN the database master key using the same password as on the source server.
NB : The OPEN command works for the same session only, the decryption capability does not work for other sessions or when the current session closes. then ALTER the database master key to associate it with the current instance’s service master key
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘yourMasterKey’
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
follow this link https://www.simple-talk.com/sql/database-administration/encrypting-your-sql-server-2012-alwayson-availability-databases/ to read about AAG and TDE
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
February 24, 2016 at 6:11 am
Thanks Kenny.
The article mentioned is for TDE enabled db. Not for column level encrypted db.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply