February 9, 2010 at 9:57 pm
{edit: I only read the first page of replies before I wrote this, TravisDBA and others have already commented on the salient points so take it as the silly comment it probably is }
Ahh, this takes me back to the fits of laughter I suffered (ROFLMAO) back when I first learnt about the SQL2000 security model. As they say, 'it doesn't matter how big your gun is if it's pointed at your foot'.
Now it's been a long time since I was an active DBA and I'm not up with the ins and outs of encryption keys and so on.
So correct me if I'm wrong.
But isn't the upshot of all this that if I copy your .mdf/.ldf files (or even your backup files) and attach or restore them to a SQL server instance on a machine where I am an admin (ie. my laptop), that I basically have full access to do all these things ?
In other words, there is simply no way of securing a database so that it cannot be fully unsecured if a copy falls into the wrong hands ? And with security breaches being what they are in our web-conected world, that makes me nervous. Just having a backup file stolen is not that unlikely a scenario. But hey, none of us would ever leave copies of data um ... er .. say on our dev machines, or on USB keys we carry on the bus which don't remove the files when deleted, just deallocate the sectors, ... without fully AES256 encrypting the files first ? Would we ? Ahem ...
I found it (and still find it) amazing that SQL Server doesn't offer an old-fashioned this-is-the-account-and-password-and-if-you-don't-know-it-you're-screwed type system. Crude, but effective. Security seems to have suffered a kind of postmodern moral relativist fate where it's no longer cool to make something totally, utterly and completely off limits.
As they say, (again), Convenience = 1 / Security
It also really reinforces to me the importance of physical security. As another poster said, a reboot is all it takes to run a patch and clear the admin password. Then you can copy what you want. Half an hour tops if you know what you're looking for.
Hopefully I'm wrong, here, and please put me to rights if that's the case. In particular, I'm not sure what happens in this scenario to the encryption keys when columns are encrypted with that new-fangled feature you youngsters seem to be using these days with the later versions.
B
PS: I AES-256 encrypt all backups and data files as soon as they are generated or copied using 7zip (on the command-line for when files are autogenerated) and a pass-phrase of about 32 characters (I calculated this is about the optimum). The pass phrase is in clear text in the generation file, but I figure the protection is worth it for when the files go walkies as they do nowadays with offsite backups, online backup (carbonite/mozy) systems and such. You never know who is going to get broken into and what files they are going to have. Don't back up the generation script containing the password. Do ensure that a copy of the passphrase is available to anyone who needs it (writing it on paper is usually effective).
February 9, 2010 at 10:07 pm
ben.mcintyre (2/9/2010)
In other words, there is simply no way of securing a database so that it cannot be fully unsecured if a copy falls into the wrong hands ?
TDE (Transparent database encryption) in SQL 2008.
It also really reinforces to me the importance of physical security. As another poster said, a reboot is all it takes to run a patch and clear the admin password. Then you can copy what you want. Half an hour tops if you know what you're looking for.
Absolutely. If you don't secure the physical server, all other security work may be in vain. It's not even copying the files off, there's too many other things someone could do with physical access.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2010 at 10:44 pm
Yeah, thanks Gail.
I was impressed by your two-people-and-bios-password story. What occurred to me when i read it was that although on first glance it sounds over the top, it's actually not that hard or expensive to implement, and it made me realise how easy it is to be lazy. It's a system most large orgs (at least ones storing appreciable amounts of sensitive data) really should have in place (but I'll bet most don't). It's a primarily a cultural thing, about really making sure, rather than a money thing.
The thing that worries me most though is the huge amount of data files flowing around the digital world. It's like an STD warning ad, don't just think about who you slept with but about everyone they've ever slept with. Have you ever given copies of your data to a development company ? Where are their backups kept ? Are they secure ? Where else did those files end up ? Are your offsite backups kept behind locked doors with a similar security to the server ? Are they on a network-connected SAN ? What else is that connected to ?
A lot of smaller scale DBAs don't seem to realise that password protected Access Databases and ZIP files can be cracked with a $20 program you can buy on the net. The only really effective protection is a strong encryption tool (see earlier comments) - again not that hard to do, but an issue of culture change.
One day I'll have time to test all my systems and uprgade them to 2008 🙂
Meanwhile it seems true that an unencrypted stolen file is literally unprotectable.
B
February 10, 2010 at 6:21 am
Ben,
Restoring backups or copying and attaching files as a way of gaining access to someone's data has been virtually eliminated through TDE. If you attempt either operations on an encrypted database you will get this error below:
USE [master]
GO
CREATE DATABASE [TravisDBA] ON
( FILENAME = N'D:\Test\TravisDBA.mdf'),
( FILENAME = N'D:\Test\TravisDBA_log.ldf')
FOR ATTACH
GO
The result of above code is;
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'.
RESTORE DATABASE [TravisDBA]
FROM DISK = N'C:\TravisDBA.bak'
WITH FILE = 1,
MOVE N'TravisDBA' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TravisDBA.mdf',
MOVE N'TravisDBA_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TravisDBA_log.ldf',
NOUNLOAD, STATS = 10
GO
10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
52 percent processed.
62 percent processed.
73 percent processed.
83 percent processed.
94 percent processed.
100 percent processed.
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
You first have to create the certificate on the destination server from a backup file (.cer) that has been saved and encrypted by a strong password on the Database Master Key on the source server, Unless you knew the strong password of the Master Key (or the Cert) on the source server and knew where the cert was backed up, no go. However, as Gail states their are other ways to gain access to physical servers and do damage. Also, as Brian previously stated you could look into memory and get some small snapshots of decrypted pages, but unless you knew beforehand exactly what you were looking at, that would not make much sense either, Security on your db server is kind of like security on your house, There are many points of entry and unless you address them all, you might as well be leaving your front door unlocked. HTH 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 10, 2010 at 8:42 am
Ben,
You bring up good points. There are some protections against copying files (TDE), but physical access is an issue. However these days, lots of remote access allows you to log in with administrator, so physical access isn't necessarily the issue here.
There is a lot of data sloshing around, and my point was that this hole, I think of it as a hole and not a safeguard, means it's easy for someone to anonymously access data. The protections built into the system aren't strong enough, and the auditing easy enough, to prevent this. Or to help the average person secure their systems.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 10, 2010 at 8:54 am
TravisDBA (2/10/2010)
Ben,Restoring backups or copying and attaching files as a way of gaining access to someone's data has been virtually eliminated through TDE. If you attempt either operations on an encrypted database you will get this error below:
I would heartily disagree here. And the main reason I would is that so long as TDE is packaged exclusively in Enterprise Edition, it hasn't been virtually eliminated. There are too many installations of Standard edition because Enterprise Edition is costly. If your org can afford to shell out EE, then it might be. But 3rd party backup packages like LiteSpeed and SQL Backup can go a lot farther to secure the backups (albeit not the data files) than TDE has. And that's because they are affordable.
K. Brian Kelley
@kbriankelley
February 10, 2010 at 9:04 am
True sir, and that is why I said "virtually", not "completely". The definition of "virtually" means "for the most part; or just about:" according to http://www.dictionary.com. I fully realize that TDE on databases is not completely secure in and of itself and I also realize that it is not available in all editions, as well as many other features in SQL Server 2008 are not available in all editions. However, you can encrypt columns of very sensitve data in Standard Edition. I am just pointing out that the feature does exist in SQL 2008 EE and it does go a long way (but not all the way) in addressing copying/attaching the data and log files issue. I also like 3rd party backup tools like Litespeed for compression and encryption as well. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 11, 2010 at 7:21 pm
Steve,
Yes I agree it's a hole (rather than a 'feature'), and as you suggest, not too dangerous on its home turf of the database server, if security is up to scratch.
But then the clincher has been that this same hole means that it's essentially impossible to secure data files or backups (at least before TDE came along). We spend all this time setting up elaborate access systems and windows permission mappings to protect our valuable data, yet if someone manages to get a copy of just one of our backup files, they simply have the run of the whole database. Top to bottom.
It's not just a hole, it's a major design flaw. You could probably argue that MS Access actually has better protection for offsite data files - at least you have to spend $20 on the password cracker !
That has never made any sense.
Travis, Gail,
Thanks for bringing me up to speed with TDE.
I'm a developer and databases are important but not front and centre for me. I haven't even installed SQL 2008 on my dev machine yet. Many of my clients are using MSDE 2000, I'm still upgrading them to SQL2005. I can't recommend SQL2008 until I do serious testing, since there is no migration path backwards. Consider that most of them are primary producers, 4000km from me and often 50km from the nearest town, with one local IT person (if any) to do the upgrade job. Some of them still use dial up internet. Any problems will no doubt come during the peak of the fruit packing season where they are working two shifts a day and downtime is a major problem. Fancy sorting out that tricky database problem by speaking over a crackly phone line with the client's mobile ringing in the background every 30 seconds ? Done it many times before.
Cost ? Substantial. Benefit ? Hmmmm.
I only mention this because I suspect there are many installations like this, rather than the ideal scenario of a large centralised organisation with a dedicated DBA, where of course the migration path could be well managed. SQL upgrades and new features are one of many factors which are juggled as part of the business.
That said, it's great that encryption has arrived, but rather than encrypting the whole database, things would be still be way more secure than they are now if Microsoft would just patch that hole with a (possibly optional) service release.
I don't necessarily want full end to end encryption, I just want a little more security on my data files than nothing at all !
B
February 12, 2010 at 11:07 am
Ben,
Well said, and I tend to agree. The lack of TDE being in other editions, as well as a lack of protection for backup files is an issue.
The remote access is all that's required to get a copy of the backup files, or even MDF.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 17, 2010 at 5:58 am
The startling thing in the editorial, for me anyway, is the line
And if they logged onto Windows as "administrator", you won't know who they are.
That makes me ask why the Administrator account has not been disabled, what are you doing with a group account rather than individual accounts on a supposedly secure server, especially one with high privileges?
Tom
February 17, 2010 at 6:08 am
paul.knibbs (2/9/2010)
Problem is, they're local admins--they can get round stuff like that!
It's extremely difficult, probably impossible, on a properly configured modern Windows system - Group Policy settings can be made so that there is always a trail of some sort, and local admins can't get around it.
Tom
February 17, 2010 at 8:18 am
I'll have to take your word on that, Tom. Not sure how hard or easy it is to secure things, but keep in mind that there are lots of small shops out there, places with 2-3 administrators. I don't know how well, or easy, it is to secure things for them to prevent anon access.
In terms of disabling the admin account, I wasn't aware you could do that. If that's the case and an audit is made when someone turns it on, that closes some issues.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 17, 2010 at 12:45 pm
Steve Jones - Editor (2/17/2010)
I'll have to take your word on that, Tom. Not sure how hard or easy it is to secure things, but keep in mind that there are lots of small shops out there, places with 2-3 administrators. I don't know how well, or easy, it is to secure things for them to prevent anon access.
It's usually quite hard in a very small shop, because the chances are that no-one knows how to do it because in a small shop there's not much need, everyone is trusted, so no-one learns how to do it. I worked in a very small shop for a bit over 8 years until the middle of last year, and at first no-one there had a clue about security (even less idea than I had about Windows security, which was pretty dismal: I'd used Windows before, but never previously been responsible for system security on Windows as I had been on other systems; I'd worked previously on DB security of course, and was frankly appalled by what I found there). By the time I left there was respectable security all round, including some logging that was pretty difficult (but not impossible) to subvert with only domain admin (as opposed to enterprise admin) privileges. Someone who really knew Windows security could have made it foolproof (or at least so I am assured by someone I trust to know, and it looked that way to me as far as I went in examining the detail) if that had been wanted (it wasn't - we didn't want to do anything on the system that we couldn't undo if we really wanted to).
In terms of disabling the admin account, I wasn't aware you could do that. If that's the case and an audit is made when someone turns it on, that closes some issues.
We did it in all our customers' systems and all our own systems (so that named individuals could log on with admin privileges using their own privileged account if they had one, but there were no group accounts) . It was actually recommended by MS as best practise for their server OS at one point, but I don't know whether it still was for Windows 2008. There are many arguments against group (shared) accounts, of which the audit trail is actually only a minor one: the main one is that when someone departs you have to change the password, and because it's a privileged account it has to have a strong password, and there are issues with passing the new password to a number of people in a secure manner and with all those people having to keep the new p[assword secure during the first few weeks when they haven't yet really learned it yet (so it's liable to be written down somewhere). With individual accounts life is easy: just delete the leaver's account; if you need to add a new sysadmin, create a new account and let her change the password to something she likes at first logon.
Tom
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply