SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Encryption - Protecting Files at Rest

You have a few options for protecting your SQL Server data at rest. So far I haven't seen anything thing protects data in memory, and I'm not sure we ever will. After all, at some point it needs to be processed, joined, etc., and that can't happen if it's well encrypted.

As I've been working through the encryption book I'm tech editing, I was thinking that Transparent Data Encryption (TDE) was the only native way to protect your data files. It had slipped me mind that there are two other Windows solutions, which will work with SQL Server.

The Encrypting File System (EFS) and Bitlocker are both part of Windows, an can be used to secure your data files at rest.

EFS requires that you take databases offline and then encrypt the files (or folders) before bringing them back online. Bitlocker will encrypt an entire volume on your Windows machine, and while you can still use Windows while it's working, it will be slow.

For SQL Server should you use these?

The answer is always it depends. Both of these will throw a hit on your system, and if that's a problem, you have to weight the issues of that against the protection you get. For a server or workstation, I'm not sure it's a big deal. For laptops I'd recommend Bitlocker as a solution since you really should protect all your files, not just SQL Server files.

I know many people might feel if they protect their data files they've covered the important things, but I'm not sure I agree. I've seen too many people cut and paste, export, or copy files for importing onto their machines.

And often this is the data you might really want to be sure is protected.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by neoeast on 7 July 2011

EFS or Bitloker can only protect your file with in the folder you have encrypted. If you detach and move file or make backup on some other folder or drive, they are not protected. The only best solution with in SQL server until now is using TDE but with a limitation of Enterprise or Developer edition of SQL Server 2008. Not available with SQL server 2005.

Posted by Mark-527630 on 2 November 2014

This is an old article but I think it the right place to ask a question ...

I have a laptop with Windows 8 and SQL Server 2008 (ver 10.50.2500.0).  I have turned on BitLocker to encrypt the whole drive. Performance is good.  The problem I have is that SQL Server won't start up cleanly. I can read database records but as soon as I try to do a write it fails. The solution is to restart the service. So every time I start up windows I have to open the config manager and restart the service.

Is this a know issue? Is there a fix? Is there a work around (eg batch file I can run at start up that does the restart for me)?

Posted by Mark-527630 on 26 November 2014

There doesn't seem to be anything about this SQL Server startup problem anywhere. Has anyone else experienced this? I am still restarting the SQL Server service everytime I turn my HB Folio on. I have an SSD if that makes any difference.

Leave a Comment

Please register or log in to leave a comment.