Double hop problem with sql server 2008

Question

I'm trying to set up the following scenario. I have 3 servers, they are all in same domain.

• Server A has web application which runs under (domain) service account, dom\web.
• Server B has instance of SQL Server 2008 R2 which runs under (domain) service account, dom\sql.
• Server C has instance of SQL Server 2008 R2 which runs under (domain) service account, dom\sql.

Servers B and C are in SQL Cluster. Instances of B and C are linked servers.

When I run SQL Server Management Studio from A and connect to B using my domain account (dom\usr) I try to execute the query which selects data from B and C and it works.

When I try web application which tries the same I get the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I see that connection in SQL has auth_scheme KERBEROS for user dom\web so it's not NTLM.

Also, dom\web domain account doesn't have selected "account is sensitive and cannot be delegated" option in AD.

I also think that SPN is correctly set up because double hop wouldn't work in the first case.

This is error on server C:

Source Logon

Message Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.65.10.53]

Source Logon

Message Error: 18456, Severity: 14, State: 11.

Solution 1

I believe IIS has the website set for "Anonymous" with the IUSR account. This goes across the domain as ANONYMOUS LOGON.

If you open up the IIS manager, on the left side there is a tree. In this tree, expand the server, then expand "Sites", then click the website you are using for this project (i.e. Default Web Site). On the right side of IIS manager, double click on the "Authentication" icon. On the next screen, right click on "Anonymous Authentication" and choose "Edit" from the context menu. Make sure "Specific User" is seleced and click on the "Set" button. Change the user to dom\web and enter the correct password. Click OK.

At this point, you may have an issue with dom\web not having access to the SQL server. You will have to create a SQL login for dom\web, and then create a user in the database that you want dom\web to have access to.

Solution 2

To add to the others - I have been in the situation where running the query works from SSMS but not from IIS via an application front-end.

I use mainly these two links only to resolve the issues. There are so many different settings for Active Directory, config files, IIS and the O/S to consider and it can be something you don't quite expect. The key is to know what the operating system/IIS/SQL Server thinks is going on, see DELEGCONFIG link below.

So the link to what is the Kerberos bible for setting up SQL Server correctly – http://msdn.microsoft.com/en-us/library/ff679930(v=SQL.100).aspx. I know it is specific to Reporting Services but still applies to an application server also as you can consider Reporting Services to be another application.

I have found the best way to troubleshoot authentication issues is with a tool called DELEGCONFIG; it helps you setup the correct SPN’s for Kerberos to work. You can find that tool here: http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1434. This is an IIS website that you install on the server and it tells you whether or not your SPN’s and delegation are setup correctly. It can also make the changes for you. I run it until all the checks are green - you will understand if you run the tool.

I don't leave the DELEGCONFIG website around in production but when setting up or having your production admins setting up the db/app servers they can use it to figure things out. Once you do it right, then remove the site or hide and secure it.

HTH

Comments

[Ben Thul]

How is the linked server configured? Specifically, what is listed for the login to use when using the linked server? Catalog views sys.servers and sys.linked_logins are going to be useful here.