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.

Answer 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.

Answer 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

Answer 3

Create a login for dom\web on servers B and C and grant access to your databases to the newly created login.

Answer 4

Just to lay down my assumptions:

• You want the web app user's identity to be used in SQL
  • This then causes you to need to use Kerberos (for double-hop auth)
  • Limiting you to browsers that support Kerberos (IE, Chrome, Firefox (with settings changes))
  • Meaning you need to configure a Service Principal Name for the web app against the dom\web
• You are using IIS 6 or 7 as the web server
• Your web app's identity management is built to support Kerberos as its an ASP .Net membership provider built around Windows Auth

So, since you have mentioned SPN, I assume you have registered one using something similar to this:

setspn -S http/webapp.fqdn.tld dom\web

Did you then go and enable delegation on the user account dom\web? For double hop to work, the user account which is running the web app needs to be allowed to pass those credentials on again. On the user account's properties in the domain, find the Delegation tab and enable "Trust delegation for any service (kerberos)".

When that's fine, make sure your IIS website is set to pass-thru Windows Auth and not instead use the NT USER\Anonymous account.

At this point you can check whether Kerberos and IIS are all configured properly by running klist from the command line. That will output all Kerberos tickets issues for the currently logged on user. Look for one that has Server: http/webapp.fqdn.tld and check that forwardable and ok_as_delegate are set.

Next (as has been suggested above), add a group of users who will be likely using the app to SQL and give them permission to the database as required.

...its nearly 1am. I think I may give my suggestions a rest for now. Hopefully something in there helps you out, I'll try add more tomorrow at work. (I'm in NZ, hence the weird sounding timezone).

Answer 5

I remember having a similar issue in the past with the double hop which some colleagues resolved by having the SQL service accounts as members of the Domain administrators group when the services start, then removing them afterwards. It was some issue with SPN.

That probably wasn't the right way to fix the problem but it got everything up and running at a crucial period of unplanned downtime.