Is there any way to decrypt the Username and Password columns in the Datasource table of a reporting server. I know the phrase that was used to generate the encryption key when the Report Server was configured. I suspect that knowing that and using the information in the Keys table, I should be able to write a sql statement that allows me to decrypt this data, but I can't seem to figure out how to do it.Any help would be appreciated.
asked Sep 17 '10 at 05:29 AM in Default
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:
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.
answered Sep 17 '10 at 08:29 AM