Linked Servers using Windows Authentication

RayMilhon 

Yes, this behaviour is normal and commonly encountered in three tier (or more) architectures. What you'd be looking to configure is something called Kerberos Constrained Delegation (or KCD for short.)

KCD is a Windows concept (a bit simplified there, but let's stick to the Microsoft context), not an SQL concept, and you'll find it's frequently mentioned in the IIS/web server context. That said, because it's a Windows concept, the documentation on how to configure it is the same for SQL Server.

Here's a short version of what's different between your SMSS test scenario and your production scenario.

SMSS

SMSS only involves a single hop for authentication as your Kerberos ticket is created on the SQL host itself. Single hops just work out of the box, which is why your linked server definition works when using SMSS, since it's only then the single hop from the SQL host to the remote SQL host linked via the linked server definition.

Production scenario

Your production scenario is different, since it involves two hops:

1. From the application host to your central SQL Server;
2. From the central SQL Server to the remote SQL Server server(s) at the other end of the linked server definitions.

And this is where KCD comes in, and without it - and it's "off" by default - your Kerberos ticket created on the application host only makes it as far as the central SQL Server host (i.e. point 1.) Because the central host is not permitted to pass that ticket to the remote SQL host, you run into the "anonymous" authentication issue you've mentioned.

By enabling KCD (involves configuration of both the SQL Server database service account as well as the account being used by the application host to log into the central SQL Server), you're providing "approval" for the central SQL Server host to take the application host's Kerberos ticket and forward it along to the remote SQL Server host, thereby completing the second hop and resolving your "anonymous" authentication issue.

Here's some additional reading that covers the concepts and action items.

• Understanding Kerberos Double Hop - Microsoft Tech Community
• How to configure Kerberos Constrained Delegation for Web Enrollment proxy pages - Windows Server | Microsoft Docs (IIS-based but relevant; just remember to use the MSSQLSvc-based servicePrincpalName values);
• Register a Service Principal Name for Kerberos Connections - SQL Server | Microsoft Docs

In short though, you want to:

1. Enable KCD on the service account for the SQL Server database service on the central SQL Server (not the remote hosts from the other end of the linked server definitions - nothing should be done for these). Make sure you enable protocol transitioning by selecting the "Use nay authentication method" option (as shown in the IIS article from above in "scenario 1, step 2");
2. Configure the account used by the application host to be allowed to delegate to the various "MSSQLSvc/" servicePrincipalName values (as seen in the IIS article, scenario 2, section 1: configure the delegation".)

Be aware that if the central SQL Server and/or the application host are using a credential such as "Network Service", "LocalSystem", "SYSTEM", etc. then these built-in references resolve back to the actual computer account within Active Directory.

For example, if my central SQL Server is named SQL01 and my application host is named APP01, and they both operate under the "Network Service" security principal, then for step 1 above, I'm editing the computer account for SQL01 in Active Directory, and for step 2, I'm editing the APP01 computer account from Active Directory.

Cheers,

Lain