August 8, 2018 at 12:12 pm
I have two server (Source and Dest). When I run the query from Source, I get an error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"
SELECT TOP (50) cif.ContactKey as ID, cif.FirstName, cif.LastName, C.*
FROM [dbo].[Contactdata] C
LEFT OUTER JOIN [Dest].[Farm].dm.ContactInfoFact cif on C.CompanyNids = Cif.CompanyNids
But when I run the query from dest, then it executes just fine.
SELECT TOP (50) cif.ContactKey as ID, cif.FirstName, cif.LastName, C.*
FROM dm.ContactInfoFact cif
LEFT OUTER JOIN [source].[DB].[dbo].[Contactdata] C on Cif.CompanyNids = C.CompanyNids
Any help is highly appreciated.
August 8, 2018 at 12:53 pm
NewBornDBA2017 - Wednesday, August 8, 2018 12:12 PMI have two server (Source and Dest). When I run the query from Source, I get an error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'"
SELECT TOP (50) cif.ContactKey as ID, cif.FirstName, cif.LastName, C.*
FROM [dbo].[Contactdata] C
LEFT OUTER JOIN [Dest].[Farm].dm.ContactInfoFact cif on C.CompanyNids = Cif.CompanyNids
But when I run the query from dest, then it executes just fine.
SELECT TOP (50) cif.ContactKey as ID, cif.FirstName, cif.LastName, C.*
FROM dm.ContactInfoFact cif
LEFT OUTER JOIN [source].[DB].[dbo].[Contactdata] C on Cif.CompanyNids = C.CompanyNids
Any help is highly appreciated.
Did you check for spn/delegation configurations on both servers? You may want to try running the Kerberos configuration utility on both servers - it's an easy way to check things.
Microsoft Kerberos Configuration Manager for SQL Server
Sue
August 8, 2018 at 2:23 pm
So I ran this query and I m looking at some connection using NTLM (for local machine) , some Kerberos and some SQL.select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections
I also ran to check if SPN is registered by bu using CMD
setspn -L <domain\account> and it's throwing an error when I run it. I used ( ), <> even without anything around the domain name but error. Complaining about the syntax.
August 8, 2018 at 3:04 pm
NewBornDBA2017 - Wednesday, August 8, 2018 2:23 PMSo I ran this query and I m looking at some connection using NTLM (for local machine) , some Kerberos and some SQL.select session_id,net_transport,client_net_address,auth_scheme from sys.dm_exec_connections
I also ran to check if SPN is registered by bu using CMDsetspn -L <domain\account>
and it's throwing an error when I run it. I used ( ), <> even without anything around the domain name but error. Complaining about the syntax.
setspn -L Domain\Account does work. Typed just like that.
I can only think of reasons for the error of could not find account. Double check whatever you are typing at the command line if the errors are about the syntax.
Sue
August 9, 2018 at 8:48 am
I must have been doing something wrong but I was able to run setspn command successfully. So now I am able to access the linked server without getting any error, however; other user is still getting the same error I was getting. What am I missing?
setspn -L domain account which SQL server service is running under
August 9, 2018 at 9:02 am
NewBornDBA2017 - Thursday, August 9, 2018 8:48 AMI must have been doing something wrong but I was able to run setspn command successfully. So now I am able to access the linked server without getting any error, however; other user is still getting the same error I was getting. What am I missing?setspn -L domain account which SQL server service is running under
Is it the same error and does it work for you on both servers now? And how is the security setup for the linked servers?
There is also a Kerberos checklist that you may want to go through:
My Kerberos Checklist
And this is older, still applies, but is specific to linked server so that might help as well:
How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply