December 7, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/implementing_efs.asp
K. Brian Kelley
@kbriankelley
December 7, 2002 at 6:43 pm
Very interesting article. I will have to play with the concept sometime.
December 7, 2002 at 7:40 pm
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
December 7, 2002 at 8:44 pm
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.
December 9, 2002 at 3:44 am
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"
December 9, 2002 at 8:33 am
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!!
December 9, 2002 at 3:22 pm
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
December 9, 2002 at 3:55 pm
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
December 9, 2002 at 5:20 pm
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?
December 9, 2002 at 6:39 pm
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
December 10, 2002 at 10:10 am
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
June 26, 2003 at 2:03 pm
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
June 26, 2003 at 3:00 pm
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
December 1, 2006 at 7:37 am
Hi,
Very nice article Brian. Do you perhaps have an idea if this affects the performance of the system using the SQL server?
Cheers
December 1, 2006 at 8:57 am
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 17 total)
You must be logged in to reply to this topic. Login to reply