December 21, 2018 at 1:23 am
We have Always On configured and linked servers are created on each replica using listener as server name with sql authentication. There is a separate SSRS server which runs report using ApplicationIntent=ReadOnly such that it uses secondary replica. There is a Stored Procedure used in report which uses linked server for doing some insert and points to primary replica.
When we run report by commenting out linked server t-sql, report runs fine, but when report is run using linked server t-sql uncommented, we get error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".
Kerberos authentication has already been setup for domain account which run sql services on both replicas.
Can you please let me know why this error is coming?
Thanks!
December 21, 2018 at 1:56 am
This is known as the 'Double-Hop Problem' and there's a lot of information out there. It essentially boils down using Kerberos to pass the client's credentials from one server to another. The main 'fix' is to ensure that the service account is trusted for delegation. This means that it can use the client credentials on the target server. That's a drastic over-simplification because it can be quite complicated to explain and fix. There's a brief explanation here but like I said, there's a lot of info to find because it's a well known issue.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 21, 2018 at 2:16 am
Neil Burton - Friday, December 21, 2018 1:56 AMThis is known as the 'Double-Hop Problem' and there's a lot of information out there. It essentially boils down using Kerberos to pass the client's credentials from one server to another. The main 'fix' is to ensure that the service account is trusted for delegation. This means that it can use the client credentials on the target server. That's a drastic over-simplification because it can be quite complicated to explain and fix. There's a brief explanation here but like I said, there's a lot of info to find because it's a well known issue.
Thanks Neil for the response. Trusted for delegation is already there for service account but still the error.
December 21, 2018 at 7:08 am
you need
- domain account for the SQL instance
- domain account above needs "trusted for delegation"
- SPN for domain account and sql service need to be set - port number is required for TCP/IP connections
- either set manually or give the domain account permissions to read/write spn's on AD
December 26, 2018 at 6:34 am
frederico_fonseca - Friday, December 21, 2018 7:08 AMyou need
- domain account for the SQL instance
- domain account above needs "trusted for delegation"
- SPN for domain account and sql service need to be set - port number is required for TCP/IP connections
- either set manually or give the domain account permissions to read/write spn's on AD
All above has been done but still the same error. Please let me know if anything else needs to be done.
December 26, 2018 at 7:09 am
just noticed that this is being called from a SSRS report. In addition to what I said you also need SSRS to be
running using a AD account
Have Kerberos enabled
Have SPNs set for SSRS account
Connect to the SQL Server using passthrough authentication or using windows authentication if the reports run under the context of the SSRS account.
have a look at https://blogs.technet.microsoft.com/rob/2011/11/22/enabling-kerberos-authentication-for-reporting-services/ and https://redmondmag.com/Articles/2010/08/23/Reporting-Services-Double-Hop-Authentication.aspx?Page=1
With SSRS you may also have further issues if there are other IIS services running on that sever under other users. May not apply here
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply