Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1140 > unrolled thread

encrypt, not encrypt, why encrypt and how encrypt?

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  encrypt, not encrypt, why encrypt and how encrypt? "Mojo" <please@dont.spam.com> - 2012-06-21 20:00 +0100
    Re: encrypt, not encrypt, why encrypt and how encrypt? Erland Sommarskog <esquel@sommarskog.se> - 2012-06-21 23:27 +0200

#1140 — encrypt, not encrypt, why encrypt and how encrypt?

Hi All

I know what I'm about to put down is probably more theorectical than an pure 
Db prob, but I don't know where else to post!! :0)

Basically I've created a classic asp web app that connects to an sql 2008 
express db via ssl and even though the whole sys runs on/through ssl I've 
been told that I should encrypt certain parts of the db's content just in 
case anybody gets onto my server and hacks into the db.

Now I started to use an old Base64 encryption with a key bit of code that 
I've had for a bit, but somebody told me that base64 just converts the text 
into a better transport method rather than actually encrypting it and its 
easy to hack, but I've put a long key in and it doesn't seem to convert back 
and forth properly without knowing the key - are they right??  Should I be 
using something else?

Having started to encrypt certain parts, eg a person's name, dob, etc, it 
suddenly dawned on me that although I'm encrypting and decrypting as I go if 
I want to do search queries then it ain't gonna work.  For example if I want 
to find all the people with 'gar' in their name then this isn't going to 
work and if I want to find all the people who are born between Apr and May 
then this isn't either.

My second query is, if I've got the dbs on a dedicated server running only 
one site, loads of password access only stuff and on https do I really need 
to encrypt db fields as well??  If so, how do I get round these query (and 
sort order) issues??

Thanks

M 

[toc] | [next] | [standalone]

#1143

Mojo (please@dont.spam.com) writes:
> Now I started to use an old Base64 encryption with a key bit of code
> that I've had for a bit, but somebody told me that base64 just converts
> the text into a better transport method rather than actually encrypting
> it and its easy to hack, but I've put a long key in and it doesn't seem
> to convert back and forth properly without knowing the key - are they
> right??  Should I be using something else? 

I have never dived into Base64, but I have always thought of it as
an encoding to make sure that binary data is not destroyed in transport.
Maybe there is a key, but it is not likely to be a very strong 
encryption.

And if you are going to encrypt, you might as well use somthing strong
like RSA1024.
 
> Having started to encrypt certain parts, eg a person's name, dob, etc,
> it suddenly dawned on me that although I'm encrypting and decrypting as
> I go if I want to do search queries then it ain't gonna work.  For
> example if I want to find all the people with 'gar' in their name then
> this isn't going to work and if I want to find all the people who are
> born between Apr and May then this isn't either. 
 
Yes, encryption comes with quite a cost in terms of reduced flexibility.

Microsoft has a solution that circumvents the problem: Transperant Data 
Encryption. With TDE, the application can access the database and 
see all data as normal data. But if someone gets hold of the database
file or the backup, all the thief sees is garbage.

There is however quite a serious catch for you: TDE is only available
in Enterprise Edition, which is quite different from Express in a 
budget perspective.

SQL Server also offer encryption functions so that you can encrypt a
column value - and this is avilable in Express. But then you will face
the issues you mention above.

The conclusion is that you should only encrypt data that is really 
sensitive like credit card numbers. If you need to search on 
encrypted columns, you can add a column with a strong one-way hash 
of the value. Then you can search on the hash. Of course, that only 
works if you search for the full value.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web