Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4772
  • Last Modified:

SQL and Bitlocker

We're trying to use Bitlocker to secure an SQL 2012 Standard installation, but whenever we turn BitLocker ON, we cannot access the database using Windows Authentication (on a domain).

We have TPM turned on. Turning Bitlocker off has it work without issue... but we need to encrypt the drive.

What am I doing wrong?
0
d2kee
Asked:
d2kee
  • 6
  • 5
  • 4
  • +1
2 Solutions
 
btanExec ConsultantCommented:
Bitlocker faq answer most questions but ideally bitlocker should be done after machine has joined domain and we also need to note where is this the db store. By default only system partition is encrypted not data partition. Account wise it should be transparent to ms sql since it is just another data in the volume.  May ne good to check event viewer for any errors.

http://technet.microsoft.com/en-us/library/hh831507.aspx#BKMK_privs
0
 
Rich RumbleSecurity SamuraiCommented:
Bitlocker is not encrypting the database, in fact when the OS is running, BL isn't protecting anything, please read: http://www.experts-exchange.com/Security/Encryption/A_12134-Choosing-the-right-encryption-for-your-needs.html
-rich
0
 
btanExec ConsultantCommented:
bitlocker is volume encryption only :)
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
d2keeAuthor Commented:
Then why does MS list it as a possible encryption method to use with SQL? And, if it isn't encrypting anything... why can't SQL access the DB data when Bitlocker is turned on? Because that's the problem I'm encountering - it sure acts like it's encrypted and inaccessible while the drive is under bitlocker protection. Can you elaborate?
0
 
Rich RumbleSecurity SamuraiCommented:
Where is the DB? on a USB or portable drive?
http://technet.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx#_Toc189384690

BitLocker and EFS
It is beyond the scope of this article to compare EFS and BitLocker as both are complex technologies that require more technical detail than is covered here. Generally, EFS is targeted at protecting user data while BitLocker is designed to protect volume and system data. In terms of Microsoft SQL Server performance, BitLocker has lower latency on disk reads and writes without the concurrency issues EFS has.

Comparison with TDE

BitLocker and TDE both primarily protect against offline attacks. BitLocker protects at the volume level so when the server is online, the volume is unlocked, though not decrypted. Like EFS, BitLocker has a data recovery mechanism, which TDE does not yet have. The advantages of using BitLocker are ease of administration, abstraction of key management, and transparency. The disadvantage is that the protection only extends to the volume. Detaching or backing up the database to a different volume that is not protected by EFS or BitLocker causes any protection the file currently has to be lost. The other disadvantage of BitLocker is the broad scope of protection. Because the entire volume is unlocked, any user with access to a computer that can access the files on disk can access the data in plaintext. Similarly to TDE, BitLocker relies on other mechanisms for access control (such as the database permissions used in TDE and the Windows file permissions used by BitLocker). As with EFS, the database administrator might not have the necessary privileges to administrate BitLocker.
--------
What is being said is sort of misleading, it does protect when the OS is off, but no when it's on or when backed up. Once you copy data off an encrypted drive on to a non-encrypted drive, you lose that encryption. If the FILE/DB is encrypted INTERNALLY, then the encryption stays. It's like haveing a file in a zip archive that is password protected. Copy the file out of the zip and it's not protected. If the file is an encrypted office document however, then there is another layer of encryption that doesn't depend on the external zip encryption.
Same with BL or EFS. You want DB level encryption not file level encryption.
-rich
0
 
d2keeAuthor Commented:
SQL is installed on C:\. Data is on D:\ - I have BL turned on for D:\ (but not C:\).

I am really only looking for drive level encryption. We can't afford TDE and BL would be acceptable. Essentially - we only need offline protection. If someone steals a drive or attempts access without an authorized user account we want to make it more difficult to gain access to the DB data. Once it's online with active queries, it's difficult to properly encrypt at this level anyway because data still has to be decrypted in transit.

Basically, this would prevent a physical attack vector. We have other security protocols in place to prevent unauthorized access. I just want to get BL working so I can perform some tests before I rule it out.

