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

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
------------------------------

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
------------------------------

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

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