December 2, 2020 at 6:26 pm
I have 3 (old) existing SSRS reports that were developed and deployed on Server A. They each use the same 2 shared Data Sources (RD and DR). 1 of the Data Sources(DR) is a linked server, Server B. The reports have queries against the same datasets. 2 of the datasets use the shared Data Source, DR. These 2 datasets query a view that is built on Server B from a database/tables on another linked server (Server C). This is the double hop I refer to. From Server A to Server B to Server C.
Server C was changed (from C to C2) and since then, the reports have failed when executed via SQL Server Agent and the queries fail when executed from within Visual Studio with a Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error.
I can execute the queries from Server B in SSMS.
I will note here that we do not have a DBA, I am the SQL programmer and I have a colleague that is responsible for infrastructure and all things related to credentials and security. He has examined and compared both Server C and Server C2 and the same logins/users/etc appear to be set up in both places.
Server A has Sql Server 2008r2, Server B has SQL Server 2016.
What should I/we look at to resolve the issue. TIA
December 3, 2020 at 10:17 am
You need to ensure all the kerberos pieces have been put back in place for C2. Ensure your using a domain account for the SQL service. Ensure the SPN's for the account and MSSQLServer are all registered. Ensure the account is trusted for delegation. Etc.
December 3, 2020 at 2:45 pm
As it turns out, the old linked server had 'Be made using this security context' set within Properties>Security and the new one did not. That has been addressed and now everything is working as it should.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply