OBIEE 12c - how to decrypt data coming from MS SQL server 14.0 tables

User_INLWQ

Hi Gurus,

Here is the requirement.  We are on OBIEE 12c and data source is SQL Server version 14.  We have some of the columns in the tables of SQL Server encrypted and they need to be decrypted in OBIEE.

This can be achieved in RPD or front end analytics ?

Please share any inputs on this.

Thanks.

[Deleted User]

If OBI could just decrypt anything coming from SQL server that would pretty much go against the concept of encryption of data.

MS uses DECRYPTBYKEY in transact SQL but it's query by query...you can't "configure the physical layer" to automatically do this. Unless you write it all in views.

User_INLWQ

Hi Chris,

Could you please elaborate "Unless you write it all in views"

Write what ?  and where ?

Thanks.

[Deleted User]

If you look at this: https://social.technet.microsoft.com/wiki/contents/articles/37513.sql-server-2016-implement-column-level-encryptiondecry…

You can see that you decrypt SQL statement by SQL statement. And OBI simply doesn't work like that. OBI works with models. Not queries. So if you want to use decrypted data coming from SQL Server you have to decrypt before  things arrive in OBI.

Now inside the RPD you *can* write opaque views, which are basically code, but you definitely need to weigh this vs writing the views inside the SQL Server DB as native objects since obviously you need to include the decryption key in the view definition.

User_INLWQ

Thanks Chris,  will there be any performance impact if we go with Opaque views vs direct SQL server native declaration ...

[Deleted User]

My concern is not about performance but the visibility of the decryption in the RPD metadata.

User_INLWQ

Asking abt performance because Oracle says Opaque views should be the last option and complex sql need to be generated to retrieve data using them.