Decryption in Power BI

[vsutar123]

Hi All,

Is there any way to Power BI decrypt the data before showing on the reports? Any alternative? Does Microsoft have any plans to bring this feature in any future release?

[hackcrr · Accepted solution]

Hi, @vsutar123 

Power BI itself currently does not support built-in decryption of encrypted data in its reporting capabilities. Data encryption and decryption is typically handled at the database or application level, not directly in Power BI.
There are several ways to implement decryption before displaying the data in Power BI:
You can decrypt the data in a SQL query using DECRYPTBYKEY or other SQL Server decryption functions, depending on how the data was encrypted. Here is an example using symmetric key encryption:

OPEN SYMMETRIC KEY SymKey DECRYPTION BY CERTIFICATE CertName;
SELECT
    CONVERT(varchar, DECRYPTBYKEY(EncryptedColumn)) AS DecryptedData
FROM
    YourTable;
CLOSE SYMMETRIC KEY SymKey;

Create SQL views or stored procedures on the server that decrypt the data at the database level. Power BI will connect to these views or stored procedures and get the decrypted data.
This ensures that all sensitive operations (encryption, decryption) are handled securely in the database.

Power BI is focused on visualization, and sensitive operations such as decryption should be performed securely in the database or through a custom middleware/API layer.

If I have answered your question, please mark my reply as solution and kudos to this post, thank you!

[MallikarjunaBan]

i have encrypted data in snowflake can i decrypt in power bi service on the fly while i use direct query or can i achive by having udf at report server level .as of now i use cognos bi and snowflake encrypted data where i am creating udf to decrypt on the fly in cognos while using direct query .need same functionality in power bi can we do it with byok pls confirm

[lbendlin]

Data in Power BI is already encrypted.  Are you referring to BYOK (Bring your own key) ?

[vsutar123]

The source data(SQL Server) is in encrypted state and should be decrypted when it is displayed on Power BI reports like paginated report etc.

[MallikarjunaBan]

i have encrypted data in snowflake can i decrypt in power bi service on the fly while i use direct query or can i achive by having udf at report server level .as of now i use cognos bi and snowflake encrypted data where i am creating udf to decrypt on the fly in cognos while using direct query .need same functionality in power bi can we do it with byok pls confirm

[SaiTejaTalasila]

Hi @vsutar123 ,

You can try to decrepit it using python on fabric notebooks and you can store that data on onelake/lakehouse and you can build reports on top of that data.

I hope it will be helpful.

Thanks,

Sai Teja 

[lbendlin]

Ah, the good old "We don't support SQL encryption"  issue.  I don't know what the current status is. May be worth raising another ticket.