SQL and Bitlocker

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

Bitlocker is not encrypting the database, in fact when the OS is running, BL isn't protecting anything, please read: https://www.experts-exchange.com/Security/Encryption/A_12134-Choosing-the-right-encryption-for-your-needs.html
-rich

bitlocker is volume encryption only :)

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?

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

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.

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).

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

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

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!

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.)

interesting (and a bit convoluted), great to know you got the answer you were looking for!
-rich

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.

Pardon me for not been of help