I've started using BitLocker on all my computers. A few years late, but I think it is a fantastic function, and recommend it to anybody. However, using it with SQL Server has been an 'interesting' journey.
My drive set up is as follows:
C: Boot Drive and SQL Server Installation
D: Database data/log files
In BitLocker, D: drive is set to auto-unlock. Therefore, one would assume that once booted, BitLocker would have unlocked the drive, and SQL would be able to access the files, just as Windows can access other files...?? Wrong!
Bizarrely, even after auto-unlocking the drive, SQL Server cannot access the databases. Any attempt at doing so results in an error such as this:
Msg 823, Level 24, State 2, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000005f4000 in file 'D:\Databases\MyDatabase_Data.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Initially I tried rebooting, but that didn't work either.
Help me get the attention of Microsoft
First off, please help me raise awareness of this by upvoting the post on Azure.com.
Get SQL Server to access databases on a BitLocker drive
The solution is to simply restart the SQL Server service:
Start > (search for) Services > SQL Server (MSSQLSERVER) > Restart
You don't need to close SQL Server Management Studio while doing tihs. Once restarted, hit refresh on the node in SSMS that previously couldn't be accessed. It should now work.