Copying table rows of encrypted columns from one SQL Server to another

[l***@gmail.com]

Hi,

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...

Thanks,

Larry

[Erland Sommarskog]

Post by l***@gmail.com
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?

You should be able to use BACKUP/RESTORE SERVICE MASTER KEY to move a
service key from one server to another.

But I kind expect that this is not needed. If you backup the database
master key one database one server and restore it on another, it should
be encrypted with the service master key of the second machine. I may
have tested this in the past, but that was a while ago.

[l***@gmail.com]

Post by Erland Sommarskog

Post by l***@gmail.com
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?

You should be able to use BACKUP/RESTORE SERVICE MASTER KEY to move a
service key from one server to another.
But I kind expect that this is not needed. If you backup the database
master key one database one server and restore it on another, it should
be encrypted with the service master key of the second machine. I may
have tested this in the past, but that was a while ago.
--

Hi Erland!

Thanks for responding. I hope you wouldn't need to restore a Service Master Key created on server A to server B. Couldn't that cause issues with server B, if you already had databases with encryption on it?

Larry

[Erland Sommarskog]

Post by l***@gmail.com
Thanks for responding. I hope you wouldn't need to restore a Service
Master Key created on server A to server B. Couldn't that cause issues
with server B, if you already had databases with encryption on it?

Yes, that is likely to happen. You would have to export all database keys
and re-import them with the new service key I suppose.

[Larry]

Just wanted to follow-up on this topic. I was able to restore the Database Master Key and the Certificate (public and private keys) to the other server, and then recreate the Symmetric Key using all of the same values as the original server used.

There is only one caveat with doing this. In the decryption session, you MUST open the Master Database Key before attempting to open the Symmetric Key. If you don't, you get the error, "Please create a master key in the database or open the master key in the session before performing this operation". Once you open the Database Master Key, the decryption works fine.

The only thing I don't like about this is that you have specify the Database Master Key password to open it, so now I have the main encryption password for all to view within my stored procedure...not ideal by any standards... I tried doing something minor to try to obscure it and use a variable for the password, but apparently the OPEN MASTER KEY command doesn't support a variable for the password.

If anyone has a work around for this situation, I would really appreciate it if you would let me know.

Thanks,

Larry

[Erland Sommarskog]

Post by Larry
The only thing I don't like about this is that you have specify the
Database Master Key password to open it, so now I have the main
encryption password for all to view within my stored procedure...not
ideal by any standards.... I tried doing something minor to try to
obscure it and use a variable for the password, but apparently the OPEN
MASTER KEY command doesn't support a variable for the password.

That's really bad. But there is dynamic SQL.

But no matter how you do it, the password must come from somewhere. Either
the user provides it, or you store it the database somewhere. You can
obfuscate it, hide in a CLR assembly, but it will be there.

[Larry]

Tried dynamic sql, but the problem is when you use the EXECUTE function it starts up a new session, so when it completes, the current session still doesn't know about the master key.

The fortunate thing is that the public facing server is the "original" server, so that I don't have to specify it in any stored procedure on that server, just on my internal server. Still not great, but better.

Thanks for your help!

Larry

[Larry]

Actually, thinking about it, I was only trying dynamic sql with the Open key command itself. I guess I could put the entire Open DBMK, Open SMK, Select, Close SMK, Cose DBMK commands into the dynamic sql, but as you say, it's still going to be there *somewhere*.

[Erland Sommarskog]

Post by Larry
Actually, thinking about it, I was only trying dynamic sql with the Open
key command itself. I guess I could put the entire Open DBMK, Open SMK,
Select, Close SMK, Cose DBMK commands into the dynamic sql, but as you
say, it's still going to be there *somewhere*.

Yes that should work. But it is certainly not appetizing.

You could use WITH ENCRYPTION, so that anyone who wants to view the code
needs to walk an extra mile.

[l***@gmail.com]

UPDATE:
As it turns out, I spoke (wrote?) incorrectly about backing up the "Symmetric" key, since you can't do that. It was the Service Master Key that I backed up. In addition, after some more searching, I found some information that seemed to suggest that I could restore the DB Master Key and Certificate to the internal server, then recreate the Symmetric key, using the same KEY_SOURCE and IDENTITY as was used to create it originally, which I hadn't done the first time, so had to go back and recreate the Symmetric Key on the DMZ server specifying these two options. I have yet to try to do the restore and Symmetric Key creation on the internals server, then attempt to decrypt some data, so in the meantime, if someone could corroborate my findings here, that would be great!

By the way, I neglected to mention that both servers are currently SQL Server 2005, if that matters.

Thanks again,

Larry