Skip to content

0650 SQL Server Linked Server Delegation Issues

Jump to bottom
Malcolm Stewart edited this page Dec 29, 2020 · 5 revisions

0650 SQL Server Linked Server Delegation Issues

0650.1 Is this the correct workflow?

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?

0650.2 Moving Parts

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.

Linked Server Delegation

0650.3 Pre-Work

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

0650.4 Isolation Technique

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.

0650.4.4 Delegation Failure Actions

This section is for when the script can connect to both servers individually and either SSMS fails to delegate from the mid-tier machine or SSMS works and the script fails to delegate when run on the client machine.

0650.4.4.1 SSMS delegation fails. Check connecting from SSMS on the mid-tier server to the back-end server. If this fails, use the 0400 Consistent Authentication Issue workflow.

0650.4.4.2. SSMS delegation fails. Check connecting from SSMS on the mid-tier server to the back-end server. If this works:

  • Check the linked server properties and make sure they are pointing to the correct back-end server.
  • Script the Linked Server to a new Query Window and then delete and recreate. Test again. Sometimes, the Linked server can cache bad information.
  • Check that the problem affects other users. If not, is the user account marked sensitive? If it affects all users, is the server account trusted for delegation?
  • Check the Linked Server Security settings and make sure there are no Login Mappings that could be breaking things.

Linked Server Properties

The default setting is for all users to delegate credentials. As long as the SPNs on both SQL Servers are correct, and the service account for the mid-tier SQL Server is trusted for delegation, this should work.

  • If Not Be Made is selected, then the user accounts will not delegate.
  • If Be Made Without Using a Security Context is selected, then the connection will made to the back-end server with anonymous credentials and fail.
  • If Be Made With This Security Context is selected, then the SQL Login credentials below must be specified. A SQL login will be made, as opposed to a Windows Integrated login. If the client is connecting with a SQL Login then this last option must be specified either here or in the Mappings section above.

Note, these global settings at the bottom only take effect if the Login name does not appear in the top section. If it appears in the top section, then Impersonate can be checked, or if unchecked, a SQL Login must be specified.

  • Force the Linked Server connection to use TCP/IP instead of Named Pipes. If making the connection from the mid-tier server to the back-end server via Named Pipes, then the user credentials must be validated on Windows of the back-end server before they are validated with SQL Server. The user account may not belong to the Users group on Windows and may fail. To avoid this, force TCP for the connection. You can do this by entering tcp: in front of the server name when creating the linked server, or by using an Alias using SQL Server Configuration manager. Create a 64-bit Alias if SQL Server is 64-bit (usual), 32-bit otherwise (rare).

0650.4.4.3 Check whether the mid-tier SQL Server is using constrained delegation. Make sure the back-end SQL Server SPN is specified as a targe for delegation.

0650.4.4.4 Check whether the client is running Windows 10 Enterprise. Is Credential Guard enabled. This will require constrained delegation. Full delegation cannot be used.

As per documentation (https://docs.microsoft.com/en-us/windows/access-protection/credential-guard/credential-guard-requirements), applications will break if they require:

  • Kerberos DES encryption support
  • Kerberos unconstrained delegation
  • Extracting the Kerberos TGT
  • NTLMv1
Clone this wiki locally