Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implementing Encrypting File System (EFS) with SQL Server


Implementing Encrypting File System (EFS) with SQL Server

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36002 Visits: 18728
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Shas3
Shas3
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 48
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Thysie
Thysie
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 78

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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6770 Visits: 1908

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Sam Greene
Sam Greene
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 584
Good article,

I'm assuming the same method will work with encrypting backups. Is this true?

Can a domain admin come in an decrypt this data? I'd like this not to happen.

Thanks!
Sam Greene
Sam Greene
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 584
For those of us who cannot restart the sql service to make this happen here is how I got this done:

Detatch database
Move files to new folder which you will eventually encrypt
Reattach files
Take DB offline
Encrypt the folder using your sql service account
Bring DB Online

Encrypting the backup folder also seems to work fine.
Sandip-31Oct
Sandip-31Oct
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 356
Excellent Article. Thanks

What additional steps require if there is cluster environment? on primary node it works perfectly but when i failover the sql on secondary node , encrypted database is not available.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search