1

My architecture is as follows:

  1. On prem sql server database [Datawarehouse]
  2. Azure Analysis Services Tabular model [Sales]
  3. International report developers that query the [Datawarehouse] to develop reports*
  4. RLS on tables in [Datawarehouse]
  5. RLS on [Sales] tabular model

For testing that our data makes it correctly from [Datawarehouse] to [Sales] I would like to create a view on the [Datawarehouse] that uses OPENROWSET() to connect to the [Sales] tabular model. The only issue with this is I can see the user that runs the OPENROWSET() query on the [Sales] model is our sql admin user, not myself.

Is there a way to configure the OPENROWSET() so that the user that is executing the local query is also the user that executes the query on the linked server? As far as I can tell, passthrough credentials would be the best way to ensure proper security is maintained.

*the only reason I mention "international" is because the RLS is based on country

Antyan
  • 158
  • 1
  • 1
  • 8

1 Answers1

1

Yes, if you want the credentials of the user who SELECTs from the View that's using OPENROWSET() to be propagated through the Linked Server, you just need to configure your Linked Server object's Security properties to "be made using the login's current security context":

Linked Server

This will respect Row-Level Security policies on the Linked Server's side.

J.D.
  • 40,776
  • 12
  • 62
  • 141