COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Framework Manager => Topic started by: MarkV on 16 Mar 2018 11:02:53 AM

Title: Decrypting Data in FWM
Post by: MarkV on 16 Mar 2018 11:02:53 AM
I have a requirement that I'm struggling trying to try and come up with a valid solution for.  Here's the requirements:

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

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
Title: Re: Decrypting Data in FWM
Post by: MarkV on 20 Mar 2018 03:41:06 PM
It would appear using a combination of several things solves, for the most part, my issues.  My database platform is MS SQL Server.

This solves several issues:

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.