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
In this section, we are going to determine whether the user can connect to both SQL Servers with a regular client-server connection and Kerberos credentials. Once the individual connections are working with Kerberos credentials, we will test the delegation scenario. If any of the pieces fail, then the workflow will have appropriate actions.
0650.4.1 Can the user connect to SQL Server using Kerberos Credentials?
Do for each server in turn
Use one of the techniques in this article to determine whether you can connect to the SQL Server machine with Kerberos credentials. Make sure the client and server are on different machines. If possible, run the test from the user's computer.
Determine If I Am Connected to SQL Server using Kerberos Authentication
0650.4.2 SQL Failure Actions
0650.4.2.1 If the Auth Scheme is NTLM rather than Kerberos, troubleshoot using the 0400 Consistent Authentication Issue workflow.
0650.4.2.2 If there is a login failure, troubleshoot using the 0400 Consistent Authentication Issue workflow.
0650.4.2.3 If there is a timeout or networking issue, use the 0200 Consistent Network Issue workflow.
0650.4.3 Delegation Testing
Only perform this step if you can connect to both SQL Server machines using Kerberos credentials
0650.4.3.1 In SQL Server Management Studio, on the mid-tier SQL Server, validate a distributed query runs. It should look similar to the following:
SELECT Count(*) FROM SQLProd01.Northwind.dbo.Customers
If that fails, then the linked server itself is misconfigured and you should be troubleshooting that until it works.
0650.4.3.2 Take the test script used to see if you can connect using Kerberos. Modify it to run the distributed query instead of the auth query and test again.