What if TDE was available in Standard Edition?

  • Comments posted to this topic are about the item What if TDE was available in Standard Edition?

  • We've been here with Steve Jones not long ago. I agree TDE would be a great feature to have on standard versions, but a lot of people remarked on how involved it is to set up, and how much it complicates restores. Realistically, I think TDE is probably overkill for most small-medium businesses.

    Previously, the discussion was about how without TDE it is impossible to protect a database backup file. Anyone with admin privs on their local machine (ie. almost everyone who owns a computer) can restore the database and take command of it by reassigning all privs as they wish.

    How about a less comprehensive encryption that's NOT end to end, but just encrypts the static MDF/LDF data and backup-file-contained data, but strongly with a serious AES-256 algorithm.

    That would be a useful medium between the two protection options at present, which are (1) nothing, and (2) truly enterprise strength (and enterprise TCO)

    How many small businesses do you know who change IT techs regularly and have trouble keeping track of their passwords, let alone encryption certificates.

    Ben

  • Very good point Ben. A lot of small businesses simply wont have tech guys at all - they'll hire someone to come in and set things up for them and show them what to do. Having a complex backup/restore process therefore will just put them off, leaving them open to data loss when something less sinister such as a disk failure occurs.

  • Ideally everyone would use ideal solutions like PostgreSQL 9, and pay for added-value services.

    If we don't agree with microsoft prices, or business-class-products categories.. we just have to set up our customers with alternative softwares.

    Crying or praying is just not enough.

    Yours truly.

  • Involved? That doesn't make any sense. You create a cert, backup the cert, and alter the database. It's a three step process that is actually simpler than creating a database, table, or any object within a database. It adds exactly 1 step to a restore process - restore the cert first.

    I thought making backup compression an Enterprise Edition only feature was just plain stupid. After all, backup compression was on the feature request list for several versions of Sybase, LONG before the very first version of Microsoft SQL Server was licensed. I know it has been requested by hundreds of people for every single version of SQL Server since it has existed as a Microsoft product. My response to the "feature hype" item in the marketing literature was "It's about #&$^ time!"

    TDE is another feature that falls into my category of "It's about $&^# time!" and should be available in every edition of SQL Server. I can create a certificate, obfuscate code ("encrypt"), enforce password policies, sign code modules, create logins/users, associate to roles, deal with ownership chains, fill in the blank for security features in every edition. TDE and Extensible Key Management are the only security features that I can think of that are not available in every edition. I can understand EKM being an Enterprise edition only feature. Making TDE an Enterprise edition only feature, in my opinion, is just plain stupid.

    Michael Hotek

  • Is there a workaround for this that is similar to the workaround from before we had backup compression in Standard Edition? Back then it was:

    * Backup the database

    * Compress the .bak file to .7z or something

    * Delete the .bak file

    * Let the .7z file be the one that goes onto the tape or whatever your backup medium is

    So if people getting stuff off your backup tapes is the issue for you (as per the article):

    * Backup the database

    * Squish it into some sort of encrypted file, with a strong password

    * Send that off to the tape

    Or even just turn on your encryption option for the tape.

    That doesn't cover you for everything (is still en clair on the disk in Sql Server's working files), but will do for the stolen backup tape scenario in the article.

  • this would be awesome for us - we end up using a 3rd party tool, instead of buying EE- it's cheaper and achieves the same result.

    It would save us quite a bit....

  • What I don't understand is why Mickeysoft would include two new features like TDE and Backup Compresion that are not mutually compatible with each other. If you don't believe this then just try using TDE to encypt a database, then back it up using COMPRESSION and notice how the compression ratio is bascially nullified on an encpyted database using TDE. Great! so, you can encrypt a very large database using TDE with no problem, but you can't take advantage of the new backup compression of that database, so you are out of luck there, at least for now 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"

  • TravisDBA (7/6/2010)


    What I don't understand is why Mickeysoft would include two new features like TDE and Backup Compresion that are not mutually compatible with each other. If you don't believe this then just try using TDE to encypt a database, then back it up using COMPRESSION and notice how the compression ratio is bascially nullified on an encpyted database using TDE. Great! so, you can encrypt a very large database using TDE with no problem, but you can't take advantage of the new backup compression of that database, so you are out of luck there, at least for now anyway. 🙂

    Because encryption changes the entropy of the data, you need to always compress FIRST (whether database or any other) then encrypt. It does not work well the other way.

    ...

    -- FORTRAN manual for Xerox Computers --

  • I am talking about compressing the backup, not compressing the database then encrypting it,, But anyway, I don't remember seeing that anywhere in SQL 2008 BOL? That would be nice if that little important detail was in there, but what else is new. 🙂

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • I'm sure TDE will make it's way into lower versions - possibly even Express. Once Microsoft gets a whiff of 3rd parties (including their partners) muscling in on its turf they will cut them out faster than Steve Ballmer can say "Our stock price has dropped 10 cents". This has happened before, it will happen again. Just be patient.


    James Stover, McDBA

  • TravisDBA (7/6/2010)


    I am talking about compressing the backup, not compressing the database then encrypting it,, But anyway, I don't remember seeing that anywhere in SQL 2008 BOL? That would be nice if that little important detail was in there, but what else is new. 🙂

    SQL Server 2008 Books Online does specifically mention that, but don't let that get in the way of a good gripe.

    "SQL Server 2008 Books Online (August 2008)

    Understanding Transparent Data Encryption (TDE)

    ...

    Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

    ..."

  • Thanks, but I already know about backing up a encrypted database with compression has no effect on the compression, and that is in BOL, hence my first post. However, it was pointed out in a previous post by Jay that compressing the database first then encrypting as an alternative. Where is that in BOL?, Anyway, I still don't like the idea of Mickeysoft giving us two new neat features we basically can't use together. 🙂

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Michael Valentine Jones (7/6/2010)


    SQL Server 2008 Books Online does specifically mention that, but don't let that get in the way of a good gripe.

    Oh that made me chuckle! I love that kind of humour 🙂

  • TravisDBA (7/6/2010)


    Thanks, but I already know about backing up a encrypted database with compression has no effect on the compression, and that is in BOL, hence my first post. However, it was pointed out in a previous post by Jay that compressing the database first then encrypting as an alternative. Where is that in BOL?, Anyway, I still don't like the idea of Mickeysoft giving us two new neat features we basically can't use together. 🙂

    My comment was not based on BOL, it was based the on the mathematics of compression and encryption. Compression works on mapping repetitive patterns in data, once encyrpted almost all such patterns are lost

    ...

    -- FORTRAN manual for Xerox Computers --

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply