I have an alwayson availability group setup using SQL 2014 and Windows Server 2012 R2. There are 3 nodes in the group. The SQL Service is running as a Managed Service account on each node (1 MSA for each server).
I am trying to make a linked server on each of the nodes to point to the current active node. So lets say the three servers are called Server1, Server2 and Server3, and the AlwaysOn availability group is called SQLAAG_PROD. I want to create the linked server to SQLAAG_PROD.
I am able to create a linked server to each individual node (e.g. Server1 with a link to server2 and Server3) and it works fine using the security option Be made using the login's current security context.
However when I try to create a linked server to the group listener I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON
If I change the security mode to use the SA password for the current active server then everything works, but for obvious security reasons that's not an acceptable solution.
I've read a lot about needing to setup SPN's and allow accounts for delegation. I have turned on Trust this computer for delegation to any service (Kerberos only) on the SQLAAG_PROD object in Active Directory but still have the same problem.
Has anyone seen this before, and/or is there a guide on how to set this up?
===UPDATE===
Not sure if it helps, but when I run
select auth_scheme from sys.dm_exec_connections where session_id=@@SPID
when connected directly to the node I get KERBEROS, however if I connect to SQLAAG_PROD and run the same query then I get NTLM
Best Answer
Finally found a tutorial here: http://www.derekseaman.com/2014/10/sql-2014-always-ag-pt-12-kerberos.html
The problem for me was that I needed to register the SPN for the Availability Group against the Managed Service accounts that are used to run the SQL Database Engine (step 8 in the link). Once I did that and restarted all 3 servers in the cluster everything works.