Operating system error 38 - SQL Storage Compress

You may encounter the following error when querying an encrypted database:

Msg 823, Level 24, State 2, Line 2

The operating system returned error 38 to SQL Server during a read at offset <offset> in file <database file path>.

This error arises when SQL Server attempts to access an encrypted database file (*.mdfe, *.ndfe or *.ldfe) and the key file is unavailable, so the file cannot be decrypted.

Resolving this error

Ensure the key file used to encrypt the database file is stored in the keys directory. By default, this is %ProgramFiles(x86)%\Red Gate\HyperBac\keys on 64-bit machines and %ProgramFiles%\Red Gate\HyperBac\keys on 32-bit machines.

You can check the keys directory from the HyperBac Configuration Manager:

1. From the system tray, double-click the HyperBac Configuration Manager icon to open the HyperBac Configuration Manager. Do not stop the HyperBac Control Service.
2. Select the Advanced tab.
3. The Encryption Key Path is displayed under Path Values.

   

4. If the database is suspect or recovering, take the database offline and then bring it back online. To do this:
   • Right-click the database in SQL Server Management Studio (SSMS), and select Tasks > Take Offline. Then, right-click and select Tasks > Bring Online.

or,

• Ensure you are connected to the master database, then run the following T-SQL statement for the database:

ALTER DATABASE <database name> SET OFFLINE

followed by

ALTER DATABASE <database name> SET ONLINE

For information on changing the location of the keys directory, see Moving the HyperBac index, key and log files.

For more information on key files, see Working with key files for encrypted data.