x

Decrypting Datasources in SSRS

Hello,

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.
more ▼

asked Sep 17 '10 at 05:29 AM in Default

otto88 gravatar image

otto88
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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][1] page has a great deal of information about how to delete or re-create encryption keys in SSRS.

Oleg

[1]: http://msdn.microsoft.com/en-us/library/ms156010.aspx
more ▼

answered Sep 17 '10 at 08:29 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

+1 - for length!
Sep 17 '10 at 12:33 PM Fatherjack ♦♦
@Fatherjack You mean it did not make any sense? :(
Sep 17 '10 at 01:03 PM Oleg
No, not at all. There is simply a huge amount of it, it wholly answers the question but your effort put into explaining it rather than simply "No" is commendable and dwarfs the question. Is it something you have reviewed in depth or find particularly interesting? Apologies if my flippancy caused any offence.
Sep 17 '10 at 01:32 PM Fatherjack ♦♦
@Oleg - I also now notice that I added an answer rather than a comment. Ga'h, time to put the keyboard away for the night I think
Sep 17 '10 at 01:33 PM Fatherjack ♦♦
@Oleg, just curious if you are on Twitter or have a blog.
Sep 17 '10 at 01:45 PM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x536
x37

asked: Sep 17 '10 at 05:29 AM

Seen: 1873 times

Last Updated: Sep 17 '10 at 05:29 AM