Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Encryption Expand / Collapse
Author
Message
Posted Friday, February 07, 2014 5:05 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
Hi,

I have several databases in SQL Server 2005.

I need to encrypt does databases because I don't want that the data inside them can be seen by the client.

The client is the owner of the servers (windows servers) and has domain admin permissions on all servers.

To protect the databases I need to encrypt the databases so that even they having windows full permissions still they cannot see the data.

The goal is that they can only work (insert, update or delete data) if they use my vb.net and vb 6.0 applications.


The questions are:

I don't know if it is better to choose SQL Server Level Encrypation or Database Encryptation.

I read on http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

That encrypt the entire database causes over head. I'm using massive aplication load operations like bulk insert.

Do I gain anything in converting the database from SQL Server 2005 to SQL Server 2012?

Do I have any type of encrypation in SQL Server 2012 where I can encrypt the entire database or Entire SQL Server Instance without causing overhead?


Thanks
Post #1539445
Posted Friday, February 07, 2014 6:10 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
I saw TDE encryptation in SQL Server 2008 which could be the answer to my questions but then I read this:

"TDE is not a form of access control. All users who have permission to access the database are still allowed access; they do not need to be given permission to use the DEK or a password."

Does this means that If a user has db_owner in the database he can decrypt the database and read the information?

I thought that they could not read the information...

Post #1539454
Posted Friday, February 07, 2014 6:13 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
I want that no one can read the database. They can only read the information on the database through my applications (vb.net and vb 6.0)

Can they decrypt (to read the database) if they are database owners?

"TDE is not a form of access control. All users who have permission to access the database are still allowed access; they do not need to be given permission to use the DEK or a password.

"
Post #1539455
Posted Friday, February 07, 2014 6:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 2,763, Visits: 5,907
You need to have 2 types of users for your databases:
Admins & Users.
Admins (you and anyone else that should be able to work with the database directly) should have full (or almost full) permissions.
Users should be able to impersonate an app role to select and execute.
No need for encrypting the database for this thing (other reasons might apply).
Study about managing security on SQL Server (Users, Roles, Securables, Permissions, etc).



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539456
Posted Friday, February 07, 2014 7:47 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
Sorry, I was not clear.

I need to find a way of encrypting the data because I cannot prevent the client from creating SQL or windows users with full permissions on SQL Server.

The databases are mine but the servers belong to them.

I need to encrypt the data so that they cannot see what is inside even if they have full permissions on the database.

How can I do this?

TDE does not seems to help on this because every body that has permissions on the encripted database can do a select stamenet through query analiser and will get the data decrypted ...
Post #1539458
Posted Monday, February 10, 2014 7:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 2,763, Visits: 5,907
You might be in serious problems, or you might just need to follow my original advice.
If you can't prevent users from accessing the database, you will definitely won't be able to encrypt it without allowing them to decrypt it. Someone has to protect the DB, if it's not possible, a disaster will come soon.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539759
Posted Monday, February 10, 2014 10:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 221, Visits: 1,104
Sounds to me like you want application roles. The access granted by an app role is only alive as long as the thread from the applicaiton is alive. So they would only have 'update' while using the app role granted by your application.
Post #1539856
Posted Tuesday, February 11, 2014 8:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:10 PM
Points: 424, Visits: 1,543
If I got you correctly- real life scenario:
1) 1-st server 2008- uses TDE- everybody who has access can see real data;
2) 2-nd server 2005- we use 3-rd party app to encrypt only some columns in some tables. Real data (decrypted) can be visible via same application we use to encrypt data
Post #1540534
Posted Wednesday, February 12, 2014 9:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 845, Visits: 2,331
Bottom line: If you actually want security, you need to store the encryption keys on something you own, not something the client owns. Expect to spend a lot of money and time, and hire (at least) two experts - one to design, another to review the design. Alternately, trust your clients.

river1 (2/7/2014)

The client is the owner of the servers (windows servers) and has domain admin permissions on all servers.

To protect the databases I need to encrypt the databases so that even they having windows full permissions still they cannot see the data.


You're pretty much done, here, with anything inside SQL Server. The client owns the machines, and thus the files and thus the Master database, so they can (either live or off of restores/copies/clones) always become sysadmin. TDE doesn't protect from sysadmin unless the keys are lost and it protects from everyone. They also own the network, so even using TDE encrypted by an asymmetric key encrypted by a password means they can sniff the password off their own network.

If you want only your app to see decrypted data, and you want to enforce that, then you need to do the encryption in your app. If that also runs on client equipment, you're getting into hard key management problems, since the client owns the drives, the OS, can look at your program at rest and while it's executing, and even the RAM is theirs if they want to get into more interesting attacks. Note that in the above password example they can also pull the password out of your app's code.

These problems are solvable, but typically with a smartcard, dongle or HSM (hardware security module) either doing key management, or doing the actual encryption/decryption for you. Note that this will irritate your users as well, and if it breaks on some upgrade or patch to whatever, they're going to be even more upset.
Post #1540860
Posted Wednesday, February 12, 2014 10:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:04 PM
Points: 32,780, Visits: 14,939
TDE will not work, for the reasons Nadrek gives above.

You can encrypt data in the tables with symmetric key encryption, and store the keys in your application. USing temp keys, i.e. create symmetric key #mykey, will give you some protection from the clients. However, as Nadrek says, they can read into RAM, depcompile things, or otherwise find the key. Most clients won't, but it is possible.

However, if you're trying to prevent the clients from accessing the data outside the application, it's much simpler to write that in the contract for the application. Doing it in code will slow down your server/application, you're giving them the data anyway in the application, and you're potentially just causing yourself headaches for no reason.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1540909
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse