Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encryption: Going from appliance to native


Encryption: Going from appliance to native

Author
Message
jiffy_79
jiffy_79
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 228
Hello All,

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. :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54003 Visits: 40389
jiffy_79 (8/21/2014)
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.


Not possible without taking the "SA" priv away from them. In the same vein, you need to fix that problem even if you don't do the migration. No one outside of the DBA group should have any higher than "DBO" and you should be ultra stingy even with that priv. NO public facing application should have any higher than Read/Write/Execute privs.

Unrealistic in most shops but certainly attainable, my personal Nirvana would be that no application would ever have more than PUBLIC privs with exec privs on the stored procedures that the app needs to execute. Of course, that also means no ORMs or embedded SQL.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Perry Whittle
Perry Whittle
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11593 Visits: 16844
the users should just require the relevant permission on the cert itself

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
jiffy_79
jiffy_79
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 228
The applications themselves or their accounts only go as high as dbo in the databases. However, for other reasons there are accounts that have sysadmin access that are not tied to the applications at all.

So if I were to create the certs/keys/views and explicitly deny access it would have the desired result. My only concern is that someone at the sysadmin level could create another sysadmin account which by default would have access. Is there a way to make the default permssion for an object 'deny'?
jiffy_79
jiffy_79
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 228
I'm thinking my two best option are:

Remove sysadmin access and lock down the views/functions/certs/keys
or
Get some kind of external solution programmed

As you mentioned I'd rather get the sysadmin access toned down, time to do some digging on why there are in there to begin with....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search