FIX: Database shows "recovery pending" state when you use TDE with EKM provider in SQL Server 2012 or SQL Server 2014

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard More

Symptoms


Assume that you have some Transparent Data Encryption (TDE) databases that are encrypted by using Extensible Key Management (EKM) provider in Microsoft SQL Server 2012 or SQL Server 2014. When you run high load insert query on an unstable network connection, you find that TDE database becomes unavailable and shows "recovery pending" state. You receive the following errors:
<Date> <Time> spid1s Cannot open session for cryptographic provider ‘<EKM Provider name>’. Provider error code: 5. (Authentication Failure - Consult EKM Provider for details)
<Date> <Time> spid125 Error: 9001, Severity: 21, State: 1.
<Date> <Time> spid125 The log for database '<DB name>' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
<Date> <Time> spid125 During undoing of a logged operation in database ‘<DB name>’, an error occurred at log record ID (1183:136:350). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
<Date> <Time> spid62 Database <DB name> was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

Resolution


After you apply the fix, the TDE database will try to use cached database encryption keys during network outages. This prevents it from shutting down.
The by design behavior was changed in the following cumulative update of SQL Server.

Cumulative Update 1 for SQL Server 2014

Cumulative Update 9 for SQL Server 2012 SP1