We were recently facing an issue where several SSIS packages failed to execute against a newly created SQL FCI Cluster with DAG and several listeners created, with the following error being received as an output.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xxx.x.xxx] SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: xxx.xxx.xxx.xxx]
The above error was in relation to the below SQL server error:
Executed as user: xxx. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6560.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:00:00 πμ Error: 2020-10-06 11:00:00.86 Code: 0xC0202009 Source: xxxxx Connection manager "InstanceName.Database" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Client unable to establish connection". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". End Error Error: 2020-10-06 11:00:00.87 Code: 0xC00291EC Source: Find Missing PropSteg Execute SQL Task Description: Failed to acquire connection "InstanceName.Database". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 πμ Finished: 11:00:00 πμ Elapsed: 0.453 seconds. The package execution failed. The step failed.
A distinguished fellow senior engineer, indicated that this was a problem with Kerberos and SPNs. To actually verify this, we deployed Kerberos Configuration Manager for SQL, a tool provided by Microsoft, that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services.
After getting the results, KCM verified that 2 required SPNs were missing, and even provided the exact script to create them.
After that, all packages are executed flawlessly.