April 1, 2011 at 10:06 am
Good day,
I'm trying to understand what (our implementation of) SQL's encryption protects against. I followed what seemed to be the most suggested route of a Database Master Key protecting a Certificate protecting a Symmetric Key.
Imagine a simple web app / database storing sensitive data. The web user requires CONTROL on the Certificate and VIEW DEFINITION on the Symmetric Key in order to encrypt the data. But that same permission allows the user to decrypt the data. So, if my concern is that user getting through the app and into the database, say by SQL Injection, then my data is not protected at all.
Am I thinking about this wrong or is there a better way to implement encryption?
Thanks.
April 1, 2011 at 10:40 am
Think in terms of granting execute permission to s stored procedure by some basic web user (app).
The user does not have access to certificates and keys directly, only granted execute on a procedure that can update or retrieve data specific to that user via parameters.
The probability of survival is inversely proportional to the angle of arrival.
April 1, 2011 at 10:43 am
You're not protecting against an application hijack with encryption. However, if someone gets direct access to the database, or backups of it, they won't be able to get to data they aren't supposed to.
I've always seen it as more useful on laptops and such, or in backup media that get transported from place to place, than on your servers. On the server, it can, theoretically, protect against a rogue DBA who isn't supposed to have access to data, but does have access to database maintenance and such. I'm not sure it really does that, and C2 audits are a better bet for that kind of thing.
But if you have backups being put on tape, and the tapes being transported off-site, encrypted data can protect against those getting misplaced/lost/stolen/whatever.
That's how I see it, anyway.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2011 at 10:45 am
I have asked the same question myself. OK, so your DB is encrypted. What exactly does that protect you from? I suppose it protects another app, like SSMS, from some other machine from running queries against your data. There may be situations where this is a valid concern, but I have not run into one yet. Since you usually can trust the network segment the SQL box is connected to, you can limit access to the box from the vast majority of untrusted sources. Now the question becomes, can untrusted people/computers access the SQL box from within or through the trusted segment of network? Again, it is possible for the most savvy infiltrator, but if they can get to the SQL box don't you have a bigger problem to begin with?
You want the backups protected so if they fall into the wrong hands the data will not by useful to them. Another valid scenario, but this can be accomplished with backup software that supports encryption. I would say it would be safer than worrying about that encryption key because if you have to move said backup to a different server the key has to go with it, and that could be problematic with some of the places backups have to go (legitimately).
These are things I brought up to a former employer when they said they wanted all of the SQL Server DBs encrypted, it was their new policy. When I posed these scenarios to them they did not know what to say. They had heard "encryption" and considered nothing about the implementation or maintenance of it.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
April 1, 2011 at 10:58 am
Encryption is only as good as the key management mechanisms and how the other security on the server is implemented. They are all layers of protection and need to be viewed as a series of interlocking systems. If you lock your door but leave the key under the front doormat you have done nothing to secure your property.
Having said that, even if the implementation was done poorly, there is still a reason: liability. If your company has a data breach and it is discovered you did little or nothing to prevent or mitigate the data being released your company can be facing lawsuits or fines or you could lose you job at the very least.
The probability of survival is inversely proportional to the angle of arrival.
April 1, 2011 at 11:09 am
Think in terms of granting execute permission to s stored procedure by some basic web user (app).
Thanks for the response. I had tried that earlier and it did not work, thus prompting my question. Wrapping the decrypt functions in a stored procedure still requires the above mentioned permissions.
However, your reply made me think deeper and I realized I could use WITH EXECUTE AS OWNERto execute the proc under a different context and thus only have to grant EXECUTE on the proc.
April 1, 2011 at 11:45 am
Regarding backups, I did some quick testing...
I restored the backup to a different server. To use the Certificate and Symmetric Key I first had to open the Database Master Key using the same password I used to create the key (which presumably the thief would not have). So on the one hand my backups are protected to the degree I used a strong password. However, on the other hand, according to BOL, the Database Master Key is stored in the database, so I wonder if the password can somehow be extracted from the mdf file?
April 1, 2011 at 12:32 pm
Henry_Lee (4/1/2011)
However, on the other hand, according to BOL, the Database Master Key is stored in the database, so I wonder if the password can somehow be extracted from the mdf file?
Given that a malicious individual has access to your database and has a lot of time on his hands it is certainly possible. The point is that you have made it exceedingly difficult... perhaps buying some time so that you can do some damage control.
Finally, you are not required to use the mechanisms built into SQL server for key management. In fact you don't even have to use the built in encryption functions, you can create your own and implement them in CLR. This is the route we decided to take.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply