You may experience connectivity issues if SQL Server service account delegation settings are misconfigured

Applies to: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

Symptoms


Consider the following scenario:
  • You have an application that connects to SQL Server instance InstanceA using Windows authentication.
  • The application then tries to execute a distributed query that involves getting data from a remote SQL Server instance InstanceB
  • InstanceB is configured as a linked server in InstanceA and in the Security Page under the properties of linked server definiton, you chose: Impersonate option.
In this scenario, you may receive the following error message:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' 

Cause


The error occurs when the SQL Server service account on the source instance (InstanceA in the above example) is not properly configured to delegate or forward the Windows credentials to the remote SQL Server instance (Instance B in the above example).

Resolution


Configure the SQL Server service startup account on the source server (Instance A) to permit Full Delegation or Constrained Delegation. You can configure these settings on the Delegation tab under Account properties in the Active Directory User and Computers console. If you use constrained delegation, make sure you add the Service Prinicpal Names of the target servers (InstanceB in our example above) under the list of allowed services.

More Information


The specific steps with screen shots of the two different Delegation settings [Full Delegation and Constrained Delegation] are discussed in detail in the following blog post on MSDN: My Kerberos Checklist…

Additional requirements for delegation to work properly:
Apart from the Active Directory settings for the Windows service startup account of the SQL Server that is forwarding the credentials, there are a couple of other settings you need to configure to ensure that delegation works properly. They are discussed below:
  1. The Windows Account that the client application is using to connect to the first instance of SQL Server (InstanceA) should not have the following property selected: Account is sensitive and cannot be delegated. This is again configured in the Active Directory Users and Computers console. For more information on this, refer to

  2. The SQL Server service account of the first SQL Server instance (InstanceA) should have the "Impersonate a client after authentication" user right. This should be configured from the Local Security Policy editor. More information is available in the "Local Policy Settings" section of  My Kerberos Checklist… blog post.
  3. Kerberos authentication protocol settings for both SQL Server instances must be configured properly. The important component of this is to have correct Service Principal Name for the SQL Server instances. For more information on configuration the Service Principal Name, refer to the following

Additional scenarios to consider:
You can experience this problem with any scenario that needs to forward Windows credentials from one server to another server. Apart from the linked server example provided in the symptoms section, you can encounter the same error for the following situations:
  1. SQL Server Reporting Services attempting to forward the windows credentials to SQL Server Database Engine
  2. ASP.NET Web services attempting to forward the windows credentials to SQL Server Database Engine

This is not an complete list. Any setup you have where there is a server that is interacting with a front end application and also needs to forward the windows credentials to another service can run into the same issues mentioned in this article. In all of these scenarios, you need to ensure the delegation settings are configured properly for the startup account of the service that is forwarding the windows credentials. In the example discussed in this article, it would be the service startup account of the Server Application (SQL Server InstanceA).