Checklist for Double Hop issues {IIS and SQL Server}

Checklist for Double Hop issues and the tasks to be done to create the right set of Delegations for this Problem

NOTE: We have to set the IIS Server and SQL Server both Trusted for Delegation and the details are right below

1.   Check for DNS Resolution. Of course J but you need to make sure that SRV Records ldp and Kerberos are registered

2. Verify that is using TCP

If you find issues with UDP fragmentation follow.

How to force Kerberos to use TCP instead of UDP in Windows Server 2003, in Windows XP, and in Windows 2000

https://support.microsoft.com/?id=244474

3. Make sure that “Account is sensitive and cannot be delegated” is unchecked

This option “Allows control over a user account, such as for a guest or temporary account. This option can be used if this account cannot be assigned for delegation by another account.” Customer’s do it for some user accounts, required for some of their legacy applications which does not support Kerberos.

4. Check if it is constrained delegation (windows 2003 domain functional level required) or unconstrained delegation.

Middle tier server configured to delegate to all the services or it is selected for specified services only (constrained delegation)

è Go to properties of machine account of middle tier server in active directory users and computers and delegation tab.

è  To configure the account to use constrained delegation without protocol transition, select Use Kerberos Only.

è  If the service runs under a domain user account, then you will need to configure the service account for delegation

Note: If running on the default port like 80 for http, leave the port field empty otherwise specify it exclusively.

5. Add any accounts that will need to delegate credentials (such as the IIS account) to Impersonate a client after authentication and Act as part of the operating system under User Rights Assignment.( middle tier service should be configured for impersonation)

6. Verify SPNs for Back-end Service Accounts

To List the SPN’s use setspn –L Account

è Verify that the server which is in middle tier (receiving user credentials and passing them to third tier) has the SPN for the service it is running registered.

Note: Verify that these two SPNs for the service account are present:

•             One for ServiceClass/Host:Port

•             One for ServiceClass/FQDN

è Verify that the server which is the backend (the ultimate destination for which the middle one is delegated) has the SPN for the service it is running registered) {This is for  SQL Server see below for the SQL Server account SPN creation)

SQL SERVER RELATED SETTINGS FOR THE DELEGATION ISSUE

7. Verify that the user is authorized to access the back-end server’s resources.

8. All communication for the delegation scenario from user to middle tier to back end server is using TCP/IP.

9. If using IIS as middle tier server verify that the client operating system connecting to it has the Integrated Windows Authentication option selected.

SQL SERVER RELATED SETTINGS FOR DELEGATION

1) If you do not select the Enable Kerberos Authentication option on the Network Name resource and a computer object does not exist in Active Directory, refer to the following Microsoft Knowledge Base article for information about how to troubleshoot the Network Name resource:

257903 Cluster network name may not come online with event ID 1052

https://support.microsoft.com/?id=257903

2) Once the above is done, go ahead and check in AD whether the Virtual SQL Server Name is showing up {Set the Computer Object  “Trusted for Delegation”}

3) Set the Right set of SPN’s which only a Domain Admin Should do

Create an SPN for SQL Server

Caution:  

SQL Server only uses Kerberos if the client uses the TCP/IP protocol to connect to SQL Server. For example, if a client uses the Named Pipes protocol, Kerberos is not used. If you have multiple instances of SQL Server on a computer, you must configure a Server Principal Name (SPN) for each instance of SQL Server because each instance of SQL Server uses a unique TCP-IP port.

Important:  

If the SQL Server service is running under the LocalSystem account, you do not have to manually configure an SPN for SQL Server. The SPN is created automatically when the SQL Server service starts. If the SQL Server service runs under a domain user account, you must manually configure an SPN. To do so, follow these steps.

To configure an SPN for SQL Server, use the SETSPN utility in the Microsoft Windows Resource Kit. To download the SETSPN utility, visit the following Microsoft Web site.

Before you run SETSPN, consider the following information:

è You must run setspn.exe under a logon account with permissions to register the SPN.

è Note the domain user account that the instance of SQL Server is running under. In the following examples, this account is named <SQL_Service_Account>.

è Important:  If the instance of SQL Server is running under the LocalSystem account, you do not have to run the SETSPN utility.

è You must have the fully qualified domain name (FQDN) of the computer that is running SQL Server. To determine the FQDN of the computer that is running SQL Server, use the ping utility.

è Ping the virtual SQL Server name to obtain the IP address, and then run ping -a to make sure that the FQDN is correctly returned by the DNS.

Note:

If you are using SQL Server failover clustering, you use the FQDN for the virtual SQL Server. Note the exact TCP/IP port that the instance of SQL Server uses. To determine this information, open SQL Server Configuration Manager on the computer that is running SQL Server, click the instance of SQL Server, and then view the properties for the TCP/IP protocol (default port).

After you determine the domain user account that the SQL Server service is running under, the FQDN of the computer that is running SQL Server, and the TCP/IP port that the instance of SQL Server is using, follow these steps to create the SPN for SQL Server.

Note:  

You must be a member of the Domain Administrators group to run the SETSPN command.

1) If you are using SQL Server failover clustering, run the following SETSPN command:

setspn -A MSSQLSvc/<FQDN> <SQL_Service_Account>

For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC, where MySQLServer.MyDomain.com is the name of the instance of SQL Server 2005 that is clustered, run the following command:

setspn -A MSSQLSvc/MySQLServer.MyDomain.com SQLSVC

2) For both clustered and non-clustered computers that are running SQL Server, run the following SETSPN command to register an SPN for the port that the computer that is running SQL Server is using:

setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>

For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC on port 1433, run the following SETSPN command:

setspn -A MSSQLSvc/MySQLServer.MyDomain.com:1433 SQLSVC

After the SPN is registered, verify that it is correctly registered by using the LIST feature (-L switch) of the SETSPN utility. Run SETSPN -L <SQL_Service_Account> to list all the SPNs that are registered to the domain user account that the instance of SQL Server is running under:

setspn -L <SQL_Service_Account>

For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC on port 1433, run the following command:

setspn -A SQLSVC

The SPN that you created in step 1 (if SQL Server is clustered) and step 2 (if SQL Server is not clustered) is shown in the following output:

C:\>setspn -l SQLSVC

Registered ServicePrincipalNames for CN=SQLSVC,CN=Users,DC=MyDomain,DC=com:

    MSSQLSvc/MySQLServer.MyDomain.com

    MSSQLSvc/MySQLServer.MyDomain.com:1433

Note:  

If you are using SQL Server failover clustering, you must register a SPN without the port number and another SPN with the port number. With a typical, non-clustered computer that is running SQL Server, you only have to register the SPN with the port number. However, if you have an additional SPN without the port number, it will not cause any problems with non-clustered computers.

Delegation Is Not Supported an Example:

SQL Server 2005 Integration Services (SSIS) does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot pass your credentials on from the second computer to the third computer on which SQL Server is running.

https://msdn.microsoft.com/en-us/library/aa337083(SQL.90).aspx

Note: 

If a middle tier service is using NTLM to authenticate to the next service, you typically receive errors such as:

Logon failed for null user

Logon failed for NT AUTHORITY\ANONYMOUS user

If you have verified that each computer uses the Kerberos protocol, a likely cause of the authentication problem is that the middle tier services are not properly configured for delegation.  Follow the steps mentioned above.