Monday, June 7, 2010

SSRS - SQL Server Reporting Server - External Image Issue (and Solution)

The other day I encountered some complicated issues with SQL Server Reporting Server (SSRS) and including external images from a file store that was not directly accessible to users (or SSRS).  I've always assumed that the credentials that you pass to SSRS to generate reports where the contextual credentials in which the reports were generated (aka impersonation).  Well, that assumption FAILED to materialize.

The intent of this post is to hopefully assist others with similar issues and to document my findings for future use and reference.

So, here the scenario and configuration (all in a TEST environment which parallels the PRODUCTION environment):
  • Box 1: Web Server
    • IIS 6
    • Web App: MyWebApp
    • File Store Virtual Directory: MyWebAppFileStoreVd
  • Box 2: SQL Server
  • Box 3: SSRS
  • Box 4: File Store
    • MyWebApp File Store: MyWebAppFileStore
As a developer, I had access to the file store (Windows permissions).  So, while creating reports in Visual Studio's Report Designer with hard coded image paths (for testing, because dynamic wasn't working - this is the issue), I could view the images in the report.  However, once the reports were deploy to SSRS, the image placeholders contained no path information and the infamous 'red X.'  This was also the case when the report was request and render via the Web app.

Ahhhh!  What's the problem?  The file store contained permissions for the SSRS user with the credentials passed to SSRS from the Web app.  After a couple of day of 'banging head on desk, computer, floor, etc...), I had an epiphany - maybe the SSRS user is not impersonated???  That was it - the user is NOT impersonated!  Reports are actually generated via the service account of SSRS - this makes perfect sense now; however, I had always assumed that impersonation was taking place.  I digress...

I've read that Microsoft's solution is to run the SSRS service as a domain account and give that domain account whatever permissions it needs on the file store.  Well, that is NOT a solution in our environment.  So, what did we do to solve the issue?  Well, we...

We decided to create a virtual directory on the Web Server (Box 1) that points to the aforementioned File Store (Box 4) and restricts access (over http) to a specific computer via IP - the SSRS IP (Box 3).  This essentially creates a File Store (Box 4) web share proxy on the Web Server (Box 1) accessible only to SSRS (Box 3) over http.

The following is the process and associated steps taken to successfully enable this scenario:
  1. Create a Virtual Directory outside of the MyWebApp Virtual Directory that points to the MyWebAppFileStore File Store - /MyWebAppFileStoreVd/
  2. Set MyWebAppFileStoreVd Virtual Directory’s Directory Security to ‘Enable Anonymous Access’ and clear all ‘Authenticated Access’ checkboxes.
    1. Virtual Directory Properties -- Directory Security Tab - Edit button (red highlighted button).

    2. Check the ‘Enable Anonymous Access’ checkbox.
    3. Clear ‘Authenticated Access’ checkboxes.
    4. Click ‘Ok’ and then ‘Apply.’

  3. Restrict access by the Report Server’s IP.
    1. Virtual Directory Properties -- Directory Security Tab -- Edit button (green highlighted button).

    2. Select the ‘Denied Access’ radio button within the ‘IP Address Access Restrictions’ group box.
    3. Click the ‘Add…’ button.

    4. Select the ‘Single Computer’ radio button in the ‘Grant Access’ dialog box.
    5. Enter the Report Server’s IP address in the IP Address masked textbox.

    6. Click ‘Ok’, ‘Ok’, ‘Apply’, and ‘Ok.’
That should about do it...

While this implementation works and works well for my specific scenario, this may not be the best implementation for your specific scenario.  I hope that this post saves at least one person the hours of troubleshooting and headaches that I incurred getting the implementation to work...

Thanks for reading...

No comments: