Encryption in SQL 2008 Standard Edition 64 Bit

Question

Encryption in SQL 2008 Standard Edition 64 Bit

danielle.shaw

SSC Veteran

Points: 253
More actions
July 3, 2012 at 3:33 am

#263522

Hi,
Over the past few days I have been investigating encrypting a database under the above version of SQL. I understand from my investigations the TDE is not available under SQL 2008 Standard but why do I have the manage database encryption option under my databases? I need to know as my boss will want to know why?
Thanks in advance
Danielle Shaw

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

Its a GUI thing.

MS have simplified the GUI so that you dont need 1 GUI for Standard Edition, 1 GUI for Enterprise Edition etc etc, so you will see options which relation to all versions in one GUI.

What are your requirements for encryption? Only a few cells need encrypting? Encryption needs to be at the data level (so when you do a select from a table the information is encrypted)? No changes to existing applications? Backup encryption so you cannot restore the DB to another server without the keys?

danielle.shaw SSC Veteran Points: 253 More actions · Answer 2

Many thanks for replying. The reason my boss wants to use encryption is so we, the SQL Server admins, can't see the confidential data in the databases on the SQL server. But I believe, and I may be wrong here, that the only way to do that is to encrypted the columns?

Lowell SSC Guru Points: 323518 More actions · Answer 3

you are correct.

a SQL admin has access to all the data, so unless the data is encrypted at the column level, the admins have access to it.

even then, if the admin knows the encryption algorytm, then he could decrypt the column data as well.

SOMEONE needs to know how to decrypt the data; at some point it's going to come down to who you trust.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

danielle.shaw SSC Veteran Points: 253 More actions · Answer 4

Many thanks for your reply, it has been really useful

Thanks

Danielle

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 5

You can encrypt the column data a few ways, but they usually require some application changes. There is column level encryption using symmetric and asymmetric keys in SQL Server. Typically this would require storage of the key inside the database, which is a problem since DBAs can access all objects.

However, the decryption keys can be protected by a password. This can be entered on the front end, by users, and the DBAs would not have access to the data. You could also use temporary keys (CREATE SYMMETRIC KEY #MYKEY ...) from the application and never store them in the system.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Personally I like a multiple approach to the issue. A little security by obscurity by creating a extra keys and then only using one or two, and obscuring which one is used, along the lines of creating 10 keys that I don't care about and 1 I do.

Also constructing the password to a key in two parts, one part in the database or in the sproc itself and another part passed in so the entire key cannot be read from the database. Encrypt the stored procedures (which is little more than a hinderance) to slow down an attacker a bit..

But I guess I'm paranoid that way..

I believe this qualifies as defense in depth..

CEWII

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 7

That's great, Elliot. Extra keys. Hadn't thought about that. Do you use procs then for encryption/decryption? If so, doesn't that defeat the purpose of the extra keys?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Yes, I use sprocs, but you pass in a value that is then hashed and then the hashed value is used to select the right key. So even the sproc doesn't know what the correct unhashed value is when selecting a key, the caller has to. So reading the DB code will not tell you which key is used, you can see all the hash values but you can't know which one is the right one.

I also use views that display the decrypted information within the session that unlocks the key.

The way encryption works is that a key is opened in a session, the other sessions don't have access to the key even though its opened. So a session that opens a key can then query the data through the view which returns it decrypted, but only if the key is opened, otherwise it returns NULLs for the values.

As you can guess, I spent a LOT of time playing with this..

I have a process and overview paper I wrote last year, I'll have to see if I can release it.

CEWII

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 9

Elliott Whitlow (7/3/2012)
Yes, I use sprocs, but you pass in a value that is then hashed and then the hashed value is used to select the right key. So even the sproc doesn't know what the correct unhashed value is when selecting a key, the caller has to. So reading the DB code will not tell you which key is used, you can see all the hash values but you can't know which one is the right one.
I also use views that display the decrypted information within the session that unlocks the key.

Love to read more.

Article?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

That sounds like a good idea. I have all the code and the paper and process already written, prettyfy it. I'll see what I can do..

CEWII

vikingDBA SSCertifiable Points: 5356 More actions · Answer 11

Elliot, very interesting. I hadn't thought to use a two-part key. Even though one part is passed over the network to the database, it is only part of it, and the other part is in the SPROC. That way, neither party knows the full key.

One thing comes to mind, though. Have you tested that PROFILER doesn't see the SQL Statements being used in the SPROC (SQL: Statement Starting), thereby seeing the full key value when utilized?

If this doesn't show it, you may have an interesting way of separating the keys so no one group can access the data without the full key.

However, if that captures it, then any DBA running Profiler would be able to capture the key.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Excellent point about over the wire, however if that is a concern you can force SQL to use an encrypted pipe, I've done it, it isn't TOO painful..

As far as what profiler gets, it DOES show the password in clear text. Encrypting the pipe will not change what is traced, I tried it.. also there is a MS Connect item right on target http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=444591..

I do think its important to realize that no matter what methods you use if the attacker has full access to the SQL server machine and the web server he is gonna have the ability to decrypt the data. And that encryption and methodologies exist to mearly slow them down.

I am having a paper peer reviewed on this topic now.

CEWII

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 13

SSL isn't too bad. Easy procedure in BOL for setting it up. Do not use a self signed cert. Get a real one.

Also, IPSec works well if you coordinate with the network/sysadmin people.

vikingDBA SSCertifiable Points: 5356 More actions · Answer 14

I mentioned Profiler because the OP stated that his boss didn't want the SQL Admins to have access to the data. If the SA has access to Profiler, he can get the key as it is sent to the engine, then can use that key to look at the data.

Therefore, there is no way to stop SA from being able to see all data in this scenario.

We have the same type of issues here. No resolution yet.