SSRS query on remote linked server

Question

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

Have you by any chance looked into a dynamic connection string? — Diego, Oct 16 '19 at 18:28
@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, Oct 16 '19 at 18:32
@WolfgangKais , windows authentication. I updated the post with a log error, if that helps — Eric Klaus, Oct 16 '19 at 18:50
Yes, thanks. Is using fix credentials in the shared datasource an option? — Wolfgang Kais, Oct 16 '19 at 18:52
@WolfgangKais, As long as it works, what should I do for that? — Eric Klaus, Oct 16 '19 at 19:02
Edit the shared datasource in the web portal and enter specific Windows Credentials to connect to the server. — Wolfgang Kais, Oct 16 '19 at 19:06

score 1 · Accepted Answer · answered Oct 16 '19 at 19:05

1

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).

answered Oct 16 '19 at 19:05

Wolfgang Kais

4,010
2
10
17

I created a service account. Where is web portal? Is that in Reporting Services configuration manager Web Portal URL? – Eric Klaus Oct 16 '19 at 19:12
Yes, that is the URL. You will have to enable delegation for the service account, not for the computer. Good luck! :-) – Wolfgang Kais Oct 16 '19 at 19:20

SSRS query on remote linked server

1 Answers1