Encryption
Barrie Wilson
What is the absolute easiest way to encrypt/decrypt a column in SQL Server
2005, consistent with a reasonable level of security? The column I need to
encrypt is char(18) if that makes a difference. No, I'm not sure how to
define "reasonable" here. I'm pretty confident the DB is secure against
intrusion but I cannot leave that column out there in plain, readable text.
TIA
BW
Uri Dimant
http://blogs.msdn.com/lcris/
"Barrie Wilson" <bwi...@nowhere.com> wrote in message
news:13l9mnp...@corp.supernews.com...
Barrie Wilson
"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:uI%23z7gjN...@TK2MSFTNGP06.phx.gbl...
> Barrie
> http://blogs.msdn.com/lcris/
Thanks, Uri ... but that's a long, rambling blog and I didn't really see
anything I could get my hands on and use
Is there not a relatively simple way to encrypt a column?
ML
throughout the solution, and not even the DBA can decrypt it.
Or you could give us more details regarding your business case. Especially,
why you think you need to encrypt data in the data store.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
Barrie Wilson
"ML" <M...@discussions.microsoft.com> wrote in message
news:40588A68-304E-4968...@microsoft.com...
> Encrypt the data in the client application. That way the data is secure
> throughout the solution, and not even the DBA can decrypt it.
that's a possibility but there's a lot of unencrypted data on the server
already and rather than write a one-off app to encrypt it, I thought i might
be able to do it without too much pain from QA or SSMS .... a la:
UPDATE table SET sensitiveColumn = xEncrypt(sensitiveColumn, 'key')
with a simple UDF or xSP perhaps ... it's millions of rows that I don't
really want to round trip if I don't have to
> Or you could give us more details regarding your business case.
> Especially,
> why you think you need to encrypt data in the data store.
because if the data gets out, the client could spend the next five years
settling lawsuits ... and worse ... I don't know how many people are aware
of the growing numbers of states which have enacted statutes which are
**absolute hell** on companies that fail to handle sensitive data properly
... have a look at TJX's woes, for one ....
it all makes for a totally *compelling* business case; spend < $50k to
secure the data or bet the enterprise
ML
If you want to limit data access, use security (logins, users, permissions).
If you want to monitor data modifications and/or data access, use auditing.
If you want to prevent data theft (and presuming physical security is in
place), you can encrypt the data files (see this example:
http://milambda.blogspot.com/2007/05/presentation-slides-and-enabling.html).
Data encryption should only be used to protect highly sensitive data, and in
real life not *all* data is highly sensitive.
A good security model should be enough.
Anyway, encrypting data in the data store is IMHO an exercise in futility -
why encrypt data *after* it has travelled all the way from the client
application to the data server in clear text? Just so that we can waste more
resources when we have to decrypt it for data retrieval?
No offense, but if you want to properly safeguard your data then looking at
the data server is simply not enough. Security should be an integral part of
the solution as a whole.
And to answer your question regarding whether there's a built-in function
available that would let you encrypt individual columns - no, there isn't
really. All built-in encryption functions accept data in clear text and
return cipher text in the varbinary type. Ergo, you're looking at redesigning
the data model.
If you're still considering using data encryption look at the blog that Uri
suggested in his post.
Barrie Wilson
>> Data encryption should only be used to protect highly sensitive data, and
>> in
>> real life not *all* data is highly sensitive.
obviously ... but I am talking about highly sensitive data; moreover, you
and I don't get to say what constitutes highly sensitive data
>> Anyway, encrypting data in the data store is IMHO an exercise in
>> futility -
ya think? here's the deal:
your org has 9.7 M rows of data in a table with column combinations which
enable identity theft; you decide not to encrypt anything in the physical DB
because it's "futile;" your consultant from Ernst & Young says she has to
have a copy of the table, the CTO approves it, the data ends up on her
laptop, and her laptop gets stolen at the airport ...
One really angry customer of your org who's been put to a lot of pain as a
result of all this comes to my law office and wants to know what can be
done; I find that thousands of other people were also adversely affected
and file a class action lawsuit; in discovery I get a copy of your DB and,
ultimately, at trial, I show the jury a snapshot of the table mounted on a
4' x 6' foam board clearly showing name, address, SSN, DOB and one credit
card # ... I call you as a witness and you verify that indeed this is your
org's table and testify that the data is not encrypted because it would have
been "futile." Next, I call a crypto expert and ask him if this data
*could* have been encrypted; he says, "of course it could have been
encrypted." Then, my security expert testifies that encryption of the data
would have substantially reduced the risk of any adverse consequence to the
plaintiffs.
In closing arguments, I tell the jury that because one DBA decided, with the
approval of his superiors, not to do something he considered "futile" but
which experts say would have mitigated or eliminated substantial risk,
almost ten million people have suffered actual and prospective financial
loss, loss of reputation, mental anguish, etc and so on. Your counsel tells
the jury, "well, this is a complex problem ... there are no easy solutions,
there are multiple points of vulnerability, it would have cost a lot of
money to protect the data, etc etc etc"
The jury retires to deliberate the matter with one thought in mind: your
org **could have** encrypted that data so that they saw
"et6638jjsdgg338882mjhhhss" instead of "4566 3322 8882 5729" in the column
labeled "Credit Card #" up on that foam board exhibit, you chose not to, and
real people were hurt as a result.
What's futile is spending any time at all speculating on the outcome: your
organization just lost a *lot* of money in the short term, suffered a lot of
reputational injury in the long term, and if you're really short on luck,
you're organization goes underwater completely. If your counsel is smart,
they settle this whole thing long before it goes to a trial and you hire the
best PR firm money can buy to do damage control.
I say, when you're handling sensitive data, as defined in terms of legal
consequences, you encrypt at the data store ... in **addition to** taking
all other necessary security measures. Why some people think otherwise, and
why DB vendors have not made this a friction-free operation embedded in the
DB engine, is utterly and completely beyond my comprehension.
You don't get in hot water for failing to prevent all data leaks; you DO
get in hot water for failing to do reasonable things.
SQL Menace
Take a look at http://sqlblog.com/blogs/denis_gobo/archive/2007/11/13/3310.aspx
before deciding to encrypt from within the DB
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
Hugo Kornelis
>
>"ML" <M...@discussions.microsoft.com> wrote in message
>news:40588A68-304E-4968...@microsoft.com...
>
>> Encrypt the data in the client application. That way the data is secure
>> throughout the solution, and not even the DBA can decrypt it.
>
>that's a possibility but there's a lot of unencrypted data on the server
>already and rather than write a one-off app to encrypt it, I thought i might
>be able to do it without too much pain from QA or SSMS .... a la:
>
>UPDATE table SET sensitiveColumn = xEncrypt(sensitiveColumn, 'key')
>
>with a simple UDF or xSP perhaps ... it's millions of rows that I don't
>really want to round trip if I don't have to
Hi Barrie,
Since encrypting changes the datatype, you'll probably end up adding
columns for the encrypted data, issuing an UPDATE statement similar to
the above to populate them, then dropping the unencrypted columns from
your tables.
>> Or you could give us more details regarding your business case.
>> Especially,
>> why you think you need to encrypt data in the data store.
>
>because if the data gets out, the client could spend the next five years
>settling lawsuits ... and worse ... I don't know how many people are aware
>of the growing numbers of states which have enacted statutes which are
>**absolute hell** on companies that fail to handle sensitive data properly
>... have a look at TJX's woes, for one ....
>
>it all makes for a totally *compelling* business case; spend < $50k to
>secure the data or bet the enterprise
If security is that important in your business, then you'd better make
sure that you get a very thorough understanding of security. The "long,
rambling blog" that Uri pointed you to is a great resource for exactly
that, since almost all posts made there are about security, and often
about encryption as a means to increase security. You'll also wannt to
spell out everything you can find in Books Online about security and
encryption.
Maybe you should pick up some books as well. I found the chapter on
security in Adam Machanic's Expert SQL Server 2005 Development to be
very enlightening, but in your case you might need to go for a more
specialized explanation in a book that is completely dedicated to
security.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Barrie Wilson
Hugo,
I also might want to bring someone in who actually knows a lot about crypto
on SQL tables; your points are taken .. the problem is I'm looking at a
situation right now where some kind of action needs to be taken .. look, I
get a little impatient with people jumping into newsgroups with "urgent
requirements" too ... OTOH, there are times when you really do have to put
something in place fairly quickly and there just isn't time/resources to do
everything as you might otherwise like to .. and in those cases I want to
travel on the conservative route; protect first, perfect later ... plain
old triage
BW
"Hugo Kornelis" <hu...@perFact.REMOVETHIS.info.INVALID> wrote in message
news:4utdl3la53jdiocmh...@4ax.com...
Barrie Wilson
"SQL Menace" <denis...@gmail.com> wrote in message
news:cd00dfb0-37ed-43e0...@e1g2000hsh.googlegroups.com...
> Barry,
>
> Take a look at
> http://sqlblog.com/blogs/denis_gobo/archive/2007/11/13/3310.aspx
> before deciding to encrypt from within the DB
>
> Denis The SQL Menace
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
DTM:
sure, reasonable points ... but Gobo is saying, "do it right" not "don't do
it" ...
BW
SQL Menace
On Monday a co-worker got a letter from that data center informing him
that one of the stolen servers had his information on it.....the world
is small indeed
With the new law about to being passed I would make sure I get it
right if not it might cost you later
But yes encryption in the DB is still better than no encryption
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Dec 5, 2:45 pm, "Barrie Wilson" <bwil...@nowhere.com> wrote:
> "SQL Menace" <denis.g...@gmail.com> wrote in message
ML
system. :)
When I say futile, I mean that the built-in encryption functions SQL Server
has to offer do not actually provide data encryption but rather data
obfuscation. The SA or the db_owner can both access data that was encrypted
using built-in functions (unless the data was encrypted using a user-provided
password - which means that as soon as the user forgets the password used to
encrypt the data this data is lost). Further more, the data needs to be
decrypted on retrieval.
That's the point I was trying to make.
I'm not saying you shouldn't use encryption, I'm saying you shouldn't use
encryption as the sole method of providing data security.
Judging from your other posts I see now that you're fully aware of this and
are simply looking for a way to improve the safety of your existing solution.
So, have you considered my suggestion of encrypting database files? (That
would cover the case of HW/file theft.)
Hugo Kornelis
>
>
>Hugo,
>
>I also might want to bring someone in who actually knows a lot about crypto
>on SQL tables; your points are taken .. the problem is I'm looking at a
>situation right now where some kind of action needs to be taken .. look, I
>get a little impatient with people jumping into newsgroups with "urgent
>requirements" too ... OTOH, there are times when you really do have to put
>something in place fairly quickly and there just isn't time/resources to do
>everything as you might otherwise like to .. and in those cases I want to
>travel on the conservative route; protect first, perfect later ... plain
>old triage
Hi Barrie,
I understand the pressure you're under, but I believe that this is truly
a case where you'd better do it right than do it quick.
I think I know more about SQL Server encryption than you do. But if I
were hired to set up encryption for highly sensitice data of a major
corp, I'd politely refuse the assignment (maybe *because* I know more
about it? i.e., I know enough to know that I know too little).
When you encrypt data, you need to store the encryption keys. The
typical place to store them is ... in the database! So you have to
protect the encryption keys themselves, which ends up as a chain of
keys, database master keys, server master keys and such. I always get a
headache well before I begin to understand this stuff.
Anyway, the two risks you are facing when implementing encryption
without proper understanding the subject beforehand are:
* too weak protection, somewhere in the chain of keys protecting keys
protecting keys; the result will be that IF the data ends op being
stolen, the thieves can decrypt embarrassingly easy;
* too strong protection, along that chain; the risk of this is that
someday something unexpected happens and you find that you lost a key
somewhere and are now no longer able to decrypt the data. *Poof*, all
your company's data gone. Ouch.
Like I said, I would refuse an assignment to set up encryption of
sensitive data, because I am aware of these risks and not sufficiently
versed in encryption and key protection to feel comfortable that I can
prevent such scenarios.