Search code examples
ssaspowerbi-desktopssas-tabular

SSAS: DirectQuery to SQL database using the current user


I want to define all the acces rights in my SQL Server database so it is nice and centralized. I am implementing basic stuff like grant select on schemas and tables, encryption of columns and RLS. On top of the database I build a Tabular model with SSAS with DirectQuery connection. On top of the Tabular model I want to build a report with DirectQuery connection.

directQuery states: Security can be enforced by the back-end source database by using row-level security features from the database.

Impersonation in Analysis Service Tabular states: Impersonate Current User Specifies data should be accessed from the datasource using the identity of the user who sent the request. This setting applies only to DirectQuery mode.

Issue:

I cannot choose "use current user" as impersonation mode in my ssas tabular model. -> "ssas the datasource contains an impersonationmode that is not supported for processing operations" changing the impersonation mode in ssms yields this error, VS2019 looks similar with the same content I can deploy it as a specific user but that means that everybody uses the access rights of that specified user.

  • My tabular model uses compatibility level 1400. It is deployed to a Microsoft Analysis Server 15.0.32.50, Tabular Mode. (The model cannot use DirectQuery when in compatibility 1500 for some arcane reason. Please don't make this your topic unless you absolutely have to.)
  • SQL Server Version is 2019, 15.0.2000.5
  • The on-premise Report Server must be used.
  • SSAS, database and report server run on the same SQL Server.

Is it possible to implement this solution using database, ssas and report server on the same machine? If so, how?


Solution

  • Alrighty, so after messing around with this stuff for wayyy too long, I narrowed it down to the SQL-Server setup. Something there in the configuration is causing a bunch of issues.

    Using direct query to pass down user information in the way described above is perfectly valid.