Solved

SQL and Bitlocker

Posted on 2013-12-06
16
3,568 Views
Last Modified: 2013-12-09
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
Comment
Question by:d2kee
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 61

Expert Comment

by:btan
ID: 39702710
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
 
LVL 38

Expert Comment

by:Rich Rumble
ID: 39702952
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
 
LVL 61

Expert Comment

by:btan
ID: 39702957
bitlocker is volume encryption only :)
0
 

Author Comment

by:d2kee
ID: 39703218
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
 
LVL 38

Expert Comment

by:Rich Rumble
ID: 39703235
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
 

Author Comment

by:d2kee
ID: 39703251
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
 
LVL 38

Assisted Solution

by:Rich Rumble
Rich Rumble earned 20 total points
ID: 39703268
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
 

Author Comment

by:d2kee
ID: 39703297
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 38

Expert Comment

by:Rich Rumble
ID: 39703303
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
 
LVL 61

Expert Comment

by:btan
ID: 39703926
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
 
LVL 5

Accepted Solution

by:
rk_india1 earned 80 total points
ID: 39706699
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
 

Author Comment

by:d2kee
ID: 39706852
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
 

Author Closing Comment

by:d2kee
ID: 39707191
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
 
LVL 38

Expert Comment

by:Rich Rumble
ID: 39707215
interesting (and a bit convoluted), great to know you got the answer you were looking for!
-rich
0
 

Author Comment

by:d2kee
ID: 39707236
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
 
LVL 61

Expert Comment

by:btan
ID: 39707368
Pardon me for not been of help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

SSL stands for “Secure Sockets Layer” and an SSL certificate is a critical component to keeping your website safe, secured, and compliant. Any ecommerce website must have an SSL certificate to ensure the safe handling of sensitive information like…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now