Double hop problem with sql server 2008

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