Why does Windows/Integrated Authentication in IIS not pass user credentials to SSRS and SQL?

Question

Issue: In ASP.NET 4.0, I use my SSRS 2005 server's ReportService2005.asmx web service to get a list of reports. Also in .NET, I use Entity Framework to communicate with my MS-SQL 2005 database. When I use Visual Studio Development Server as my web server, calls to SSRS and SQL work fine. But when I switch to IIS 5.1, both SSRS and Entity code produce errors. I use only Windows/Integrated Authentication in IIS.

Errors: For SSRS, I get The request failed with HTTP status 401: Unauthorized.

For Entity Framework, I get Login failed for user ''. The user is not associated with a trusted SQL Server connection.

Attempted Solutions: In the Web.Config I added <identity impersonate="true" /> and that fixed Entity Framework errors but not SSRS errors. I expanded the identity reference to include my username and password, and that fixed all errors.

Question: Why does specifying my username and password fix the errors, and why does SQL say I am not specifying a username ('')? I thought Windows Authentication automatically impersonated the current user. How can I fix this without hardcoding a "service" account into the web.config?

Solution 1

Windows or Integrated authentication means that user is identified using windows credentials (or token) but it does not means that the request in running under that user. ASP.NET run-time will execute the request under worker process (App Pool) identity unless you configure it to impersonate some other identity.

So when you are accessing the site using development server, the server is running under your identity and so access to SSRS and Sql Server is done under your identity and it works.

When you loaded your site under IIS, ASP.NET request would be run under whatever identity is configured for the application pool. Typically this identity is local user and hence access to network resources such as SSRS or Sql Server would be denied. Adding <identity impersonate="true" username="your name" ../>, ASP.NET will run requests under your identity and that should work for both SSRS and Sql Server.

The curious case here is <identity impersonate="true" /> - under this setting, ASP.NET will impersonate currently authenticated windows identity. However, for this to work correctly, you have configure both IIS and ASP.NET on integrated authentication and deny anonymous access (in ASP.NET as well as IIS). Failing to do so may result in not authenticating current user's identity and the request would be run under anonymous user's identity (as configured in IIS). If you marked integrated authentication in IIS but not in ASP.NET then identity would not be passed to the ASP.NET request. You need to check your environment to see what exact scenario you had faced but ultimate result was your ASP.NET request was running under credential that has access to SQL Server but not to SSRS.

Solution 2

You also need to be aware of the 'double hop' issue - this means that your credentials can only be used twice.

If you are accessing a website using Windows Authentication and impersonation, that website can call another service as you. If that other service is another website (i.e. Reporting Services) which in turn calls another service (e.g. database) it cannot pass your credentials on again. This means that the database will throw an error if it expects credentials from the user.

Comments

[Devin Burke]

You are correct. I ended up using <identity impersonate="true" /> in the web.config and ((WindowsIdentity)HttpContext.Current.User.Identity).Imperso‌​nate() for the one reference to SSRS and that correctly impersonated me.