Decrypting Data in FWM

[MarkV]

I have a requirement that I'm struggling trying to try and come up with a valid solution for.  Here's the requirements:

• Data is encrypted with a passphrase.
• Encrypted data is stored in the database.
• The passphrase cannot be stored on the database server.
• We have multiple client databases.  Each database will have it's own passphrase.  We use the same model for all clients.
• The model needs to be published in DQM.

I'm trying to use FWM to configure the model to use a passphrase.  I've attempted the following approaches:

• XML data source stores passphrase - Externalizing the passphrase here works.  However, DQM is not supported with XML data sources. (https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_fm.doc/c_dqm_dyn_query.html)
• Use a Parameter Map or Session Parameter to store the passphrase - This works.  However, the negative is that a report author can see the passphrase in the generated SQL statement.  And,
  with every deployment of reports, the passphrase will need to be updated for each client and republished.

My understanding is that IBM recommends that the database server handle the decryption.  However, company requirements are that the passphrase not be stored in the database server.

Additionally, pulling in the passphrase via another data source seems problematic because that passphrase will eventually need to be passed to another data source.  The SQL that Cognos generates will fail because the SQL statement will be referencing both data sources; this would cause Cognos to try and run the command locally.  This would fail because the SQL command to decrypt the data has to happen on the database server (not local to Cognos) since Cognos does not support this decrypt function.

A coworker suggested using a web service that returns the passphrase.  It would appear CA does not support web services as a data source but is slated to.  Even if it did, I believe I'd run in to the issue in the previous paragraph with using multiple data sources in the same query subject where the decrypt function is using a value from the other data source.

When I tried using an XML file in CQM mode, I had two data source query subjects (one from each data source with a relationship created between the two) being used by a model query subject but it errored out with the following error "RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0219 The function "decryptbypassphrase" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.".  This tells me that the decrypt function call has to happen in a data source query subject.

I'm afraid my only solution at this point is the Parameter Map or Session Parameter approach.  But, since the passphrase is visible, that defeats the security around protecting the passphrase.

Does anyone have any other suggestions about how I might be able to decrypt data and still adhere to the requirements above.

Thank you for your input.

Mark

[MarkV]

It would appear using a combination of several things solves, for the most part, my issues.  My database platform is MS SQL Server.

• Create a database table to temporarily store the passphrase.
• Create a stored procedure to insert the passphrase in the table.  Pass the passphrase as a parameter.
• Create a stored procedure that's remove the passphrase from the table.  Pass the passphrase as a parameter.
• Configure the Open Session Command to run a command-block that executes the stored procedure to insert the passphrase.  The passphrase is hard-coded here.
• Configure the Close Session Command to run a command-block that executes the stored procedure to clear the passphrase from the database table.
• Give appropriate execute permissions to the two stored procedures to the correct user account.
• Convert the data source query subject to a pass-through query and then join the table with the encrypted value with the passphrase table.  Join these on a key column that you make sure to create between the two tables.

This solves several issues:

• The passphrase is not viewable in the SQL statement.
• With the passphrase stored in the Open Session Command block, new deployments will not require updating the passphrase every time.
• This approach supports DQM.
• It adheres to IBM's recommendation to having the database server do the decrypting.

Of course, storing the passphrase in the database temporarily is still storing it in the database.  Hopefully, my developers will be OK with the approach of storing it temporarily.

I tried using temp and global temp tables but SQL Server automatically drops them once the Open Session Command SQL statement finishes running.  And, SQL statements don't validate when referencing temp tables.

A big thank you to IBM support for pointing me in the direction of Open Session Command blocks and Close Session Command blocks.