Azure Analysis Services, decrypt data from SQL Server

%3CLINGO-SUB%20id%3D%22lingo-sub-2112577%22%20slang%3D%22en-US%22%3EAzure%20Analysis%20Services%2C%20decrypt%20data%20from%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2112577%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20using%20always%20encrypted%20to%20encrypt%20data%20in%20certain%20columns%20in%20the%20database%20(Azure%20SQL%20Managed%20Instance)%20by%20storing%20the%20key%20in%20Azure%20Key%20Vault.%20Is%20it%20possible%20to%20encrypt%20the%20data%20while%20processing%20the%20cubes%20that%20we%20have%20in%20Azure%20Analysis%20Services%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113916%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Analysis%20Services%2C%20decrypt%20data%20from%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370433%22%20target%3D%22_blank%22%3E%40Fosse83%3C%2FA%3E%26nbsp%3BFor%20the%20certificate%20piece%2C%20try%20and%20install%20the%20certificate%20on%20the%20data%20gateway%20VM.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegarding%20the%20key%20vault%2C%20I%20haven't%20tested%20it%20but%20try%20and%20grant%20the%20Analysis%20Services%20Service%20Principal%20that%20does%20your%20processing%20access%20to%20read%20from%20the%20key%20vault%2C%20like%20outlined%20in%20this%20document%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkey-vault%2Fgeneral%2Frbac-guide%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EGrant%20permission%20to%20applications%20to%20access%20an%20Azure%20key%20vault%20using%20Azure%20RBAC%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113624%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Analysis%20Services%2C%20decrypt%20data%20from%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113624%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F296281%22%20target%3D%22_blank%22%3E%40msftchris%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20reply!%20I%20have%20had%20a%20look%20at%20that%20one%2C%20but%20I%20fail%20to%20get%20it%20working%20due%20to%20the%20fact%20that%20we%20have%20our%20key%20stored%20in%20Azure%20Key%20Vault.%20I%20am%20not%20sure%20that%20storing%20the%20key%20in%20key%20vault%20is%20supported%20by%20AS%2C%20but%20I%20cant%20find%20any%20official%20documentation%20on%20this.%20I%20am%20also%20not%20sure%20on%20how%20I%20can%20access%20Azure%20AS%20in%20order%20to%20store%20the%20certificate%20on%20the%20server.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113516%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Analysis%20Services%2C%20decrypt%20data%20from%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113516%22%20slang%3D%22en-US%22%3EHi%20fosse83%2C%3CBR%20%2F%3E%20Yes%20this%20can%20be%20done.%20See%20this%20link%20for%20guidance.%20Although%20it%20refers%20to%20ssas%20it%20is%20the%20same%20process%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.kasperonbi.com%2Fuse-always-encrypted-data-with-ssas-and-power-bi%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.kasperonbi.com%2Fuse-always-encrypted-data-with-ssas-and-power-bi%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EHTH%2C%3CBR%20%2F%3EChris%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

We are using always encrypted to encrypt data in certain columns in the database (Azure SQL Managed Instance) by storing the key in Azure Key Vault. Is it possible to encrypt the data while processing the cubes that we have in Azure Analysis Services?

3 Replies
Hi fosse83,
Yes this can be done. See this link for guidance. Although it refers to ssas it is the same process:

https://www.kasperonbi.com/use-always-encrypted-data-with-ssas-and-power-bi/

HTH,
Chris

@Chris-Schmidt-MSFT Thanks for the reply! I have had a look at that one, but I fail to get it working due to the fact that we have our key stored in Azure Key Vault. I am not sure that storing the key in key vault is supported by AS, but I cant find any official documentation on this. I am also not sure on how I can access Azure AS in order to store the certificate on the server. 

@Fosse83 For the certificate piece, try and install the certificate on the data gateway VM. 

 

Regarding the key vault, I haven't tested it but try and grant the Analysis Services Service Principal that does your processing access to read from the key vault, like outlined in this document: 

Grant permission to applications to access an Azure key vault using Azure RBAC | Microsoft Docs