SSRS query on remote linked server

0
votes

I have configured SSRS on Server1. Server2 is linked to Server1 in sys.servers.

All queries/reports link runs fine if I run Server1 objects

I want to run report on a Server2 objects

Try 1 I created a Shared Data Source to Server2, built query in a Shared Dataset.

SELECT Field1 FROM Table1

a) Preview - Runs fine

b) Server1/Reports throws this error:

An error has occurred during report processing. (rsProcessingAborted) The execution failed for the shared data set 'table1'. (rsDataSetExecutionError) Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors

Try 2 I tried to use Server1, and link from there. So when creating a Dataset I used Server1 as a source and built this query 

SELECT Field1 FROM Server2.master.dbo.Table1

a) preview runs fine

b) Server1/Reports throws same error as above

NOTE: changing above query to OPENQUERY yields same thing

If I look at logs it says:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

it can connect to Server1 datasource (since SSRS is installed on Server1), but can't connect to Server2. How can I grant that user access to Server2?

How I can fix above issue, so that report runs fine from Server1/Reports too

NOTE: I use SQL Server 2017

1
reporting-servicesssrs-2017
Have you by any chance looked into a dynamic connection string? - Diego
@Diego , I didn't . But shouldn't it work without that if it works in preview? And I don't need to run one report on a different servers. I have just 1 report which needs to be run from Server2 - Eric Klaus
What kind of authentication is used? - Wolfgang Kais
@WolfgangKais , windows authentication. I updated the post with a log error, if that helps - Eric Klaus
Yes, thanks. Is using fix credentials in the shared datasource an option? - Wolfgang Kais

1 Answers

1
votes

You are using Windows Authentication for the Report Server and again in the shared datasource. That means that the logged-in user token is passed to a second hop, Server2. To allow this, you will have to enable Active Directory Delegation.

Contact your network administrators and ask them to configure (at least constrained) delegation on Server1 to allow delegated connections to Server2 (at least to the SQL Server Service).