Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Implementing Encrypting File System (EFS) with SQL Server Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2002 10:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #48917
Posted Thursday, June 26, 2003 2:03 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:59 PM
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
Post #48918
Posted Thursday, June 26, 2003 3:00 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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
Post #48919
Posted Friday, December 1, 2006 7:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 2:34 AM
Points: 11, Visits: 42

Hi,

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

Cheers




Post #326969
Posted Friday, December 1, 2006 8:57 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873

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
Post #327015
Posted Friday, August 8, 2008 3:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:11 AM
Points: 253, Visits: 546
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!
Post #549625
Posted Friday, October 3, 2008 12:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:11 AM
Points: 253, Visits: 546
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.
Post #580515
Posted Tuesday, October 11, 2011 2:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:59 AM
Points: 190, Visits: 320
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.
Post #1188809
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse