1

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.

  • Are you sure you're connecting to the SQL instance via Kerberos Authentication and not NTLM? What does select auth_scheme from sys.dm_exec_connections where session_id=@@spid return? – John Eisbrener Oct 24 '17 at 18:37
  • Returns "KERBEROS" – vesuvious Oct 24 '17 at 18:42
  • Sorry, I should have been more explicit; is KERBEROS returned when you connect to all servers in these clusters? If one is reverting to NTLM, this may explain the behavior, but if you're seeing KERBEROS on all connections you at least have SPNs setup appropriately. That narrows the issue down to either a delegation configuration setting or the OPENDATASOURCE command 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. – John Eisbrener Oct 24 '17 at 18:49
  • Interesting it looks like it is working with LINKED SERVERS but not with OPENDATASOURCE – vesuvious Oct 24 '17 at 18:53
  • Really strange, I think it may be an issue with SSMS. If I open a new query and run the OPENDATASOURCE command, then replace it with the linked server command it fails. If I open a new query window and go right to the linked server command it is successful. – vesuvious Oct 24 '17 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.