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
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_SERVERDEVELOPMENT' 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 AUTHORITYANONYMOUS LOGON’.
What is missing in this linked server setting, that I have to be on the machine itself for it to work?
This is an issue with Kerberos Authentication.
You are connecting to SQL Server on your own laptop, but that service runs under a different process & account than your SSMS session. Then, that SQL Server instance needs to connect across the linked server. That’s one hop from SSMS to SQL Server, then a second hop to the second SQL Server instance.
Doing that “second hop” will require that you are using Kerberos. By default you are using NTLM for your Windows/AD Authentication. Kerberos is more secure, and has better domain-level controls. When you make the second hop with NTLM, your SQL Server service is not able to pass your credentials along any further, and instead passes
NT AUTHORITYANONYMOUS LOGON. If Kerberos is correctly configured, you will get a different error.
There are a few possible fixes:
Configure Kerberos. This is certainly the correct answer for when you are configuring servers. You should be doing it for every server you build, but you would need to do it if you are going to be using linked servers with AD Authentication. You can query
session_idto confirm if Kerberos is correctly configured or if you are using NTLM.
Create login mappings for your linked server. This might be sufficient for your local dev machine, but when you are storing credentials in any scenario, keep in mind the security implications, and any security policies that apply for your environment/employer.