Can a server outside the Windows domain connect to a 2nd SQL instance via a linked server?

Question

I have the infrastructure shown in the diagram below.

Web Server 1 is outside the Windows Domain. Web Server 2 is inside the Windows Domain.

SQL Server 1 and 2 are both inside the domain.

There is a linked server connection from SQL Server 1 to SQL Server 2.

Both SQL Servers only accept Windows Authentication, not SQL authentication.

Both SQL Servers have a Kerberos SPN setup with unconstrained delegation.

A domain user on Web Server 2 has a SQL login configured on both SQL Server 1 and SQL Server 2 and can query data on SQL Server 1 and on SQL Server 2 via the linked server.

A local user on Web Server 1 has a local login of the same name and password configured on both SQL Server 1 and SQL Server 2 and can query data on SQL Server 1, but not on SQL Server 2 via the linked server. The following error is given in the SQL Server log:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided.

The question: can Web Server 1, outside the Windows Domain, be made to successfully query data from SQL Server 2 via the Linked Server connection on SQL Server 1? If so, how?

Answer 1

This is a classic double-hop problem, but in this case, you are using NTLM rather than Kerberos (as it's outside the domain). So it's impossible to do double-hop, as NTLM doesn't support it.

Options are:

• Join Web1 to the domain and set up Kerberos delegation
• Use an SQL Authentication login instead. You only need to do that for this user over the linked server connection, by using a mapping, although unfortunately I don't think you can specify where that user connects from.