Always Encrypted, Impersonation and Reporting Services (SSRS)

Question

Scenario

Table1 has encrypted data using always encypted, with certificate given only to user1, and reporting services service account serviceAccount1
SSRS is running under serviceAccount1
User1 has access/certificate to Table1

serviceAccount1 has impersonation enabled so that reports can connect through and connect to Table1 as User1.

Objective
Prevent anyone with access to the reporting server/ service account from decrypting the data.

My problem
Even though impersonation is enabled and the users credentials are being used to connect to the database, the certificate has to be installed in the service account's personal vault. therefore anyone accessing the report can view the encrypted data, by creating their own datasource. Therefore the security becomes as simple as database security and the encryption piece is now weak.

Any way to allow ONLY the user being impersonated able to access the certificate and the encrypted data?

Answer 1

Hi @bc dba ,

Welcome to Microsoft Q&A!
Please refer to this article: https://www.mssqltips.com/sqlservertip/4814/exporting-and-importing-sql-server-always-encrypted-certificates-for-client-access/
Your problem seems to have no other way except not using impersonation.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.