2014-10-03 14:46:16 UTC
I have a process that I need to build regarding copying data from a table on a SQL Server in our DMZ that contains encrypted columns into a SQL Server within our network with a similarly designed table. I plan on running the job to do this from the internal server pulling the data from the DMZ server.
My question is, what do I need to do to be able to then turn around on the internal server and decrypt the data within this table? I have created a DB Master Key, Certificate, and Symmetric Key on the DMZ database, and have created the stored procedure on the DMZ server to add the data performing the encryption to the table, which is working. I've also made backups of those keys. I've read a bit about the Service Master Key, and if I understand that correctly, it's what is used to encrypt the DB Master Key. I also understand that the SMK is generated when SQL Server is installed, therefore the SMK on the DMZ server would be different than the SMK on the internal server. So, does that mean that even if I restore the DB Master Key, Certificate, and Symmetric Key to my internal server, that they wouldn't be able to decrypt the data pulled in from the DMZ table?
A little confused by multi-server encryption...