Forum Discussion
Always encrypted and Key Vault in SSRS
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
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_GandhiCopper Contributor
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
- DiegoHernandezBañosCopper Contributor
Nirav_Gandhi Where the certificate is generated and how it is generated
- Nirav_GandhiCopper Contributor
DiegoHernandezBaños Please read below link. all details are mentioned.
https://www.sqlservercentral.com/forums/topic/always-encrypted-with-ssrs-2016
https://www.sslshopper.com/article-how-to-create-a-self-signed-certificate-in-iis-7.html