March 30, 2016 at 6:43 am
What is the best practice for encrypting columns on one SQL Server and decryption on another SQL Server? We only want the user to be able to encrypt information on the first SQL server, but not allow them to open encryption keys your view data.
March 30, 2016 at 12:31 pm
Your question is a bit open-ended, and there is not much information about your application.
But assuming that you use stored procedures to read and write data, you can use the OPEN SYMMETRIC KEY to open the key inside the procedure. But you do not grant permission to the users to run this command. Instead you sign the procedure with a certificate, and grant a user create from the certificate the required permission to open the key.
When it comes to the procedure that decrypts the data, you don't give these users permission on this procedure. Or you simply don't sign it, and only users who have direct permission to open they key and decrypt the data.
I will have to confess that I have never implemented something like this myself.
There is an article on my web site that discusses the technique with certificate signing in detail:
http://www.sommarskog.se/grantperm.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
April 8, 2016 at 11:58 am
If you really want to separate the data provider and data consumer, perhaps using asymmetric keys is the better solution? That way, those that enter/update data encrypt using the PubKey while whoever is authorized to read has access to the PrivKey. The key management work is non-trivial plus you can expect a performance hit (potentially big hit) but it'll get you what you want.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply