October 27, 2008 at 1:28 pm
Hi guys,
I have different environment with the same replication setup. I am trying to create a stored procedure to check the replicated tables of the publisher matches with subscriber( row count too). This stored procedure is created in a seperate server. We use only windows authentication. I have created the linked server ( previously created) for all the publisher , distributor and subscriber.Now when executing this stored procedure i get the msg at in my subject
Login Failed for user NT Authority\Anonymous Logon
what might be the problem? How can I resolve this issue?
Thanks
October 28, 2008 at 7:57 am
srawant (10/27/2008)
Hi guys,I have different environment with the same replication setup. I am trying to create a stored procedure to check the replicated tables of the publisher matches with subscriber( row count too). This stored procedure is created in a seperate server. We use only windows authentication. I have created the linked server ( previously created) for all the publisher , distributor and subscriber.Now when executing this stored procedure i get the msg at in my subject
Login Failed for user NT Authority\Anonymous Logon
what might be the problem? How can I resolve this issue?
Thanks
Validate the SPN's (Service Principal Names) for both instances and enable delegation. These articles should point you in the right direction 🙂
http://technet.microsoft.com/en-us/library/bb742516.aspx
http://support.microsoft.com/kb/319723
Tommy
Follow @sqlscribeOctober 28, 2008 at 8:03 am
When using windows authentication and querying linked servers over 3 or more "tiers" - you need to enable a setting in AD called something like "trust SQL Server for delegation" or something like that. (can't remember)
ie.
you are working on your laptop and are connected to SERVERA via SSMS. You execute QueryA and
in turn Query A is Querying ServerB via a linked server
Your windows authentication does not delegate down to ServerB so SQL do not know if you have a valid login or not - and you get the error
to test this: connect remotely to ServerA via Windows Remote desktop then execute the same query. Now it will work...
I only know of the AD setting, but maybe someone else knows of a another solution?
October 28, 2008 at 8:04 am
Aahhh, and Tommy just gave another solution 🙂
October 28, 2008 at 8:42 am
Exactly TVR.. Its a double hop issue.. Thanks for the reply..
December 11, 2008 at 1:33 am
For anyone else coming across this issue, here's a brilliant article explaining this...
http://www.sqlservercentral.com/articles/Security/65169/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply