December 5, 2011 at 10:50 am
Completely agree.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 5, 2011 at 12:32 pm
I could not agree more!
Another redgate product to come? I would be similar like the SQL Storage Compress. Redgate should kiss Microsoft sales teams feet.
December 6, 2011 at 9:49 am
It seems like I hear more stories about sensitive data theft (or least misplacement) as a result of some developer's laptop getting stolen than I do about production database servers getting hacked. I guess staging production data on a laptop is inevitable for some organizations, even if management doesn't know it's being done by the developer.
However, even with TDE, the developer must have the technical knowledge and presence of mind to implement it. With SQL Server Compact Edition, we have the option of simply specifying full database file encryption when a new db file is created. Not only does Express Edition need TDE, but it needs some mechanism that allows it to be enabled as easily as what Compact Edition offers.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 6, 2011 at 11:56 am
Steve Jones - SSC Editor (12/5/2011)
OCTom (12/5/2011)
I think the stupid decision is to put senstitive information on mobile devices and media. That data needs to be siloed and protectedYou're missing the point. It's not all data on mobile, you might need to access some data, or carry some. Think salespeople; they don't need all customer data, but they might need their data.
It's not always a connected world, and you can't limit access to data to certain workstations in certain places.
I have to agree Steve, it's not always a connected world, and sometimes depending on your job, you may still have to transport smaller pieces of sensitive data, and using drive encryption tools like Windows 7 BitLocker isn't always a panacea either. There are already Bitlocker crackers out there on the market. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
December 7, 2011 at 8:08 am
I completely agree with this point. All data is valuable, from huge corporations' to personal, and therefore losing it to the wrong hands unencrypted is not acceptable. Does Microsoft care about security at the small business/personal business level? They should.
December 7, 2011 at 8:35 am
I am in partial agreement with TDE being on more versions, Standard sure, express, not convinced..
A lot depends on what you are using TDE for. By and large you should be using whole disk encryption for your laptops that contain ANY sensitive data, this negates the need for TDE on laptops and covers all those hidden spots that you would otherwise miss.. If you are concerned about your backups then TDE seems like a fair amount of overhead to achieve that and you have to pay that overhead for every read and write. I would much rather appeal to MS to build a backup encryption regime..
And perhaps my views on encryption color this view. I view TDE as overkill in 99.999% of the cases it is used for. In most databases there is truly very little data that really MUST be secured, things like bank account data, etc.. That data should be encrypted and likely independently of the container (the database). All the rest might be important and surely wouldn't want a competitor to get but TDE seems like overkill when you should already be taking other steps to secure your machines.
CEWII
December 7, 2011 at 9:00 am
Elliott Whitlow (12/7/2011)
I am in partial agreement with TDE being on more versions, Standard sure, express, not convinced..
Fair enough, but does it hurt to have it in Express? I'm not sure I've made a great argument, but I've yet to hear a good reason why it's not in Express.
And perhaps my views on encryption color this view. I view TDE as overkill in 99.999% of the cases it is used for. In most databases there is truly very little data that really MUST be secured, things like bank account data, etc..
My view is that it's hard to know if things will change, and that FDE is a mus to help with this. I'd prefer TDE so that if something changes, and data is added, perhaps through replication, we have some minimal protection (which is what I see TDE as).
December 7, 2011 at 9:09 am
Steve Jones - SSC Editor (12/7/2011)
Elliott Whitlow (12/7/2011)
I am in partial agreement with TDE being on more versions, Standard sure, express, not convinced..Fair enough, but does it hurt to have it in Express? I'm not sure I've made a great argument, but I've yet to hear a good reason why it's not in Express.
Other than for sales reasons I don't have a great answer on yes/no either..
And perhaps my views on encryption color this view. I view TDE as overkill in 99.999% of the cases it is used for. In most databases there is truly very little data that really MUST be secured, things like bank account data, etc..
My view is that it's hard to know if things will change, and that FDE is a mus to help with this. I'd prefer TDE so that if something changes, and data is added, perhaps through replication, we have some minimal protection (which is what I see TDE as).
But TDE doesn't help with replication, it only protects data-at-rest and as long as the database server is up you have no visible encryption. So query the SSN's away or the bank details because its wide open.. While things change addition of new fields should not come as a surprise and should always be thought through and one of the questions I ask if it isn't obvious is whether this data needs to be protected. In most cases no, in very few cases yes and we take extra steps. So I don't see TDE as much more than overhead..
CEWII
December 7, 2011 at 9:36 am
Elliott Whitlow (12/7/2011)
But TDE doesn't help with replication, it only protects data-at-rest and as long as the database server is up you have no visible encryption. So query the SSN's away or the bank details because its wide open.. While things change addition of new fields should not come as a surprise and should always be thought through and one of the questions I ask if it isn't obvious is whether this data needs to be protected. In most cases no, in very few cases yes and we take extra steps. So I don't see TDE as much more than overhead..CEWII
Protects the backups, detach/attach, and copies of the db files. Which can be a remote copy issue, especially with autoclose on many msdb/express installations.
Overhead, but shouldn't be much.
December 7, 2011 at 9:47 am
Steve Jones - SSC Editor (12/7/2011)
I'm not sure I've made a great argument, but I've yet to hear a good reason why it's not in Express.
You might not consider it a *good* reason, but the obvious reason is "Because Microsoft can't charge you for an Enterprise Edition license if all the toys are in Express". 🙂
December 7, 2011 at 10:19 am
paul.knibbs (12/7/2011)
Steve Jones - SSC Editor (12/7/2011)
I'm not sure I've made a great argument, but I've yet to hear a good reason why it's not in Express.You might not consider it a *good* reason, but the obvious reason is "Because Microsoft can't charge you for an Enterprise Edition license if all the toys are in Express". 🙂
I'm sure that's the reason, I'm just not sure it's a good one or valid one. I think better to charge by scale, but that's me.
December 7, 2011 at 11:05 am
Steve Jones - SSC Editor (12/7/2011)
Elliott Whitlow (12/7/2011)
But TDE doesn't help with replication, it only protects data-at-rest and as long as the database server is up you have no visible encryption. So query the SSN's away or the bank details because its wide open.. While things change addition of new fields should not come as a surprise and should always be thought through and one of the questions I ask if it isn't obvious is whether this data needs to be protected. In most cases no, in very few cases yes and we take extra steps. So I don't see TDE as much more than overhead..Protects the backups, detach/attach, and copies of the db files. Which can be a remote copy issue, especially with autoclose on many msdb/express installations.
Overhead, but shouldn't be much.
These are all data-at-rest issues, which I contend should be handled by more granular encryption. I'm sorry but TDE generally feels lazy to me and while it DOES buy me some protection I think the protection is limited and could be better filled by other means.. By lazy I mean an unwillingness to do the analysis of the fields/tables that require additional protection and instead just doing everything.
The one point I really like though is backup encryption, that part is a bonus but I think MS should provide that independent of the whole DB being encrypted.
Attach/detach and copies are not really a problem for me, access to the base files is controlled and copies moved to non-prod environments have to be scrubbed.
I have little doubt that it isn't included in Express and Standard because they have to have SOMETHING to differentiate the different versions. At least we got backup compression in standard..
CEWII
December 7, 2011 at 11:21 am
Elliott Whitlow (12/7/2011)
And perhaps my views on encryption color this view. I view TDE as overkill in 99.999% of the cases it is used for. In most databases there is truly very little data that really MUST be secured, things like bank account data, etc.. That data should be encrypted and likely independently of the container (the database). All the rest might be important and surely wouldn't want a competitor to get but TDE seems like overkill when you should already be taking other steps to secure your machines.CEWII
Not exactly accurate. With the advent of people's medical/psychological historical data becoming more and more sensitive every day, I can think of virtually thousands of databases across the country, not to mention the world, where the organizations themselves don't want just anyone looking at ANY of the data in those databases, period. Also, with HIPPA and PCI compliance becoming real issues now in the workplace, it is fast becoimg a requirement as well. Also, look at the huge liability perspective a company or provider would possibly have if someone got hold of the thousands of Addiction, Substance Abuse, and Recovery databases that housed not only peoples names but other sensitive data/conditions about them (HIV positive, Hep C, etc..). Can you imagine the hundreds, if not thousands of lawsuits spawned out of a mass disclosure like that? We are not just talking about an occasional bank account or credit card column anymore in one or two tables in a database. Many databases today, and a lot more than .001% I might add, the ENTIRE database is sensitive, not just one or two columns. As far as securing machines go, firewalls/security access measures stop outside penetration, but they don't stop someone stealing a database from the inside and taking it down the street and attaching that database somewhere else and then releasing those peoples names to the public. Sure, ultimately they can go to jail for that if caught, but apparently that is not stopping alot of people from doing it anyway. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
December 7, 2011 at 12:06 pm
TravisDBA (12/7/2011)
Not exactly accurate. With the advent of people's medical/psychological historical data becoming more and more sensitive every day, I can think of virtually thousands of databases across the country, not to mention the world, where the organizations themselves don't want just anyone looking at ANY of the data in those databases, period. Also, with HIPPA and PCI compliance becoming real issues now in the workplace, it is fast becoimg a requirement as well.
So because they don't secure their databases this is a valid reason to use TDE, unfortunately TDE won't help in the cases you reference.. Also HIPAA and PCI hardly require full encryption. In the case of PCI you can store CC data but to be PCI compliant the keys can't be in the same database. Again TDE won't help if you can actively query the data. As for HIPAA things like notes and identify information are required to be protected. Some companies chose to go further. Again TDE won't protect this information if ti can be queried.. TDE ONLY protects data-at-rest when that database engine is NOT running or the database is backed up or disconnected. Don't get me wrong those are things that need to be mitigated but I wouldn't consider the data HIPAA compliant if I can do a seach of the Patients table by name directly such as:
SELECT * FROM dbo.Patients WHERE LastName = 'Jones'
Or
SELECT NameOnCC, CCNumber, CCExpiration, CCCode FROM dbo.PaymentTypes
TDE is not a solution for these cases..
Also, look at the huge liability perspective a company or provider would possibly have if someone got hold of the thousands of Addiction, Substance Abuse, and Recovery databases that housed not only peoples names but other sensitive data/conditions about them (HIV positive, Hep C, etc..). Can you imagine the hundreds, if not thousands of lawsuits spawned out of a mass disclosure like that?
TDE != HIPAA Compliance.. All the data you are referencing has one thing in common, a patient, that patient's name and other identifying information needs to be independently encrypted regardless of what you do to the database as a whole. See my points above..
We are not just talking about an occasional bank account or credit card column anymore in one or two tables in a database.
See my points above.
Many databases today, and a lot more than .001% I might add, the ENTIRE database is sensitive, not just one or two columns.
I challenge this assumption. Don't get me wrong your sales data is important but sensitive enough to encrpyt, not usually.. I have been in a single environment where the data was important enough to be considered sensitive accross the board.. The URLs for their websites all end in .MIL, and even in those cases not everything was sensitive.. I'm going to keep hammering on this point, TDE ONLY protects data-at-rest..
As far as securing machines go, firewalls/security access measures stop outside penetration,
No, they help, but they are a line of defense not a brick wall uterly preventing access. Defense in Depth is the key.. Every heard the phrase, "hard outer shell, soft chewy middle"? This is a good description of many companies who put up external firewalls/access security but utterly fail at internal security. Once you get in you are free to do as you wish..
but they don't stop someone stealing a database from the inside and taking it down the street and attaching that database somewhere else and then releasing those peoples names to the public. Sure, ultimately they can go to jail for that if caught, but apparently that is not stopping alot of people from doing it anyway. 😀
Good point, who has access to your backups? The keys to do the restores externally are available to your DBA so you aren't going to prevent him from doing it. So you are talking about someone else.. TDE WILL help in this case, but from my previous posts I argue that backup encryption should be available without having to do TDE. However without additional third-party tools this is not possible at present. Even with all that said if you encrypt the sensitive data in your databases you make it even more difficult to make use of, because then the perpetrator not only has to get access to the database backups and keys for the backups he has to get the keys/passwords for the independently encrypted data as well as the methodologies. And if he already has query access to the data TDE does nothing to stop him where independent encryption, even with keys stored in the database make it that much harder.
CEWII
December 7, 2011 at 1:16 pm
There is hardly ever a good reason for employees or contractors to copy sensitive production data down to the local SQL Server instance on their laptop or desktop. Any developer (or organization) that lazy would probably not make the effort to implement TDE even if it was available.
Even in those narrow circumstances where this scenario is necessary, the organization should install some type of full HD encryption solution on the laptop. We simply can't trust the developer to properly DBA his local SQL Server instance when he's not really a DBA. TDE is more of a server and database configuration task than something a SQL coder or data analyst would be proficient at. Even with TDE implemented correctly on the local database, there may still be raw data files or Excel sheets containing sensitive data within the HD folder structure. For example, perhaps the developer is working on an ETL solution.
When it comes to protecting corporate or government data on laptops, I think implementing TDE on the database provides only partial coverage, and it would be best (not lazy but best) to encrypt the entire HD to make sure everything, including other things like email storage files and VPN configurations, are covered.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply