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?
Best Answer
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.