SSAS Tabular model access to Always Encrypted data

Question

Using:

Visual Studio Professional 2019.
SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Windows 10 Enterprise 10.0 <X64> (Build 17763: )
SQL Server SQL Server Management Studio 15.0.18369.0
SQL Server Management Objects (SMO) 16.100.46041.41
Microsoft Analysis Services Client Tools 15.0.19342.0
Microsoft Data Access Components (MDAC) 10.0.17763.1
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.17763

Visual Studio Projects, SQL Server and SSAS instances are all on same local computer.

Creating tabular mode SSAS model in Visual Studio.

Data source uses Always Encrypted for sensitive columns in several tables.
When not logged into SSMS with Always Encrypted, data appears encrypted as expected (Image 1)
![71090-image.png][1]

When logged in with Always Encrypted, same query decrypts (Image 2)
![71119-image.png][2]

So far so good. Now, starting Analysis services tabular project in Visual Studio.
Using Workspace Server and SQL Server 2019 compatibility mode per Microsoft recommendations (Image 3)
![71144-image.png][3]

Importing data from SQL Server datasource containing the table shown earlier (Images 4/5).
Logged in as sa, impersonating service account.
![70919-image.png][4]
![71154-image.png][5]

Attempt to establish encrypted connection failed message appears (shouldn't be a problem - don't require that right now on this server)...click OK and connection succeeds presenting a list of databases to connect to (Image 6) - names obscured
![71161-image.png][6]

Upon selecting the target database, tables list is presented, and I select the desired example table (e.g. 'Appointment') - (Image 7)
![71171-image.png][7]

Selected 'Transform Data' which opens the Power Query Editor....eliminated all columns except the primary key column and those columns I know to be Always Encrypted in the database. As can be seen - the data is browsed with the encrypted columns displayed as 'Binary' (Image 8)
![71181-image.png][8]

Select Close and Apply from Home menu. System indicates data loaded without error (Image 9)
![71182-image.png][9]

Upon closing the result dialog, the table is shown in the model browser (Image 10)
![71099-image.png][10]

As shown, the data is still encrypted. So far I have seen no option to connect to the SSAS server with a connection parameter equivalent to the SSMS Always Encrypted option.

I also cannot find any specific guidance from Microsoft or any where else that addresses this. Since encrypted columns are a ubiquitous necessity in today's databases, this seems like a serious oversight on Microsoft's part.

Our customers require reports that can display their customers' data, so just not showing the encrypted columns is not an option.

I am trying out the suggestion made by @Darren Gosbell in response to my earlier question at https://learn.microsoft.com/en-us/answers/questions/278767/ssas-dimension-with-encryptedcolumn.html - whereby a registry setting can cause the display of the legacy connection dialog that appears when using earlier compatibility modes.

That dialog provides for the selection of encryption. When I test that I will post my results back here. Meanwhile that seems like a hack (when this gets into a production environment, Ops is just gonna love having to set registry flags!).

So - anyone out there that is a guru on TODAY's version of SSAS (not 10 years ago) - any guidance would be appreciated.

[1]: /api/attachments/71090-image.png?platform=QnA [2]: /api/attachments/71119-image.png?platform=QnA [3]: /api/attachments/71144-image.png?platform=QnA [4]: /api/attachments/70919-image.png?platform=QnA [5]: /api/attachments/71154-image.png?platform=QnA [6]: /api/attachments/71161-image.png?platform=QnA [7]: /api/attachments/71171-image.png?platform=QnA [8]: /api/attachments/71181-image.png?platform=QnA [9]: /api/attachments/71182-image.png?platform=QnA [10]: /api/attachments/71099-image.png?platform=QnA

Answer 1

You can use the steps outlined in this article (which you used in your previous post). Note step 1 talks about using the legacy data sources. There are details in this other article on how to enable those.

Answer 2

Update:

I have done everything in the articles suggested...

*Set SSAS to tabular mode per "https://www.sanssql.com/2013/11/change-ssas-deployment-mode-from.html".
*Altered model.bin to use legacy connection per "https://www.businessintelligenceinfo.com/business-intelligence/self-service-bi/using-legacy-data-sources-in-tabular-1400".
*Later on, accomplished same thing with Visual Studio Tools->Options->Analysis Services Tabular->Data Import->Enable Legacy Data Sources
*In connection, specified Column Encryption Setting Enabled
* Set Trust Certificate to True (self-signed cert running here)
*Impersonate Service Account
*Verified that within SSMS, Master/CEK keys are correct, columns encrypted. With SQL Server connection Encryption enabled I can browse encrypted
data.
*Created simple Tabular SSAS project. One table with 1 Encrypted Column.
*As a test, I imported a table with no encrypted columns - no errors. Browsed fine - data all there.
*Then imported the table having an encrypted column - no errors BUT --- encrypted column is omitted from the displayed grid.

Tried creating a stored proc on the SQL Server that returns the entire table (SELECT * FROM xxxxx). Then execute that query as my table import in SSAS --- same thing - loads everything but the encrypted columns.

I hear everyone saying it 'can' be done....has anyone actually DONE this?

I would love to see an example of someone being able to browse an encrypted column from within SSAS tools in Visual Studio...

I'm out of ideas.