2

I have been searching for 2 days for the solution to this, but every solution does not apply to my situation.

I have 2 SQL servers: DataServer and InterfaceServer set up as linked servers. DataServer does all of our data processing. InterfaceServer pulls only the data it needs from DataServer. Each server has its own encryption process and our compliance department does not want them to use the same passwords/certficates/etc... Due to these restrictions, I am unable to execute a stored procedure that does the decryption for me.

I have tried creating views with the DecrypByKeyAutoCert() function as well as creating views with SELECT ... FROM OPENQUERY() to execute the stored procedure that does the decryption, but they always return NULL values for the decrypted data. If I run them on the DataServer itself, it returns the data fine.

Is there a way to create an assymmetric key on InterfaceServer, use the public key to encrypt data in a table on DataServer, then decrypt it on the InterfaceServer after copying everything over?

2
  • Does the solution need to use asymmetric encryption? Do you have any restrictions against using clr?
    – mathewb
    Jun 22, 2018 at 15:40
  • It doesn't have to use asymmetric encryption, but that is the only way I could think of accomplishing what I am trying to do. I don't know much about CLR, so I am not sure. Right now I am trying to configure the Interface Server for RDC (not sure if I am allowed to), create a stored procedure there that encrypts the data, then have a job run on the Data Server that decrypts the data into a temp table and executes the Interface SP with the temp table as a parameter.
    – ridetnx
    Jun 22, 2018 at 15:50

1 Answer 1

0

It's possible to do what you are suggesting. You can't create the same asymmetric key directly using parameters, but you can backup the database containing the asymmetric key and backup the database master key on the data server. Restore them on the Interface server and drop the private key on the data server using ALTER ASYMMETRIC KEY remove private key. That way the data server can only encrypt and the Interface server can decrypt using the same asymmetric key.

Or, you can create a symmetric key encrypted by the asymmetric key using the same key_source and identity_value on each server and transfer the data in it's encrypted stated, then decrypt it on the Interface server. It does not matter, in the latter case, if the asymmetric keys are different because each protects keys that are identical on each server. If your goal is to encrypt data in transit, then the second one will be easier to recover if there are any issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.