KB #240102: Encrypted Databases not accessible (Recovery Pending, Suspect)

Search Knowledge Base

KB #240102: Encrypted Databases not accessible (Recovery Pending, Suspect)

Type:

Information

Summary:

Your encrypted SQL databases are not accessible after you have started SQL Server. They are in Suspect or Recovery Pending mode. There are numerous reasons this might be the case. This article lists some myriad reasons and how to diagnose and resolve.

Additional Information:

You have encrypted your database, but you are not able to access it via SQL Server. You will typically see either “Torn Page..” error or a “I/O inconsistency..” error in the SQL error log when SQL has tried to load the encrypted database. In SQL Management Studio, the database has Suspect or Recovery Pending status. Below are some of the reasons that you might not be able to access the encrypted databases.

If you have installed the Encryptionizer API’s during installation or from the main menu, you can test if the keys are being delivered to the SQL instance.

SELECT master.dbo.fn_n_keycount()

A result > 0 (zero) is expected.

If result > 0, an encryption key is delivered.
If result = 0, and encryption key has not been delivered.

Encryption Key Delivered but databases not accessible

If there results of the fn_n_keycount query is greater than (>) zero (0), then there are some other things to explore:

The Encryption key(s) with which your database(s) is encrypted does not match one of the keys which are part of the key profile, created when you secured the SQL instance with the Administration Wizard. In order for a SQL instance to load an encrypted database, the key with which you encrypted the database must match exactly one of the keys entered in the Admin Wizard – algorithm, key length and passphrase. If the encrypted database does not match the Admin Wizard keys, Encryptionizer cannot open the encrypted database file and SQL cannot recognize the database files as valid. More information can be found in the Whole Database User Guide (PDF) installed with your software (found in the install directory). Use the fn_n_codelvl Encryptionizer API which will examine each associated database file and determine if the database file matches the SQL instance profile (results: 0 or 4 = database file encrypted but does not match profile, 1 = database file not encrypted, 2 or 6 = database file key matches profile).
You secured or resecured the SQL instance, but did not restart the SQL instance since securing or resecuring the instance. The new encryption keys are loaded at the time that SQL Server starts, so a restart is needed after secure or resecure.
Database being loaded has originated from another server with a newer version of Encryptionizer. Encryptionizer is backwards compatible, meaning that a database encrypted on an older version can be read on a newer version of Encryptionizer. However, the reverse is not always true. Create a support case and one of our technicians may be able to help you resolve this issue.

Encryption Key not delivered

If you receive a result of 0, the key was not delivered to the SQL instance. The below are some of the possible reasons:

The SQL instance is not secured or has not been restarted since you secured the instance. You can run the Administration Wizard to determine the Secured state of a SQL instance. If the state of the SQL instance says “Unsecured”, you must run the Admin Wizard to set the Encryptionizer server key(s) and options. If the state says “Secured/Restart”, you must restart the SQL instance in order for the Encryptionizer keys to load.
Your License has expired or is invalid. If the Encryptionizer license is not valid (it may be a temporary or evaluation license) the Encryptionizer drivers will not load and your encrypted databases are not accessible.
- You can test your license using a small status utility in this KnowledgeBase article:
  KB #240132: Test if your License/Activation is valid
- For versions 2019.1210.31152 or before, you can also run reg3.exe or reg2.exe program found in your NetLib install directory, it will display the registration key name. If it has the words “Temp” or “Exp” in it, it is likely an expired registration key.
- For versions later than 2019.1210.31152, run nllicense4.exe from the NetLib install directory, and then More Info, you will see Product name listed. If this has Lease or Temporary or Evaluation in the product name, it is likely an expired license.
- If you start the Encryptionizer Main Menu program, you will be presented with an expired license message.
- These are all tips that your license may have expired. Contact tech support for a new Temporary License or information on how to permanently activate the software for your machine
The NetLib Key Management Service is not running. You will find this service in the Services Management Console. It must be running at the time that your secured SQL instance starts or your database will not come online. If it is not running, try starting it. If it starts, restart SQL and see if your databases come online. Even if they do, this should be investigated why the service was not running in the first place.
The NetLib Key Management Service is in START (Pending) mode. The NetLib Key Management Service has attempted to start up but gets stuck in Start Pending which will prevent the SQL instance from receiving the encryption keys. This happens rarely and randomly on Windows Server 2016 and Windows 10 operating systems. See KB #240126: NetLib Key Management Service in START_PENDING status for more information and a workaround.
The NetLib Key Management Service will not start. The NetLib Key Management Service will not start and there is an error message in the Windows System Event Log “..”is not a Win32 application”. This happens in very rare circumstances. See KB #240131: “not a valid Win32 application” error in the Windows System Log” for more information and a workaround.
Database did not come online after reboot but a manual restart of the SQL instance allowed the databases to come online. Particularly on Windows 2016 or later, the NetLib Key Management Service could start later than SQL Starts, causing this symptom. See KB240119: Encrypted Databases Not Accessible After Reboot but are Accessible After Manually Restarting SQL Server
You are using a Remote Profile (profile stored in alternate location) and there is an issue. See KB240040: Cannot start SQL Server or encrypted database is inaccessible when profile is on a remote machine
This machine is an EKM Client and the key is not being delivered to the SQL instance from the Encryptionizer Key Manager Server.
You have specified that the Master must be encrypted when you secured the instance, but the Master is not encrypted. The Encryptionizer Key Management Service (KMS) will not deliver the encryption key to the instance unless the “master must” rule is satisfied. The Master must be encrypted and it must be encrypted with a key that matches one of the keys specified in the Admin Wizard.

Knowledge Base

Search Knowledge Base