SQL Server on Windows 10 or 11 with data on a secondary drive with bitlocker enabled

Question

Hello,&nbsp;On a Windows pro, 10 or 11 (both tested), with SQL Server 2019 (developer edition) installed with its program files in the system drive, C:, but its data files in a data drive D:, both ciphered with bitlocker with automatic unlocking enabled, it seems the SQL Server is unable to access the data files after a fresh boot.&nbsp;Attempting to log on the SQL Server through a local SSMS yields a 823 error code, which is supposed to mean the data files are corrupted. But the trouble disappears by restarting the SQL Server service, until the next boot.&nbsp;It looks to me as if the SQL Server service is unable to read the data on the 😧 drive upon startup, until I log on my desktop and restart the SQL Server service.Deactivating bitlocker on the 😧 drive causes the trouble to disappear.&nbsp;I cannot find any information whether this is an expected behavior or not.&nbsp;The Windows application log has many occurrences of the following messages, from the boot time until I restart the SQL Server service:The operating system returned error 21(Le périphérique n’est pas prêt.) to SQL Server during a read at offset 0x000000002d4000 in file 'D:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf'. Additional messages in the SQL Server error log and operating system error 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.Source: MSSQLSERVER; Event: 823&nbsp;There is nothing to see in the SQL Server error log, but since it is located on this 😧 drive too, that does not surprise me.&nbsp;SSMS error when attempting to log in without having restarted the SQL Server service is:===================================Cannot connect to ..===================================Warning: Fatal error 823 occurred at Feb 21 2022 12:00PM. Note the error and time, and contact your system administrator. (.Net SqlClient Data Provider)------------------------------For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-21-database-engine-error------------------------------Server Name: .Error Number: 21Severity: 24State: 1Line Number: 65536------------------------------Program Location:at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)at ...&nbsp;This is a developer workstation, so this trouble is not critical, but just a nuisance, having to manually restart the SQL Server service after each boot in order to be able to use my local SQL server.&nbsp;I got this trouble first on a Windows 10 pro workstation, after bitlocker has been enabled. Then I have migrated to Windows 11, the trouble was still here. And on a newly installed Windows 11 with BitLocker enabled, the trouble was here too.&nbsp;Is there anything which can be done to solve this trouble? (Excepted disabling bitlocker or moving the SQL data files to the system drive.)

deepakgupta86 · Answer

Have you tried delayed start option in sql server services?

fdelaporte · Answer

Yes I have tried that. Maybe rarely it helps, meaning sometime I do not have to restart it manually, but most of the time it is just the same. And so, I am not even sure that is a change that some very rare times makes a difference.

deepakgupta86 · Answer

You can try couple of other options1) Increase registry stratup delay for the sql services2) use the same account to run sql services and enable bitlocker.

fdelaporte · Answer

Thanks,Unfortunately the SQL Server service won't start with my personal account. It has local admin rights, but that is an AAD account (no on premise AD), which is maybe the issue for SQL Server.With the two minutes default delay for delayed auto-start, I am quite confident I am already logged-in when it starts. So, I do not expect increasing the delay will do. (My laptop power button is also a fingerprints reader and powering-up with a known finger causes it to automatically log-in once started.)Still I have added this:[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER]"AutoStartDelay"=dword:000493e0I will see if it helps

fdelaporte · Answer

It changed nothing.Actually the delayed auto-start can help, but only if doing a full reboot, not a shutdown and start.&nbsp;When doing a shutdown and start, by default Windows do a "fast startup", which kind of restore a hibernation state, but logged-out (so no session preservation). This does not actually stop any Windows service (including MSSQLSERVER service), they are kept running in this process. So, they do not actually start on the boot after a shutdown: they are already started. This causes the delayed auto-start to have no effect. And the SQL Server service still loses its access to its data files in the process, thought not having been stopped at all.&nbsp;So a workaround should be to disable Windows fast Startup (which is infamous for being regularly forced back upon us at major releases) and set the SQL Server service to have a delayed auto-start.&nbsp;I will confirm later if that works.

Forum Discussion

SQL Server on Windows 10 or 11 with data on a secondary drive with bitlocker enabled

5 Replies

Resources