February 7, 2014 at 5:05 pm
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.
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
February 7, 2014 at 6:10 pm
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...
February 7, 2014 at 6:13 pm
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.
"
February 7, 2014 at 6:19 pm
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).
February 7, 2014 at 7:47 pm
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 ...
February 10, 2014 at 7:29 am
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.
February 10, 2014 at 10:15 am
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.
February 11, 2014 at 8:00 pm
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
February 12, 2014 at 9:34 am
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.
February 12, 2014 at 10:47 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply