I am attempting to setup kerberos delegation on a SQL Server failover cluster in order to get around the double-hop issue but have hit a roadblock.
Environment Overview:
Four SQL Servers in two datacenters (DA01,DA02,DB01,DB02) Two SQL Server named instances with dedicated IP addresses statically bound to port 1433 (SQINS01, SQLINS02)
Each sql instance is on a failover cluster within its datacenter (SQLINS01 may live on DA01 or DA02, SQLINS02 may live on DB01 or DB02)
Both SQL server instances run as a domain service account domain\account1
SPN Overview
domain\account1 has the following SPNs in place on the domain MSSQLSvc/SQLINS01:1433 , MSSQLSvc/SQLINS01.domain.com:1433 , MSSQLSvc/SQLINS02:1433 , MSSQLSvc/SQLINS02.domain.com:1433
Delegation
domain\account1 has been configured for constrained delegation User is trusted for delegation on the SPNs listed for Kerberos Authentication
I am attempting to test this connection as follows: Connected from my local computer to SCLINS01 using my domain credentials that are allowed to be delegated.
Running the following command still returns the NT Authority\Anonymous Login failure:
SELECT TOP(1) * FROM OPENDATASOURCE ('SQLNCLI11','Server=SCLINS02.domain.com;Integrated Security=SSPI;').database1.dbo.table1
I have used the microsoft Kerberos Configuration Manager to verify that SPNs are configured properly.
select auth_scheme from sys.dm_exec_connections where session_id=@@spidreturn?KERBEROSreturned when you connect to all servers in these clusters? If one is reverting toNTLM, this may explain the behavior, but if you're seeingKERBEROSon all connections you at least have SPNs setup appropriately. That narrows the issue down to either a delegation configuration setting or theOPENDATASOURCEcommand itself (which I normally don't use to test delegation). I'm not currently in an environment where I can test any of this quickly, hence the comment, but hopefully this gives some options for further investigation.