SOLVED
Home

Always encrypted and Key Vault in SSRS

%3CLINGO-SUB%20id%3D%22lingo-sub-655928%22%20slang%3D%22en-US%22%3EAlways%20encrypted%20and%20Key%20Vault%20in%20SSRS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-655928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20%20BCX5%20SCXW253647968%22%3E%3CSPAN%20class%3D%22NormalTextRun%20%20BCX5%20SCXW253647968%22%3EWe%20are%20working%20on%20a%20prototype%20for%20a%20solution%20and%20I%20want%20to%20use%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20%20BCX5%20SCXW253647968%22%3E%3CSPAN%20class%3D%22NormalTextRun%20%20BCX5%20SCXW253647968%22%3E%E2%80%AFAlways%20Encrypted%20to%20encrypt%20certain%20sensitive%20database%20columns%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20setup%20is%20a%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Efollows%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESQL%20Server%202016%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E(Virtual%20Machine%20in%20Azure)%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EReporting%20Server%202016%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESQL%20Management%20Studio%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EReport%20Builder%202016%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAzure%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ekey%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EVault%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW209052078%20BCX5%22%3EI%20can%20view%20the%20encrypted%20data%20from%20my%20Web%20App%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW209052078%20BCX5%22%3Ewithout%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW209052078%20BCX5%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22ContextualSpellingAndGrammarError%20SCXW209052078%20BCX5%22%3Eproblem%3C%2FSPAN%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW209052078%20BCX5%22%3E%26nbsp%3Band%20the%20DBA%20can%20read%20the%20encrypted%20data%20directly%20from%20the%20database%2C%20so%20I%20am%20assuming%20that%20my%20environment%20is%20set%20up%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW209052078%20BCX5%22%3E%26nbsp%3Bcorrectly%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW209052078%20BCX5%22%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW209052078%20BCX5%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3EAs%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3Eexplained%2C%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E%26nbsp%3BI%20have%20SSRS%202016%20installed%20on%20the%20Application%20Server%20but%20pointing%20to%20the%20database%20with%20encrypted%20columns%20on%20the%20database%20server.%20I%20have%20done%20a%20basic%20dump%20report%20(for%20testing%20purposes)%20using%20Report%20Builder%20of%20course%20and%20all%20works%20well%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3Eexcept%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3Ethat%20the%20encrypted%20data%20is%20not%20displayed%20-%20it%20is%20remaining%20blank%20in%20the%20SSRS%20Table%20The%20encrypted%20column%20is%20just%20a%20basic%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22SpellingError%20SCXW186931618%20BCX5%22%3Envarchar%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E(%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E250).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW35592809%20BCX5%22%3EIn%20the%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22SpellingError%20SCXW35592809%20BCX5%22%3Edatasource%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW35592809%20BCX5%22%3E%26nbsp%3Bconnection%20string%20I%20have%20added%20'Column%20Encryption%20Setting%20%3D%20Enabled'.%20Without%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW35592809%20BCX5%22%3Ethis%20parameter%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW35592809%20BCX5%22%3E%26nbsp%3Bthe%20report%20display%20%23Error%20as%20expected.%20So%2C%20I%20am%20assuming%20that%20this%20is%20needed%20as%20well.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW35592809%20BCX5%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22EOP%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW165160982%20BCX5%22%3ESomething%20that%20I%20noticed%20is%20that%20from%20the%20Query%20Designer%20I%20can%20read%20the%20encrypted%20column.%20if%20I%20remove%20'Column%20Encryption%20Setting%20%3D%20Enabled'%20from%20the%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22SpellingError%20SCXW165160982%20BCX5%22%3Edatasource%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW165160982%20BCX5%22%3E%26nbsp%3Bthe%20Query%20Designer%20displays%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22SpellingError%20SCXW165160982%20BCX5%22%3EVarBinary%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW165160982%20BCX5%22%3E.%20I%20am%20working%20with%20Report%20Builder%20and%20Query%20Designer%20directly%20on%20the%20Application%20server%20of%20course.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW165160982%20BCX5%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22EOP%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22EOP%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW3418275%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW3418275%20BCX5%22%3EI%20tried%20to%20search%20for%20any%20tutorials%20on%20how%20to%20use%20SSRS%20with%20Always%20Encrypted%20but%20I%20couldn't%20find%20anything.%20All%20I%20found%20is%20a%20comment%20in%20a%20post%20that%20SSRS%20supports%20Always%20Encrypted.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW3418275%20BCX5%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW209052078%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW186931618%20BCX5%22%3E%3CSPAN%20class%3D%22EOP%20SCXW35592809%20BCX5%22%3E%3CSPAN%20class%3D%22EOP%20SCXW165160982%20BCX5%22%3E%3CSPAN%20class%3D%22EOP%20SCXW3418275%20BCX5%22%3E%3CSPAN%20class%3D%22TextRun%20Highlight%20SCXW76864240%20BCX5%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW76864240%20BCX5%22%3EThanks%20in%20advance%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22EOP%20SCXW76864240%20BCX5%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-657164%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20encrypted%20and%20Key%20Vault%20in%20SSRS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-657164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351364%22%20target%3D%22_blank%22%3E%40diegoHernandezb15%3C%2FA%3E%26nbsp%3BPlease%20use%20below%20link.%20you%20need%20to%20install%20a%20certificate%20on%20the%20server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlservercentral.com%2Fforums%2Ftopic%2Falways-encrypted-with-ssrs-2016%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlservercentral.com%2Fforums%2Ftopic%2Falways-encrypted-with-ssrs-2016%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658748%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20encrypted%20and%20Key%20Vault%20in%20SSRS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349918%22%20target%3D%22_blank%22%3E%40Nirav_Gandhi%3C%2FA%3E%26nbsp%3BWhere%20the%20certificate%20is%20generated%20and%20how%20it%20is%20generated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666157%22%20slang%3D%22en-US%22%3ERe%3A%20Always%20encrypted%20and%20Key%20Vault%20in%20SSRS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351364%22%20target%3D%22_blank%22%3E%40diegoHernandezb15%3C%2FA%3E%26nbsp%3BPlease%20read%20below%20link.%20all%20details%20are%20mentioned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sqlservercentral.com%2Fforums%2Ftopic%2Falways-encrypted-with-ssrs-2016%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sqlservercentral.com%2Fforums%2Ftopic%2Falways-encrypted-with-ssrs-2016%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sslshopper.com%2Farticle-how-to-create-a-self-signed-certificate-in-iis-7.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sslshopper.com%2Farticle-how-to-create-a-self-signed-certificate-in-iis-7.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
diegoHernandezb15
New 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
Solution

@diegoHernandezb15 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies