By: Rob Fisch | Comments (1) | Related: > Reporting Services Security
Problem
It is the job of every DBA to configure security in the SQL Server environment. This can be done at many levels including server, database, tables and views, etc. But if you haven't worked with Reporting Services security or Sharepoint security, there is still a lot to learn.
Solution
This article focuses on the different ways of accessing the Reporting Services portal along with security and design implications. I will also discuss some of the features of using both the Sharepoint Integrated mode and for lack of a better term, the Standard mode. (I'm not really sure what Microsoft calls it.)
One last note before we begin. There is no possible way to include detailed information about all the security options, configurations and installations. This is a conceptual article. For specifics you should consult SQL Server Books Online.
Demystifying the Report Server, the Report Manager, and Sharepoint Integration Mode.
In Native mode, when you install Reporting Services, you end up with two virtual directories. If you followed the defaults you end up with something like http://localhost/reportserver and http://localhost/reports.
The IIS tree will look something like this:
The Report Manager looks like this:
In a web browser, the Report Server (http://localhost/reportserver) looks like this :
By the way, why does the Report Server look so blah? It's because it's not really meant for user browsing. But there are things you cannot do without it sometimes. The trick that comes to mind is that of passing parameters within the URL itself. Another is hiding the toolbar. See this tip for more information.
In Sharepoint Integrated mode, you end up with one Report Server like the one above, but the URL will likely be different. Instead of a Report Manager, you end up with a rather normal looking Sharepoint Document Library with some added features. Document libraries look something like this:
OK, now you know what everything looks like and what it's called. Let's review.
Report Manager - Standard mode only
Sharepoint Document Library - Sharepoint Integrated Mode only
Report Server - Can be found in both modes. Can pass parameters and other nifty things in the URL.
Report Deployment
In Native mode, the deployment configuration looks something like this.
The TargetServerURL is the URL of the Report Server (not the Report Manager). The Target Report and Datasource folders are indicated without the URL. Simply use folder names. These can be nested with forward slash '/' marks. Multiple slashes can be used for sub-nesting.
The Target configurations for Sharepoint mode, are a little different. The TargetServerURL will be the home URL of the target Sharepoint site. Depending on the number of sites and its hierarchy, this can be a long path. For instance, it could be something like http://localhost/TopLevelSite/subsite1. For the Target Report and Datasource, you would use the full URL path (not just the folders) to the target document library (i.e. http://localhost/TopLevelSite/subsite1/MyBestReports).
(Note: In a production environment, you wouldn't use 'localhost' for any of the URLs. That's only shown as an example.)
Design Implications
So what does all this mean? When you deploy the reports in your solution they get published to a single folder. This is true in either mode. This one to one mapping of 'Solution' to deployment folder is a critical concept in understanding how to put your portal together. This is for two reasons.
- You will want to plan your folder hierarchy by functional areas so the drill downs make sense. This is more important if you are using the Report Manager by itself without any other front end.
- You will want to configure security by folder. You can grant exceptions at the report level, but for the most part, you want to think of a solution (and its target folder) as both a functional area (i.e. Accounts Receivables, Manufacturing, Human Resources, etc) and a security boundary.
Security Inheritance
With both Sharepoint Integrated and Native modes, the principle of security inheritance applies. That is when creating an object, be it a new folder or adding a report, each new object inherits the permissions of its parent object. This feature has an override for granular control down to the report level.
Now, remembering that a solution can only deploy to a single folder*, if you have 10 reports in your solution, but you want different security on each report, you deploy them, and then individually override the inheritance feature for each report.
* This isn't exactly true...you can do tricks with various deployment folders in the configuration manager, but I can't really think of a good reason to ever do this in a production environment. I highly recommend against doing this.
In Sharepoint Native mode you can configure permissions by using the Report Manager or the SQL Server Management Console. It's much easier to do this at the Management Console, but you might not have access to the that. Or you might want to delegate the permission function to a power user that you would never give the developer tools to.
To access the screen from the Management Console, you have to "Connect" to "Reporting Services" from the Object Explorer, and choose the server. Then drill down to the target report or folder, right-click, select properties, and then Permissions. When you change from "Inherit roles from the parent folder", this breaks the inheritance and allows permission granularity.
In Sharepoint, you handle report, folder and/or library permissions exactly the same way as you would in any Sharepoint object.
Windows or SQL authentication?
Regardless of how the SQL database server is setup, the reporting datasource is another matter. There are some important security design aspects to consider. One critical topic is -- where is your data?. For instance, if your data is on your Reporting Services server, then you can use Windows Authentication all the way through without too much trouble at all. However if your data is remote from the Reporting Server, then you've got a problem with using Windows Authentication in your report datasource. Using a web browser from a client workstation, browsing to the report server, which points to yet another server for data can be quite tricky. This is known as a double-hop. I've read about all sorts of tricks to handle this having to do with impersonation and such...but to be honest I've never actually tried it. After you've Googled this topic enough, unless your an IIS guru, you'll either get your data to the reporting server (via replication or SSIS), or leave it where it is and use SQL authentication in the Reporting Services datasource. This makes it even more important to be really tight with portal permissions.
Incidentally I recently tested report performance when reporting to a remote server (in the same data center) and reporting direct to data on the Report Server itself. As funny as this sounds, performance was faster when reporting remotely. Please don't consider this conclusive evidence. It's completely anecdotal.
Summary
When planning your portal, think carefully about both reporting functional areas and security maintenance. Plan your hierarchy accordingly. You'll be glad you did later on.
Next Steps
- If you are interested in installing Reporting Services in Sharepoint Integration mode, check the Microsoft Installation and Configuration Guide for SharePoint Integration Mode. (Note: If you don't have Word 2007, you will have to get a friend who does to convert the document for you.)
- Stay tuned for Part 2 of this Portal Management series. The topic will be "How to show and hide reports" in your portal.
- Take a look at these other Reporting Services tips.
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
