I have been tasked with getting a number of databases off a SafeNet appliance and use native SQL Server for encryption on a few columns. The applications for these databases currently use a view to access the data. In order for me to make this as seamless as possible I would need to replace the view with some kind of auto decryption for specific users only. I've kicked around some ideas such as using DECRYPTBYKEYAUTOCERT in the view and having the password parameter call a function that validates the user and returns the password but even encrypting the view/function is vulnerable as local encryption of these objects is weak. I'm guessing I will need to implement some outside code, perhaps via CLR. I suppose I could delve into the route of locking down the view/function to everyone else but that doesn't stop someone from just adding a new account with access.
Here are a few details:
1. The applications will continue to access the data from a view as they do today. No changes to the application, such as opening keys, will be made.
2. Only users configured by the DBA can decrypt the data automatically. There are also a number of accounts that are sysadmins on the instance that I would need to block.
3. Nobody outside of the DBA team will know the passwords to any certificates/keys.
4. We need to be able to rotate the certificates/keys.
Current process used by the SafeNet appliance:
1. Application uses a view that references the SafeNet view.
2. The SafeNet view pulls the same data but for the columns to decrypt it calls a function that receives user/database info along with the encrypted data.
3. The function called by the SafeNet view passes the user/database info with encrypted data to a CLR.
In all, the goal is to get off the appliance with as little effort/impact outside of the DBA group and without spending money on some other solution. Personally I haven't done any development in Visual Studio since college but if I have to go that route then so be it.
Any help/tips/advice will be greatly appreciated. :-)