Sql-server – Double hop problem with sql server 2008

linked-serversql serversql-server-2008

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.

Best Answer

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.

Related Topic