I have a few queries that I use to monitor log shipping. Sometimes I want to run those queries on a different server. I usually do that through a linked server.
I have a server called SQLDEV4-TS that has a linked server to MY_SERVER\DEVELOPMENT
ON SQLDEV4-TS I have setup Kerberos as per the picture below:
I have this query below that I use to test the connectivity to the linked server.
DECLARE @server_name sysname = @@servername SELECT @server_name=N'MY_SERVER\DEVELOPMENT' DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM OPENQUERY([' + @server_name +'], ''SELECT ''''Radhe'''' AS Radhe'');' BEGIN TRY EXEC sp_testlinkedserver @server_name EXEC sp_executesql @sql=@sql END TRY BEGIN CATCH SELECT [error_number]=ERROR_NUMBER(), [error_message]=ERROR_MESSAGE(); END CATCH; PRINT 'We got past the Catch block!'; When I am on the server itself – SQLDEV4-TS – and I run the above script I get the following result:
All good – it works as expected – no problem.
However, when I connect to SQLDEV4-TS from my local machine, and run the script (on my machine) I get the following result:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
What is missing in this linked server setting, that I have to be on the machine itself for it to work?
