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
Highlighted
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
46 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 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
Extentions Synchronization
Deleted in Discussions on
3 Replies