Passing some (any) context information from the SSAS connection string to the underlying database
I would like to be able to "sort of" impersonate a user in a SQL database. Is there any part of the analysis server connection string that is then passed to the underlying database connection string? In my scenario, the SSAS instance uses a direct query to query the analytic database.
There are ways to pass some context to SSAS itself via a connection string, namely:
-
CustomData
-
Extended Properties
-
Application Name
However, as far as I can tell, nothing is being passed to the underlying SQL database.
Long story
I have a complex system with permissions calculated at the SQL level that will not change. This is internally implemented in such a way that the application connecting to the SQL server calls SET CONTEXT_INFO
to set up the user context. For SSAS / Tabular / DirectQueryMode reports to work properly, I need to set contextual information at the SQL level just like any other client application. SSAS allows you to create your own SQL provider where you can set this context, however (from logs and experiments), although SSAS allows you to set some optional context (like CustomData
in the connection string) there seems to be nothing that gets passed to the base SQL data.
source to share
Are you at SSAS 2016? If so, you can do it. Check out the SQL BI documentation on DQ that came out in January. It contains explanations and instructions on how it works.
From technical document
"When you use DirectQuery, the model has one connection that has a specific configuration for impersonation (this is the imersonationMode property in the JSON file, but called Impersonation Information in SSMS and just impersonation in SSDT).
I am assuming these are the Windows users you are issuing. It requires Kerberos constrained delegation to work.
To set this in SSMS, go to your connection properties, find the Impersonation Information option and change it to Use Current User Credentials
source to share