SOLVED

Always encrypted and Key Vault in SSRS

Copper Contributor

We are working on a prototype for a solution and I want to use Always Encrypted to encrypt certain sensitive database columns

 

My setup is a follows: 

SQL Server 2016 (Virtual Machine in Azure) 

Reporting Server 2016  

SQL Management Studio 

Report Builder 2016 

Azure key Vault 

 

I can view the encrypted data from my Web App without problem and the DBA can read the encrypted data directly from the database, so I am assuming that my environment is set up correctly. 

 

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(250).

 

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

 

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

 

Thanks in advance 

 

 

3 Replies
best response confirmed by DiegoHernandezBaños (Copper Contributor)
Solution

@DiegoHernandezBaños Please use below link. you need to install a certificate on the server.

 

https://www.sqlservercentral.com/forums/topic/always-encrypted-with-ssrs-2016

 

@Nirav_Gandhi Where the certificate is generated and how it is generated

1 best response

Accepted Solutions
best response confirmed by DiegoHernandezBaños (Copper Contributor)