Sql – ASP.NET web app can’t use multiple impersonation for authenication

asp.netintegratedreporting-servicesSecuritysql

I have a asp.net app (uses windows authentication for access) which (stipulated by the security team) needs to connect to a remote SQL Server 2005 using integrated security.Because of the fact that it is remote SQL server I needed to impersonate a custom account (impersonating the original caller would not work) via :

<identity impersonate = "true" userName="domainname\user" password="password" />

This workes fine. The rub is my app also connects to an SSRS server for reporting needs using the ReportViewer control. The report server is on a separate server and the security team mandates that all calls to this server must be using the original window's account for auditing purposes. It seems my only option was to to try and separate my app into folders and use a "location" tag in my web.config and use separate identity tags. Such as: 

 <location path="Reporting">
    <system.web>
      <identity impersonate = "true"/>
    </system.web>
  </location>

Note: no username and password specified which means it should impersonate the original caller.

However to make matters even more complicated my app is a Masterpage/content page app. The master page makes calls to SQL to populate menus and such. Bottom line is the dual impersonation track is not working. I am ready to throw my hands up and declare that that this can not be done. If there was a way where I could have the app impersonate the original caller which would satisfy my SSRS auditing needs yet make connections to SQL server as the custom domain account. I cannot use SQL authentication: not allowed although that would solve this issue.

Best Answer

Have you tried the following setup:

  1. Set impersonation to true. This is necessary for authentication into the application and for access to the SSRS to use current user logged in.

  2. Use one connection string to SSRS that has Integrated Security set to true, so that the impersonated user passes straight through.

  3. Use a second connection string, with the custom user name and password hard coded into the connection string. You can encrypt the connection string section of the web.config so that it isn't visible to human eyes, but the framework will automatically decrypt this on the fly when creating a connection.

I have a similar situation (need a specific account to retrieve specific data, but the general impersonation for the rest of the service functionality) and this setup is working.

EDIT: The general syntax for encrypting your web.config from the command prompt is:

aspnet_regiis -pef "connectionStrings" [PhysicalPathToApplication] -prov "DataProtectionConfigurationProvider"

Encryption is done on a machine per machine basis, so the encryption will have to be done on the specific server. You can pull up more documentation on this if needed.

Related Topic