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 occurs 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,
   • Make sure 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