0650 SQL Server Linked Server Delegation Issues
Does the issue only affect database connections, or does it affect web and file share connections, too?
0650.1.1 Typical Error Messages
- Cannot generate SSPI Context
- Login failed for user '(null)'
- Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
0650.1.1 Is there a better workflow?
- If the issue is intermittent and not consistent, use 0300 Intermittent or Periodic Network Issue, including connection timeouts.
- Does the issue happen with a simple client-server connection? If yes, use 0400 Consistent Authentication Issue.
- Is the error a connection timeout or other network failure? If yes, use one of the networking workflows.
The initial goal is to try to isolate whether either or both the SQL Servers is not accepting Kerberos credentials. If there are problems after that, we have additional troubleshooting steps.
Here is the conceptual diagram of Kerberos delegation.
0650.3.1 Please perform the initial data collection and narrowing steps:
0100 Initial Data Collection and Scoping Questions
This will help get a macro perspective of the scope of an issue, such as whether the issue affects multiple computers or just one, or whether only those computers in a specific data center are facing issues. This can help focus the troubleshooting steps. It will also make you prepared for discussing the issue with Microsoft Support should you choose to do so.
0650.3.2 Make sure you understand the application architecture. Make a summary in a succinct form, similar to the below description:
- There are two domains involved: CONTOSO and FABRIKAM.
- The client (SPARKY.CONTOSO.COM) is Windows 2012.
- The user (CONTOSO\JOHNDOE) runs a desktop application and connects to a SQL Server server (SQLSTAGE01.CONTOSO.COM) using Integrated security.
- The SQL Server service account for SQLSTAGE01 is CONTOSO\SQL_STAGE01_SVC.
- The application executes a linked server distributed query that connects to another SQL Server 2014 (SQLProd01.FABRIKAM.COM\Accounting on port 1433) using the MSOLEDBSQL OLE DB Provider and delegates the user credentials to the SQL Server via integrated security.
- The SQL Server service account for SQLPROD01 is FABRIKAM\SQL_SVC_01.
0650.3.3 Collect the SPN information based on the service accounts identified in the description. e.g.
SETSPN -L CONTOSO\SQL_STAGE01_SVC > c:\temp\spns.txt > creates a new file
SETSPN -L FABRIKAM\SQL_SVC_01 >> c:\temp\spns.txt >> appends to a file