Can't execute sql queries with linked servers - "Access to the remote server is denied because the current security context is no trusted."

0

Hi,

I'd like to build SQL query that uses 'current BPS database' and at the same time uses linked server that exists on that server. So I want to do sth like that:
Select *
from BPS_Content.dbo.WFElements t1
left join [my_linked_server].[database].[schema].[my_table] t2
on t1.id = t2.id

Unfortunately it doesnt work. I got an error in Webcon Studio "Access to the remote server is denied because the current security context is no trusted.".

Then I tried to execute this query directly from database.
So I wrote the following:
Execute as user = 'BPS_SQLUser';
and the same query.
It also didnt work. The same error.
When I execute this query from my user then it works.

The problem is that BPS_SQLUser doesnt have access to linked servers. I tried to give it sysadmin privileges but it didnt work.
The only solution that worked was to update GlobalParameters table and put NULL in SQLLowPrivilegeUser. Then Webcon connects to its database with 'dbo' user. This user has access to linked servers.

I'd like to ask whether is there any better solution and why BPS_SQLUser doesn't see linked servers.

Will be grateful for your help.