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?
