Implementing Encrypting File System (EFS) with SQL Server

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/implementing_efs.asp

    K. Brian Kelley
    @kbriankelley

  • Antares686

    SSC Guru

    Points: 125444

    Very interesting article. I will have to play with the concept sometime.

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    EFS is a very interesting technology... perfect for mobile users who might accidently get drunk with a laptop holding military software:

    http://english.pravda.ru/main/2002/10/22/38519.html

    If you've got sensitive data, this is definitely a technology to consider. The public key encryption is RSA and the symmetric is DESX, two established and proven encryption algorithms. The fact that it's seemless to SQL Server (unless we use the wrong account to encrypt) is another big plus.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Antares686

    SSC Guru

    Points: 125444

    The link is quite funny. And if true then I bet those guys got a real good duty after that if not court martialed.

    Good to know is RSA and DESX as those have well proven.

  • ckempste

    SSCoach

    Points: 17983

    Great article Brian, very thorough.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • don1941

    SSCarpal Tunnel

    Points: 4183

    Excellent article. We are struggling with how to protect personal information. This article is complete enough that I am going to circulate it around our org.

    Thanks for taking the time to do a good job on a cool NT feature!!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    Very interesting. Course, be sure you protect the backup files as well. Especially when they are on tape.

    For laptops, we use Pointsec (www.pointsec.com) here at JD Edwards. Help Desk has a central password reeset if you forget it.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    Backups are a particularly troublesome bird. Especially since throwing a password on a backup doesn't encrypt them. Speaking of which, anyone have any experience with SQLLiteSpeed and how it works?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Antares686

    SSC Guru

    Points: 125444

    I just got a copy and will be running on my test environment. It does have encrytion. Any thing specific you are curious about as I will be testing for company purchase and I plan to do anything it will let me?

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    I'd like to see how the times compare between a normal backup and SQLLiteSpeed backup, especially with encryption turned on. I'm going to DL a copy myself and test it out fully. I saw that it works using extended stored procedures, so I've got a good idea how it works, I just want to know how it compares.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    I've got a review coming out next week or the following.

    I spoke with the Litespeed people at PASS and they mentioned that they had worked with MS and taken advantage of new APIs in SS2K that run faster than the pipe mechanisms. Most agents and the native backup use the pipe mechanism.

    Litespeed runs faster than native, slghtly higher CPU, but since I usually backup at off peak, it isn't a big deal. I did some minor work with the encryption, short keys (15 char or so) and didn't seem to substantially increase the CPU or times. Restores required the pwd (as expected) and worked fine.

    The key management of the passwords has me a little stymied right now and until we can come with a way to manage this, not sure what to do. This is a great product and I highly recommend it. I'm seeing 70-90% compression of backups. 1GB backup files going to 120MB on compression level 2. Compression 3 (highest), really jumped the CPU. Not sure it matters, but 2 is a nice balance for me.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • Shas3

    Hall of Fame

    Points: 3671

    Very good article Brian, Perfect solution for the companies looking for the high security

    But at the same time it is very scary if the service account is lost or mistakenly deleted your backup is the only way to go. I am wondering whether you can decrypt the files by recreating the lost service account? I guess not? Also how does this work if there is a subscriber to this running with a different service account?

    Edited by - Shas3 on 06/26/2003 2:05:57 PM

    Shas3

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    Best way to approach using EFS is to follow the rules as set forward in the Win2K Resource Kit. Basically, you need to have your recovery agents in place.

    This can save you a lot of headaches should the service account get deleted. Recreating an account means it actually gets a different SID. The "name" of the account is for our convenience (and apps like SQL Server). However, as far as the OS is concerned (and therefore EFS), it relies on the SID.

    On the local system, the administrator tends to be a recovery agent, so you have an option, usually. Biggest problem, though, is when you have to rebuild a system and you try to get access to the files. Administrator account would be different, etc. You get the idea.

    So before implementing EFS, make sure you've got recovery in mind. And make sure you've tested it.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Thysie

    Old Hand

    Points: 341

    Hi,

    Very nice article Brian.  Do you perhaps have an idea if this affects the performance of the system using the SQL server?

    Cheers

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    It does to some effect, although the numbers I saw are a couple of years old. I think it used to be a 20-30% performance hit for the databases that were encrypted as opposed to if they weren't encrypted at all. As far as actual hit on the processor or memory, I've not seen actual numbers. I need to do that research and update this article as this was written in the Windows 2000 days and there were some changes to EFS in Windows Server 2003.

     

    K. Brian Kelley
    @kbriankelley

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

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