CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-databaselinked-server
xUrko
xUrko

Reputation: 335

Querying data over linked server from SQL Azure RLS database

I want to get data via a linked server from SQL Azure which is using Row-Level Security. The problem appears that the query does not return any data.

If I execute the same query directly on SQL Azure, I get the data.

{SQL query for linked server} - Not working

EXEC SP_SET_SESSION_CONTEXT @key=N'TenantId', @value='XXX'
SELECT * FROM [AHPRO].[AllHoursProduction].[dbo].[User]

{SQL query for SQL Azure} - Working

EXEC SP_SET_SESSION_CONTEXT @key=N'TenantId' ,@value='XXX'
select * from [AllHoursProduction].[dbo].[User]

Upvotes: 2

Views: 145

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89256

When using Linked Server you have a local Session and a remote Session. You're setting a property in your Local Session and it has no effect on your remote session.

You could do something like 

exec('
EXEC SP_SET_SESSION_CONTEXT @key=N''TenantId'' ,@value=''XXX''
select * from [AllHoursProduction].[dbo].[User]
') at AHPRO

to run the whole batch at the linked server.

Upvotes: 1

Related Questions