RTC - How to decrypt enumeration literals that are encrypted in the database?
We're building external reports based on data stored in our RTC database. We are querying the database directly. For some reason, RTC encrypts these enumeration literals in the database. There doesn't appear to be an option in RTC/CCM to specify whether certain fields are encrypted or not (these are not sensitive fields, why can't we specify whether to have them encrypted or not?). It appears my only option is to decrypt these manually. What is the encryption used? Is there a default key? If not, where can I find the key and was it set during the initial configuration of the server? Any help would be appreciated.
Database: SQL Server 2008 RTC Version: 4.0.1 Query to get specified encrypted field: USE RTC SELECT I.ITEM_VALUE FROM REPOSITORY.ITEM_STATES AS I Thanks, |
Accepted answer
@ben the Item_value field is a gzipped blob, if you unzip it you can get the xml.
Ben Newell selected this answer as the correct answer
Comments Thanks for the response. So these enum literals ARE stored in this field/column ( REPOSITORY.ITEM_STATES.ITEM_VALUE)? And the value of the field is a gzipped blob that contains XML that specifies what enum has been chosen?
@ben from my knowledge it is XML.
|
One other answer
Ralph Schoon (63.1k●3●36●46)
| answered Mar 25 '13, 11:59 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
You should not work on DB level, especially not write to it. You should use the API's to access the data. If you work on the DB level, you will be potentially hit by schema changes. And you will probably run into many places where plain SQL does not give you the data you want, or the data is stored in blobs and zipped.
Comments
Ben Newell
commented Mar 25 '13, 12:40 p.m.
Thanks for the warning, but we have quite a few reports that pull directly from the database and they work perfectly for our use. We've even updated from v3 to v4 and the queries/reports still worked fine. I guess we got lucky. We may try the API route if we do, indeed, start running into issues though. So far it's been fine.
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.