I'd love to use TDE... but it's just not an option at almost 4 times the cost (when that's the only add'l feature I'd use).

Thanks for educating me and being willing to help. I appreciate it.
0
 
Rich RumbleSecurity SamuraiCommented:
Then D: has to be unlocked before you can access the drive/files at all, perhaps that's why? You can't really explore the drive until it is. There are many ways to encrypt at the DB level, and I'd recommend using an HSM if doing so, perhaps explore the Yubico offerings.

You can also use TrueCrypt for offline protection if you want to explore the ease of use aspects of BL vs another. Physical theft is typically not an issue for servers, when they are properly secured in a data-center or a good strong cabinet. But not everyone gets a secure room or cabinet to themselves to it's likely drive encryption would be good in those cases.
-rich
0
 
d2keeAuthor Commented:
No - the drive was unlocked... still wasn't working. Another forum mentioned possibly having to ensure the BL is created using the same account that SQL runs under... but that seems to contradict a lot of other things saying that all authorized users have access to the files.

Could it be that the drive that SQL runs on also needs to be bitlocked (though that doesn't make much sense either).
0
 
Rich RumbleSecurity SamuraiCommented:
No, once it's unlocked, anyone with access can access... Turn up the event loging settings, and check the event logs, meaning audit failures and successes beyond the defaults.
Start-> run ->Secpol.msc ->secpol.msc
Advanced audit policies -> enable as much as you can for the time being.

There are (free) alternatives to BL as well if you think that will help. If you use one of them, and there is no issue, than perhaps there is something with BL that isn't allowing it to work.
I mention a few in my Choosing the right encryption article.
-rich
0
 
btanExec ConsultantCommented:
The setup needs admin right but thereafter any authenticated user should be able to access the BL drives.

Also note this from BL FAQ

Why am I unable to automatically unlock my drive?

Automatic unlocking for fixed data drives requires that the operating system drive also be protected by BitLocker. If you are using a computer that does not have a BitLocker-protected operating system drive, the drive cannot be automatically unlocked. For removable data drives, you can add automatic unlocking by right-clicking the drive in Windows Explorer and clicking Manage BitLocker. You will still be able to use the password or smart card credentials you supplied when you turned on BitLocker to unlock the removable drive on other computers

By the way, there can be restriction of the user access for removable drives set though
http://blogs.technet.com/b/askpfeplat/archive/2013/06/10/how-to-enable-user-based-control-enforcement-of-bitlocker-on-removable-data-drives.aspx
0
 
rk_india1Commented:
You need to use the SQL server service account  to enable Bitlocker and initialize the TPM

Installation and initialization


BitLocker is installed automatically as part of the operating system installation. However, BitLocker is not enabled until it is turned on by using the BitLocker setup wizard, which can be accessed from either the Control Panel or by right-clicking the drive in Windows Explorer.

At any time after installation and initial operating system setup, the system administrator can use the BitLocker setup wizard to initialize BitLocker. There are two steps in the initialization process:

1. On computers that have a TPM, initialize the TPM by using the TPM Initialization Wizard, the BitLocker Drive Encryption item in Control Panel, or by running a script designed to initialize it.


2. Set up BitLocker. Access the BitLocker setup wizard from the Control Panel, which guides you through setup and presents advanced authentication options.



When a SQL service account  initializes BitLocker, the administrator should also create a recovery password or a recovery key. Without a recovery key or recovery password, all data on the encrypted drive may be inaccessible and unrecoverable if there is a problem with the BitLocker-protected drive.

reference link:http://technet.microsoft.com/en-us/library/cc732774.aspx
0
 
d2keeAuthor Commented:
Thanks - we're working through this today. We'll report back and mark answers once we have a successful resolution. I really appreciate the help!
0
 
d2keeAuthor Commented:
While richrumble's education on encryption was very valuable - rk_india1 directly answered my question. This was the exact problem - once we used the SQL Service account as the account that enabled BitLocker - we were able to access the DB without issue.

For others encountering this issue, you also need to enable BL on your C:\ drive to enable auto-unlock (you can't enable auto-unlock unless your system drive is also bitlocked). If you don't do this, every time you restart your system for maintenance, you will have to manually unlock the drive and then restart SQL services. (At least that's what we've experienced.)
0
 
Rich RumbleSecurity SamuraiCommented:
interesting (and a bit convoluted), great to know you got the answer you were looking for!
-rich
0
 
d2keeAuthor Commented:
And thanks to you Rich - I appreciated the help a lot. I learned more about encryption in a few articles you pointed me to than I'd known previously.
0
 
btanExec ConsultantCommented:
Pardon me for not been of help
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now