CODEWITHSUNDEEP
sql-server-2016ssrs-2016always-encrypted
J Pace
J Pace

Reputation: 21

No data shown in tablix of SSRS 2016 when retreiving columns with Always Encrypted

I am working on a prototype for an upcoming big solution and wish to use Always Encrypted to encrypt certain sensitive database columns.

My setup is a follows: Database Server: SQL Server 2016 installed Application Server: Reporting Server 2016 installed pointing to the Database Server engine. IIS, .Net 4.6.2 etc. all setup as well.

The environment is also setup in a way that the DBA can't read the encrypted data even if from Management Studio he will add the 'Column Encryption Setting = Enabled' in the connection. So my certificate is installed on the Application Server while the CMK and CEK are installed on the Database Server database.

I can view the encrypted data from my Web App installed on the Application Server with no problem, and the DBA can't read the encrypted data directly from the database, so I am assuming that my environment is well set up.

As explained I have SSRS 2016 installed on the Application Server but pointing to the database with encrypted columns on the database server. I have done a basic dump report (for testing purposes) using Report Builder of course and all works well EXCEPT that the encrypted data is not displayed - it is remaining blank in the SSRS Table! The encrypted column is just a basic nvarchar(200)

In the datasource connection string I have added 'Column Encryption Setting = Enabled'. Without it the report display #Error as expected. So I am assuming that this is needed as well.

Something also that I noticed is that from the Query Designer I can read the encrypted column. if I remove 'Column Encryption Setting = Enabled' from the datasource the Query Designer displays VarBinary if I remember correctly. I am working with Report Builder and Query Designer directly on the Application server of course.

I tried to search for any tutorials on how to use SSRS with Always Encrypted but I couldn't find anything. All I found is a comment in a post that SSRS supports Always Encrypted.

Can someone please enlighten what I am doing wrong or what I am missing?

Thanks in advance.

Upvotes: 2

Views: 1442

Answers (2)

kevinjm
kevinjm

Reputation: 1

The account that is running the SSRS service needs to have read permission on the Always Encrypted certificate in the Local Machine store. Right click the certificate, select All Tasks – Manage Private Keys and then provide the SSRS service account read permissions on the certificate.

Upvotes: 0

Jakub Szymaszek - Microsoft
Jakub Szymaszek - Microsoft

Reputation: 596

Disclaimer: I am a Program Manager at Microsoft.

To troubleshoot your issue, please try to run the problematic query (the one that returns no data in Report Builder) from SQL Server Management Studio on the same machine (that you run Report Builder from) and as the same user, over a connection with 'Column Encryption Setting = Enabled' and see, if you get any error message.

I have seen a query against encrypted columns returning no results in Report Builder, if the certificate is not deployed on the machine, hosting Report Builder, or the user does not have a permission to access the certificate. Do you store the certificate (used as a column master key) in the Current User or Local Machine store? If it is in the Local Machine store, you need to ensure that you (the user who runs Report Builder) have the permissions to access the certificate (you can configure permissions on a certificate using Management Console).

Upvotes: 1

Related Questions