November 23, 2009 at 11:02 am
If one encrypts their backups and their database itself, does that really do any good if the "hacker" has physical access to the server?
You could remove local admins from the SQL sysadmin group, but they could rather easily reboot into single user mode and grant themselves the permissions they needed to get back into the sysadmin group.
So is there any benefit of encrypting the database to prevent someone with physical access from doing "bad things"?
Encrypted backups are great of course, and I would imagine encrypting data that may be stored on a less than perfectly secured SAN is also a huge plus, but what about physical access?
November 25, 2009 at 2:21 am
The implementation of security is a multi-layered activity and there is no one solution that will solve all attacks.
My understanding of SQLServer2008 TDE featurte is that it prevents a physical media attack. That is if either the disks or backup media is stolen then it cannot be used.
I can not see how TDE can help once a hacker has got into a SQL Server with admin rights.
From your point of view of a hacker gaining access to the box then I feel that there are other security holes that needs to be addressed in terms of "how did they get in?" and "how did they get to impersonate the admin accounts with their associated privileges?".
November 25, 2009 at 10:38 am
Makes good sense, thanks!
November 25, 2009 at 11:53 am
Good answer and I don't think you left anything out that I was going to say..
Except a little philosphical point. Multi-layered security is called defense-in-depth, you can't uterly prevent an intrusion, you can make it harder and buy time for discovery, but if your computer connects to anything else then it can be compromised.
Many corporations have what is described as a crunchy outside with soft chewy center, meaning that once you get in the front door, you are basically done with your breach, it is smooth sailing from there. Think of an M&M..
Physical access is perhaps one of the easier to prevent since most bad guys these days don't want to risk exposure so it is MUCH safer to hide behind their keyboards.
CEWII
December 10, 2009 at 10:52 am
Agreed.
If someone has access where they can query the database TDE does nothing, you could encrypt columns within the tables themselves and that would obscure the view. then you would also need functions and procedures to decrypt those columns.
The big plus of TDE is that all files at REST are encrypted, as well as the backup files and the .ldf and .mdf files. as well as the .trn files.
If someone hacked into your server and you had excluded the built in admin group, and they were only able to steal files, then they would not be able to re-attach mdf files without the certificate used to encrypt that db.
even if they get a copy of your certificate you can encrypt the certificate backups by password. I have a seperate DB that I keep my passwords in that are encrypted using TDE. my nightly certificate backup job uses a function to get that password so that I don't keep the password in plain text anywhere on the server.
If you use the AD groups to assign membership in your server then you just add to the depth that a hacker would need to go to get your data.
Keep in mind that data in the mdf file are kept in plain text and if you do not encrypt .bak files and .mdf files then your sensetive data or PII stored in a database server exist un-encrypted on your file structure.
here are 2 great articles about TDE.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
December 11, 2009 at 6:18 am
snu155 (12/10/2009)
If someone hacked into your server and you had excluded the built in admin group, and they were only able to steal files, then they would not be able to re-attach mdf files without the certificate used to encrypt that db.
If the Hacker is able to get privileged User account, there is little security for you. You probably have SQL Server running under service account. It is not difficult to change the password for Service account and log in with that. The best defense is actually your outer perimeter. Once a hacker is inside with a privileged user, you are at their mercy. Yes, Multi level security model is the best way to go.. But your Outer perimeter is your best defense.
-Roy
December 11, 2009 at 6:29 am
I don't dispute that for a second, however unless you are in a small shop most places have a network team that would prepare the out defenses and would establish the routers and networking equipment.
there may even be teams that handle the server builds and the physical boxes.
but as a DBA you look at your environment and say okay if they made it this far, (and obviously there is a problem if they made it that far...a glaring, problem) what can i do to further secure my environment.
I've worked in small shops where there were only 2 people, I've worked with massive enterprises where we have 100s on just the Network Staff.
Your role will be limited or increased by the business rules of your environment, but when you look at the Database there are always things you can do so it is not a cruise home after someone gets to that point.
You should be using AD service accounts, they should be unique per Engine and Agent, you should use strong passwords and change them 120 days, you should have encryption enabled, you should have alerts configured, you should not have development tools on a production server, you should not store passwords in plain text files.....the list is endless, but we have all seen simple things left to the wayside that would make a difference (maybe only minutes, but it is better than a free pass).
And let's be honest as a DBA our butt, job, and reputation is on the line if something is compromised and we didn't do everything in our power to ensure our environment was safe.
I agree 100% the outer perimeter is what will and should keep you safe, but the servers shouldn't be a cake walk to get through if and when a system is compromised.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
December 14, 2009 at 8:27 am
I have been considering TDE, but nixed the idea when I read that once the data is encrypted you have little or no ability to compress it. If your DB is tiny then TDE is useful, but when you have DBs from G to T, you are likely to desire compression in your backups at some point. I plan to enable encryption in my backup tool, when encryption is needed.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
December 14, 2009 at 8:32 am
That is a very good point, of the databases that i have encrypted not only does it nix compression, but it increases the size of the database as well. for very large databases that has to be watched. of course it also helps to have filegroups, use partitioning, to be able to shape the restore strategy of vldb, but turning it on a vldb would take some time and could generate file growth.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
December 14, 2009 at 9:09 am
You could argue that when you dbs are TB sized, they might be more valuable and more in need of protection. I just added 4TB to my home network for $280. I know SAN space or corporate drives are more expensive, but at what cost? Is there a need to secure things and for another $1k a year, is it worth not compressing?
Not saying it is, but examine the data rather than make the trafeoff that compression is more important. Or have someone in legal decide.
You ought to implement good security inside, especially as you grow. I think it's more likely that someone could pass unnoticed into a high security environment (http://www.cbsnews.com/stories/2009/11/26/eveningnews/main5791444.shtml). In a small company, someone would be more easily noticed. In many large companies, you don't know everyone.
The inside security helps with the insider issues. you can't necessarily protect against a rogue DBA, but perhaps from a rogue accountant.
December 14, 2009 at 9:31 am
Steve Jones - Editor (12/14/2009)
You could argue that when you dbs are TB sized, they might be more valuable and more in need of protection. I just added 4TB to my home network for $280. I know SAN space or corporate drives are more expensive, but at what cost? Is there a need to secure things and for another $1k a year, is it worth not compressing?Not saying it is, but examine the data rather than make the trafeoff that compression is more important. Or have someone in legal decide.
Yeah, me too. That was a great bargain on those drives, huh?
But even if you aren't worried about size, there are only so many hours in a day, and the additional advantage of compression I have experienced, at least with SQLSafe, is that the time it take to generate the backup is significantly decreased when using compression, which is a big advantage with a VLDB.
In my new position I have found a 4.2TB DB on one system that has not been backed up since it was 1.6TB, because it takes such a long time to do, and disk space is at a premium. This is a prime candidate for compression, if possible. I will have to test it because the DB contains BLOBs, which hit you in the compression like encryption does.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
December 14, 2009 at 10:56 am
Stamey (12/14/2009)
I have been considering TDE, but nixed the idea when I read that once the data is encrypted you have little or no ability to compress it. If your DB is tiny then TDE is useful, but when you have DBs from G to T, you are likely to desire compression in your backups at some point. I plan to enable encryption in my backup tool, when encryption is needed.Chris
Maybe you should think about Table level compression. Let us say that you have a table that has million and millions of row. If you use table level compression, you could reduce the size of the table drastically. Keep in mind that compression has a little bit of overhead. If it is not a table that is read regularly (Like a log table) then you could go for it. The advantage is that the size of the Back up reduces by using this method. Then even if you have TDE enabled, this could reduce the back up size. (I have not tested this scenario but theoretically this should work)
-Roy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply