Decrypting Datasources in SSRS

I believe that knowing the phrase will not be enough. When the server was configured, it received just a pulic key from the Cryptographic Services. The latter of course created an X509 certificate which has both public (used for encrypting) and private (used for decrypting) keys (RSA asymmetric algorithm, 1024 bit strong). The public key is now stored in the SSRS database. The phrase was used for just one purpose: to feed it to the Cryptographic Services so the latter can generate symmetric key. This symmetric key was encrypted by using a public key now stored in SSRS. This encrypted symmetric key is now stored in the SSRS database as well. The data which needed to be encrypted is encrypted as follows:

• SSRS retrieves encrypted symmetric key from its data store
• SSRS (which cannot decrypt the key) calls Cryptographic Services and asks to decrypt the key
• Cryptographic Services uses the private key to decrypt RSA encrypted symmetric key and returns the key to SSRS
• SSRS uses the decrypted key to encrypt sensitive data using one of the 2 symmetric algoritms: 168 bit strong TripleDES or (potentially) 256 bit strong Rijndael

The symmetrically encypted data is decrypted in the same fashion: encrypted symmetric key is decrypted with the private key stored by Cryptographic Services and then used to decrypt symmetrically encrypted data by using one of the 2 symmetric algorithms, Bob's your uncle.

The procedure to encrypt symmetric key using asymmetric RSA and encrypt/decrypt the data using symmetric TripleDES or Rijndael is used by many compliant secure solutions: SSL of https protocol, custom SSO solutions etc. It is extremely convenient because it allows both: a secure storage of the key (RSA is slow but very strong) and fast symmetric algorithms to actually encrypt/decrypt sensitive data.

The bottom line is that SSRS does not store enough information to just decrypt encrypted values, so I would not know how to help with writing a T-SQL query to do it. Even if you knew the decrypted value of the symmetric key, you would still needed to know which algorithm was used and how initialization vectors were generated (TripleDES needs 8 bytes IV and Rijndael needs 16 bytes IV to work). However, because SSRS is able to decrypt and render sensitive data, it should be possible to exract it somewhere and then use as you see fit.

This MSDN page has a great deal of information about how to delete or re-create encryption keys in SSRS.

Oleg