IIS SQL Linked Server Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Question

I have an IIS 7.0 server connecting to an SQL 2012 Database. On the SQL database I have added a number of linked servers and created a distributed partitioned view to consolidate data from the multiple linked servers. I have a common Windows account on the main server and the linked servers that is common to all (same password). Querying this view from SSMS on the SQL server works fine logged in as the common user. The application pool I am using uses this same user (also on the IIS server) to connect to the SQL server using Integrated Security. I have a simple query of SELECT SYSTEM_USER when run from IIS reports the common user. When trying to retrieve the data from the linked server view I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. instead of the data in the view.

Help appreciated

Answer 1

This is basically what is happening: If you are running Active Directory, this is the famous "double-hop" problem. Double-hop is a term used to describe Microsoft's method of maintaining Kerberos authentication credentials over two or more connections. You must configure Kerberos delegation on the AD account running the application pool so that it can forward credentials to the SQL 2012 database server. You configure this on the Delegation tab of the AD account. Without Kerberos delegation, the AD credentials are not forwarded and instead local credentials are sent by the IIS server and get denied by the SQL server. Since the SQL Server doesn't understand the local credentials of the IIS 7 server it denies it as an anonymous logon attempt. The link I provided describes the resolution to this problem in full detail and explains how to expand it out to other servers. Note that if you are not using Active Directory and only local authentication then NTLM is being used and NTLM credentials cannot be delegated off the system so authentication to the SQL server will be in the form of anonymous authentication.