SQL Server – Reporting Services (v2005) – Error – Key not valid for use in specified state

Error Message

Image

Key not valid for use in specified state

Textual


Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B) (rsRPCError) Get Online Help
 

 

Remediation

The problem could be a combination of the following:

  1. Encrypted data are no longer accessible
  2. Reporting Services Service Account crypto key is no longer valid

 

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database

Error

Image

CannotDecryptSymmetricKey

Textual


Reporting Services Error
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. 
You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)
Bad Data. (Exception from HRESULT: 0x80090005)
 

What does this mean?

With the crypto (privatekey) gone, we can no longer access encrypted data.

We have two choices, restore the key by providing our backup file and the password we used while storing it.

Or remove all encrypted entities.

List encryption keys

Syntax


RSKeyMgmt.exe -i [instance] -l

Sample


set "_instance=v2005MIRROR"
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\RSKeyMgmt.exe" -i %_instance% -l

Output

listKeys

Removing encryption keys

Syntax


RSKeyMgmt.exe -i [instance] -d

Sample


@echo off
set "_instance=v2005MIRROR"
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\RSKeyMgmt.exe" -i %_instance% -d

 

Output

deleteKeys

 

Are Encryption keys actually the problem

Do Encryption Keys exist

GUI

EncryptionKeysList

 

Explanation
  1. If encryption keys do not exists or can not be accessed, then:
    • The Backup/Restore/Change buttons are disabled
    • But, the delete can still be
  2. If encryption keys exist and they are usable, then all (  Backup/Restore/Change/Delete ) buttons are enabled

 

Review Encryption Keys in database

Query



SELECT [MachineName]
      ,[InstallationID]
      ,[InstanceName]
      ,[Client]
      ,[PublicKey]
      ,[SymmetricKey]
  FROM [dbo].[Keys]

Output

DBEncyptionkeys

Explanation

Pay attention to the following:

  1. Query
    • That you are in the right database ( use [database] )
  2. Results
    • MachineName
      • TY ( in our case)
      • Especially important in scale out deployment
    • InstanceName
    • PublicKey
    • SymmetricKey

What happens when we delete keys

Database

On the database side, it invokes [dbo].[DeleteKey].

SQL

And, here is the SQL.


ALTER PROCEDURE [dbo].[DeleteKey]
@InstallationID uniqueidentifier
AS

if (@InstallationID = '00000000-0000-0000-0000-000000000000')
RAISERROR('Cannot delete reserved key', 16, 1)

-- Remove the encryption keys
delete from keys where InstallationID = @InstallationID and Client = 1


Output

listKeysForMachine

Ramification
  1. If you check your database table and do not see entries matching your MachineName and InstanceName, then you likely not having problems with existing Database keys
  2. Keep in the mind that the record for the null installationID ( 00000000-0000-0000-0000-000000000000 ) remains

 

Clean Database

Another avenue to try is to start with a fresh database

GUI

Database Setup – Database Connection

On the “Database Setup” \ “Database Connection” page, click the “New…” button

DatabaseSetup-DatabaseConnection

 

SQL Server Connection Dialog

Enter parameters for Server name, new database name, etc….

SQLServerConnectionDialog

 

Retry accessing the web site, and if problems remain, then you likely do not have encryption key problems after all

Key not valid for use in specified state

What is the Reporting Services LogOn/Service Account?

ServiceAccount-LogON

In our case, it is a local account called ASPNetServiceAccount.

Find the Key

Based on the service’s account name find the crypto key…

OS Folder
Win 2008/R2 C:\users\%_user%\AppData\Roaming\Microsoft\Crypto\RSA\S-1-5-21-2652458360-3351814613-1943000632-1043
 Windows 2003  C:\Documents and Settings\%_user%\Application Data\Microsoft\Crypto\RSA\S-1-5-21-2189112842-255748226-437124553-1121

 

 

 

View  Key Contents

fileContents

 

Contents Reviewed

  1. Microsoft SQL Server Reporting Services Key Container
    • It is  indeed Reporting Services Key

 Key Renamed

crytoKeyRenamed

Restart related services

Please restart related services, such as

  1. Reporting Services
  2. World Wide Web Publishing Service

Crediting

Crediting MSFT’s Jin Chen

CreditingJinChen

 

Listening To

Elevation Worship – Here As In Heaven (Acoustic)
Link

References

  1. Key not valid for use in specified state with an exception
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s