There are several different ways to implement encryption in ms sql. i believe ours is a certificate that is then used to encrypt a symmetric key.
below works in our system to decrypt
cast(DecryptByKeyAutoCert(cert_id('certificatename'), null, [encryptedcolumnname]) as varchar) as decryptedvalue
I assume that your datasource signin would be db_datareader. if so then you would need to execute the following to your sql login that is used by the cognos -> datasource -> dataconnection -> signin
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: symmetrickey TO SQL_LOGIN
GRANT CONTROL ON CERTIFICATE:: certificate to SQL_LOGIN
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------
Original Message:
Sent: Tue January 21, 2020 07:49 AM
From: Kiran Passumarthi
Subject: Decrypt SSN column in Report Studio
In SQL Server it would be something like this....
Select
CONVERT(nvarchar(100), DecryptByKeyAutoAsymKey ( AsymKey_ID(<asymmetric key>) , NULL ,<tblEmployee.ssn_encrypted_table_column>)) AS SSN_decrypted
From
tblEmployee
------------------------------
Kiran Passumarthi
www.linkedin.com/in/kiranpassumarthi
Original Message:
Sent: Tue January 21, 2020 07:13 AM
From: Sai B
Subject: Decrypt SSN column in Report Studio
Thanks Kiran. Is there any specific query template you can provide, I would appreciate it.
Thank You.
------------------------------
Sai B
Original Message:
Sent: Tue January 21, 2020 02:27 AM
From: Kiran Passumarthi
Subject: Decrypt SSN column in Report Studio
Hi,
If SSN is encrypted using database certificate then you need to use the cert to decrypt. You can write a sql query in FM database query subject to decrypt using the cert and use in the model.
------------------------------
Kiran Passumarthi
www.linkedin.com/in/kiranpassumarthi
Original Message:
Sent: Mon January 20, 2020 10:56 AM
From: Sai B
Subject: Decrypt SSN column in Report Studio
Hello,
I have a requirement from my client to decrypt the SSN column in the report. Currently only encrypted value is being showed in the SSN column in the report. SSN field is encrypted in the database itself. Is there a way to get it decrypted in Framework Manager or Report Studio and show the full SSN field in the report.
Thanks!
------------------------------
Sai B
------------------------------
#CognosAnalyticswithWatson