SQL Server Unconstrained Delegation failing after 10 hours
After the Windows updates from November 2020, you might be facing some issues running Bulk Inserts or working with linked servers, if you keep an open session for more than 10 hours. Some recent changes were done on the Windows side when it comes to the way S4U (Unconstrained delegation) Kerberos tickets work.
Intermittent ANONYMOUS LOGON of SQL Server linked server double hop
If you already know how to configure the double-hop for SQL Server linked server, you may go "RARE SCENARIO" section directly. Kerberos Delegation (double-hop scenario ) Here we will use Linked server scenario as an example. The 3 servers involved is Client, SQL Server 1 and SQL server 2. The first hop is from Client to SQL server 1 and the second hop is from SQL server 1 to SQL server 2. One of the most common double hop failure scenarios we handle during connectivity troubleshooting goes like this. Scenario I defined a linked server in a SQL Server instance running in machine A (middle server SQLBI), configured to connect to SQL Server running on machine B (backend server – SQLBI2). I am using the Be made using the login’s current security context security option for the linked server. That means you are using Integrated Security. If you test linked server connectivity, opening SQL Server Management Studio in machine A, you are able to connect just fine. Error Condition: If you open SQL Server Management Studio, in another machine C (SQLDW), connect to SQL Server in machine A and try to expand the linked server or run linked server query, you get the following error messages: SQL Server 2005 and later: “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”. Troubleshooting =================================== Checklist: - Verify SQL server(s) SPN exists (for both SQL server 1 and SQL server 2) - Verify the SQL SPN has the correct format under the correct service account - Verify no duplicated SQL SPN Make sure Service Principal Name (SPN) is registered for the SQL Server instance running on： Service Account Check (using SQL Server Configuration Manager). SPN =========================== List existing SPN for SQL service account: setspn -l SQLStartupAccountName Delete SPN: setspn -d MSSQLSvc/FQDN SQLStartupAccountName setspn -d MSSQLSvc/<FQDN>:<Port> < SQLStartupAccountName > setspn -d MSSQLSvc/SQLMachineName SQLStartupAccountName setspn -d MSSQLSvc/ SQLMachineName:<Port> < SQLStartupAccountName > Add SPN: setspn -A MSSQLSvc/<FQDN> < SQLStartupAccountName > setspn -A MSSQLSvc/<FQDN>:<Port> < SQLStartupAccountName > setspn -A MSSQLSvc/SQLMachineName < SQLStartupAccountName > setspn -A MSSQLSvc/SQLMachineName:<Port> < SQLStartupAccountName > If the client uses the server name to connect SQL Server, we should have the SPN for the server name. If the client uses the FQDN name to connect SQL Server, we should have the SPN for the FQDN name. If the client uses the DNS name to connect SQL Server, we should have the SPN for the DNS name. Search all duplicated SPN – For Windows 2008 and above only Setspn -X Search SPN example: setspn -F -Q mssqlsvr/test-sql2-2* Export all the SPN in order to check for duplicates ldifde -r (serviceprincipalname=*) -f allSPN.txt List all the SPN under an account ldifde -f spn.txt -d "dc=oceania,dc=corp,dc=anz,dc=com" -r "(samaccountname=aucemawdssqlsvc)" -l serviceprincipalname Delegation ===================== - Verify below option in AD for the SQL server 1 (i.e. the server in the middle) service account: Note: If SQL Server is running under a local system account, then below step should be performed on the SQL server 1 computer object instead.: Note: The Delegation tab appears only for accounts that have an assigned SPN. Constrained Delegation (recommended, more secure) - specify the SQL server service as SQL server 2 i.e. the last server in the hop as below: Or Un-constrained Delegation (also works, but less secure) - as below: - Verify the SQL server service account is in the local policy > user rights assignment > Impersonate a client after authentication - Verify the server name in the client connection string. The server name normally is FQDN or server short name. It can NOT be IP address as IP address cannot be used for Kerberos authentication. If the server short name is used, make sure that SPN is created for server short name. Note: FQDN is recommended. - When all the items in the checklist above are passed, open a problem to involve AD team to investigate further. RARE SCENARIO: ============================ The above information could help you to resolve all persist issues if that is caused by the wrong configuration. However, if you still face some intermittent “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”, please follow this section. When delegation is set to “Trust this computer for delegation to specified services only – Use Kerberos Only” (S4U2Proxy), double hop delegation may fail intermittently. This causes middle tier to access backend anonymously as there won’t be available ticket to access, and this could be rejected by backend server. This happened because S4U2Proxy relies on the service ticket sent from client to middle tier, in order to obtain a service ticket to backend. So, after the service ticket from client expires, they will be no evidence ticket available to serve the S4U2Proxy request. As a result, middle tier will need to start a new S4U2Self to first obtain the client user’s ticket to itself, but this would be rejected by DC with the error KRB_ERR_BADOPTION. Anonymous logon would be attempted from SQL1 to SQL2 and fail. Restarting the client application may temporarily fix the issue because the ticket will be purged if the application runs in its own session, so client will send a new service ticket to middle. This is expected behavior with linked Server scenario or any other application holding a logon session alive for more than 10 hours, where our evidence ticket from user gets expired Resolution: Adjust the option from “Trust this computer for delegation to specified services only – Use Kerberos Only” to “Use any protocol”. It will allow the S4U extension in Kerberos to obtain a service ticket when the existing one expires. This procedure is known as Protocol Transition, which allows the middle tier server to obtain a service ticket to itself and accomplish the Kerberos delegation. We do recommend administrators to go with this configuration in linked Server scenario and actually middle tier will still go through Kerberos authentication protocol when attempting to obtain the ticket to itself. More Information: When an application receives Kerberos ticket buffer from the client side, it passes the buffer to LSA using AcceptSecurityContext. There is an optional parameter to receive the expiry time for application when calling this function. If the application asks for this time, the return value will be set to (or before if we have other limitations there) the service ticket’s expiration time, and application can technically associate this expiry information with the client connection. This offers the options to application to check if we need to re-authenticate the user before processing any context in that session. Refer: AcceptSecurityContext function (sspi.h) - Win32 apps | Microsoft Learn However, not all applications are going to check this expiry time. Also, in certain scenarios, the expiry may not help much: Client is trying to connect to the server multiple times, so we have the chance to send a previous request service ticket (near to expiry). Or, long running connection in pool which doesn’t reauthenticate each time. This causes possibilities that the service ticket is cached for long on middle tier LSA. Middle tier may not connect to backend immediate after it authenticate the client. This is now observed on SQL Linked Database and Web Application deployment, as those configuration may have long running tasks or connection pools that cause LSA caches the expired ticket. Internal: To verify this, get network trace and Kerberos ETL trace on the middle tier (SQL1). Netmon: We should be able to identify that server is trying to perform S4U2Self Logon and get BAD Options error from KDC, which is supposed to not happen if we configured as “Use Kerberos Only”. Kerberos ETL: this should tell that we have ticket expired therefore in the session. In S4U2Proxy delegation situation, the first expired ticket should always be the evidence ticket - the one sent from client, because other tickets are requested using it and KDC use the nearest expiry among all limitations. Additional Reference: The UI tool makes things more easy =========================== You may try the UI tool “Kerberos Configuration Manager for SQL Server” to make checking easy. https://learn.microsoft.com/en-us/troubleshoot/sql/connect/using-kerberosmngr-sqlserver https://www.microsoft.com/en-us/download/details.aspx?id=39046 this tool has the “SPN” tag and “delegation” tag to cover both. This tool could connect to local server or remote server: SPN Check, using Kerberos Configuration Manager. System Tab. Do “refresh” before all steps： SPN Tab You can simple click on “Fix All” from the KCM or If you do not run the KCM without domain admin privilege, you may get the following error: Generate the SPNs from KCM and run the script on CMD window. Verify the delegation configuration for current SQL Server starting up account. à this tool can only check the delegation configuration but could not use the configure that. Please go above section to setup the delegation.
