SSRS 2008 integrated mode: Security
When you configure SSRS 2008 to run in SharePoint integrated mode, the way you configure authentication and permissions in your SharePoint Web application matters a lot, because:
- That’s what report server uses to control access to report server items and operations.
- This would dictate what kind of security model you can use in your reports data sources to access external data sources.
Figure 1: Authentication settings for your Web application is an important step in the integration
Having SharePoint to perform authentication and access control doesn’t mean that SSRS is unaware of the security context under which reports gets executed. The report server uses an internal component called security extension (only available in SharePoint integrated mode) to query WSS object model to find out whether or not the requested resource or operation can be performed for the passed in security context.
Figure 2: Security extension queries SharePoint OM to check for permissions
Okay, let me take one question that I often get asked right after I say the above sentence. The question is :
Q) If SharePoint object model is only available on the server on which SharePoint is installed, how does security extension queries SharePoint? Via Web services?
A) Nope. Remember, as part of the installation prerequisites on the report server you need to have a minimum installation of SharePoint(Web front end). This makes the object model available on the SSRS machine when they are installed on two separate boxes.
Below is just the proof. Uninstall SharePoint bits from the Report server or configure it to Integrated mode without having the Sharepoint bits there and here is the error you get when hitting the report server:
The configuration paramter SharePointIntegrated is set to true but Share Point Object Model cannot be loaded.
In short, you set, manage and leverage the security model implemented by SharePoint, but report server just performs the following checks:
- Connection Request Validation Check: Whether or not the incoming connection request has access to the report server. If Kerberos is enabled, this request would be under the Windows identity of the user, otherwise in case of NTLM or FBA it is a complete different story and a bit complicated. Here you go:
If Kerberos is not enabled, incoming connection request is under the security context of a trusted account (SharePoint application pool identity). For this validation check, WSS OM is NOT queried. When connection request arrives at the report server, report server compares the application pool identity to the account information that the report server retrieved from the SharePoint configuration databases when the report server started and recognized it as a trusted account. On the report server this account is also added as a windows user and is given a local policy of the “Impersonate a client after authentication” property. This account is ONLY used to establish a connection and for impersonating SPUser object on the report server with absolutely no access to the any of the reports or operations. - Permission Check: Whether or not the incoming security context has required permission to do what it asks to do – for example executing a report. If Kerberos is enabled this incoming security context would be under the Windows identity of the user, otherwise (for NTLM or FBA) it is the SPUser object which SharePoint impersonates on behalf of the current logged-in user. For this validation check, WSS OM is queried by security extension.
Very simple! Eh? 🙂
Well, when you think about it, it makes much sense to off-load some of the responsibilities from SSRS side to SharePoint side and have SharePoint take over all report-related security. SSRS authorization model (native mode) is not anywhere close to what SharePoint offers. Two big wins right off top of my head:
- Beauty of role inheritance (Which doesn’t exist in SSRS native mode)
- Flexibility of report-level permissions (Rather than task-based permissions used by SSRS native mode)
Again, you’re in much better control of your reports if you allow SharePoint to be in charge of security!
Aside from the way security is setup on the SharePoint Web application; there are three more important factors that have a big time effect on how connections and requests are made between the two servers:
- Authentication Mode in Reporting Services Integration page in the Central administration site. In this page, you should use Windows Integrated only for the following scenarios, for the rest use Trusted Account mode.
- Your environment is Kerberos enabled
- You have a single-box deployment scenarios.
Figure 3: When you install SSRS Add-in for SharePoint you can authentication mode
- Your environment is Kerberos enabled
Important: As I mentioned here, as part of the integration , Reporting Services Add-in installs bunch of Proxy endpoints in the 12/ISAPI/ReportServer folder. Thankfully, if you have chosen the Trusted account authentication mode in the page above, when you are developing against these proxy endpoints the add-in handles the exchange of credentials between the SharePoint server and the report server , so you are off the hook! 😉
- Service Accounts: As I said here and here, my first choice, ALWAYS is domain user accounts, all the way through with no exception and no matter how I integrate both products (Stand-alone or distributed). Troubleshooting Network Service, Local Service and Local system is just PITA. There is this fantastic table here that provides service account recommendations for different deployment scenarios. Read up for yourself!
- Your security settings in the report data source itself. Data source is the last stop at determining what security context your report should use to access the external data source. As you can see in the picture below, after you deploy your data source to a Data Connection Library from BIDS, you still have a chance to edit the data source definition by choosing the “Edit Data Source Definition” from the provided ECB (Edit Control Button) menu. In fact, you have a mini SSO functionality here by which you can map the security context of the caller to either a sql or even a windows account. Isn’t that very nice feature that you can specify a Windows account here? This comes extremely handy when you don;t use Kerberos. More on Kerberos later in this post.
Figure 4: Option to edit data source
Figure 5: Mini SSO functionality available for your data sources , hosted in DCL
Let’s just look at the error that’s thrown. if I do NOT select the check box “use as Windows credentials” and still use “Stored credential” option with a windows account (dhltsrv\administrator):
Figure 6: Although we specified a Windows account , it was unknown to the backend SQL Server
This error may look bizarre, because dhltsrv\administrator was just sysadmin of my database server, but it makes sense! we didn’t specify that dhltsrv\administrator is a windows account (we didn’t select the checkbox shown above, right?). It is an unknown account type passed through to your report server. What do you expect?! 😉
Now let’s consider the following combination of security settings in the Web Application, SSRS Authentication mode and data source:
1) Web Application = Kerberos (Figure1), SSRS Authentication Mode in Central Admin = Windows Authentication (Figure 3), Data Source = Windows authentication (Figure 5)
- Result: Connection Request is under the security context of the caller. Permissions checks are performed for the caller.
- Advantage: Report server uses caller’s identity during report processing to retrieve data from external data sources. This means that when you set data source properties on such report, you can select the Windows integrated security option for the data source connection. No double hop syndrome!
2) Web Application = NTLM or Forms (Figure1), SSRS Authentication Mode in Central Admin = Trusted Account (Figure 3 – 2nd option in the drop down), Data Source = Windows authentication (Figure 5)
- Result: Connection Request is under the security context of a trusted account (Application Pool Identity). Permissions checks are performed on behalf the SPUser object that SharePoint impersonates when user logs in.
- Advantage: Easy to install and no need to go through Kerberos setup, but remember this is not going to scale. It is good for stand-alone installations and where Kerberos is not an option.
Obviously in either cases mentioned above , You can opt-in on having your data source to be executed under the security context of your choice. Have a look again atthe figure 5 above (Mini SSO functionality).
Hope this post helps you understand how security works in SSRS 2008 integrated mode! See you in the next SSRS post coming down the pipe soon!
Hi, and thanks for the great article! I missed such an article some time ago when I was trying to make MOSS 2007 and SQL RS work on two separate machines. That was a nightmare! At first I colud not grant database access to RS from the MOSS server, I even could not open RS web site from MOSS machine because of authentication problems. And there was NO ANY WORD in Microsoft MSDN about that I need to run cmd:
cscript adsutil.vbs set w3svc//root/NTAuthenticationProviders “NTLM”
if I do not use Kerberos on SQL RS WebSite, because IIS on RS machine is trying Negotiate (Kerberos) and fails if it does not work (and I do not use Kerberos).
When I granted database access, I still could not open “Set server defaults” page in MOSS and opening ReportServer WebSite gave an error about bad RS configuration. And RS configuration app told that SharePoint integration is not working. I was totally crashed… and only because (again!) MSDN did not tell that I must restart ReportingServices Windows service and IIS (or at least Web site) after I do that “Grant database access” from MOSS. And finally I got it working. I even went through all the steps 3 times on virtual PCs to see that indeed I have a process of installing that works 100%.
And finally I got to developing reports and was crashed again because of missing the point of Trusted Account and “Use as Windows credentials” thingy (but this time it was my fault – I thought that after publishing the reports from Visual Studio to MOSS they will work out-of-the box, uhh, silly me 🙂 ). I could run the reports only on http://localhost on MOSS machine, I got “Unauthorized” errors until I got everything right (as in this article) with that Trusted accounts and “Use as Windows credentials” thing.
And now I am stuck again, maybe the author of this article can help me. The problem is following.
The report viewer which comes with RS addon for MOSS has too little ways of customization to be useful in some project I am working on. So I tried WebForms.ReportViewer control which allows to override almost everything – button texts, parameter zone and so on.
Everything worked fine in a simple ASP.NET web site, I could get to reports published on MOSS and they showed up exactly as I need.
The problem – WebForms.ReportViewer has not WebPart analogue. So I tried to make a simple WebPart with embedded WebForms.ReportViewer and got it run on MOSS (that was another nightmare about granting FullTrust for my assembly without deploying it to GAC but it seems I got it working – at least no more Security Exceptions).
But again my customized WebForms.ReportViewer in my custom web part runs fine only on http://localhost on MOSS machine. I got “Unauthorized 401 error” if I try to open it from some other machine. And tonight it came on me – WebForms.ReportViewer does not know anything about that “Use as Windows credentials” checkbox! Because it seems a really specific setting for RS working in SharePoint integrated mode and only the real ReportViewer web part knows all about Trusted account usage.
So I am stuck.
Can anybody tell me how to get my customized WebForms.ReportViewer in my custom web part work with accounts the same way as MOSS ReportViewer web part does? Or tell me how to customize MOSS ReportViewer the way I need (I could not find the source code for it, only dlls)? For now I only have found source code for SQL 2000 Reporting Services ReportViewer but it is not meant for SharePoint integration so I do not know if it will work my way. BTW, I am using domain accounts for MOSS services and RS services (Web service and Windows service).
Thanks for any ideas.
Thanks for reply,
It seems, the blog engine did not accept my previous post (it told that the post looks like a duplicate) but I am not sure, maybe it still got to its destination. If so then delete this one.
Anyway, I finally made it work in Trusted account mode. I had to implement ReportViewer’s ServerCredentials interface and it worked. Now I am thinking how to make it at least as secure as the real SharePoint ReportViewer is. The SharePoint ReportViewer is installed in the GAC by default so it gets full trust for the entire server machine! For now it seems my custom webpart is more secure – it has full trust only for that one website with the custom policy config.
I wish Microsoft could publish the source of SharePoint ReportViewer webpart to save me and lots of other people from experiencing nightmare trying to customize report viewer…
Midix,
I am glad you got this to work!
sorry I was under the impression that you’ve raised the entire trust level on the web site and didn’t noticed that you’re applying CAS to the assembly containing your custom WP. If that’s the case, no worries.
Well, with regards to reportviwer web part being in GAC , I’d slightly disagree with your statement of report viewer not being secure. Remember, before a process OR an intruder find its way to this control, it should have passed SharePoint’s authentication and ACLs ( list level and item level permission) , so I am not sure how much it would have made sense for the SSRS team to run their control with partially trusted mentality. Don’t get me wrong, by no means I am in favor of running in full trust and I’m very good at setting granular permissions at my custom dev works , but I also belong to the group who thinks there is a place for everything 🙂 IMHO, in report viewer and due to the security architecture layer of its host (SharePoint) running in partial trust was just an overhead in configuration and quite frankly a bit unnecessary.
Midix, customizing out of the box SSRS components have always been kept limited in SSRS from day one (I’ve been with this product for a long time so I can say this 😉 )! Now , by hosting ASP.NET inside the SQL CLR and inside the reporting services service process it’s become even more limited , but you should remember that since the OM is fully exposed and WMI provider is there for any configuration and scripting work , there is really no room left for complains! SSRS has evolved to be application server and certain customizations has been their least concern. However , in case of Report Viwer web part , I guess they could give us more flexibility , but they didn’t!
Do you have a blog or something that you can share your findings with regards to WebForms.ReportViewer? if not , please email me your stuff and I will publish it under your name. I am sure many people will find it useful. You;re on the right track bro! keep up the good work and keep us updated. thanks
Reza Alirezaei,
I’ll try to sum my experiments together (now everything is a bit chaotic 😀 ) and after some weeks or so I think I’ll have some kind of an article about my experience with RS starting from installing RS and SharePoint on two clean Windows Server 2003 machines without using Kerberos and up to getting a customized reportviewer to run reports which get their data from XML webservice (XML datasources may get a bit tricky when trying to use with RS, there are some XML namespace issues and missing nodes for empty datafields).
So when I’ll be ready I’ll let you know 🙂
Sounds like a plan! Looking forward to your write up! It is all about sharing 😉
thats good… but how configure the same case with Oracle Database?
Reza Alirezaei, great article. I wonder if you could help on this issue:
I have MOSS configured with kerberos, including the ssp using host headers and port 80 and the infrustructure up and WSS/MOSS sp1 in a test environment. Everything works great, except when I try to connect with an account in a transitive trusted forest. All other MOSS access where granted is successful. The file that gets the error is from the sample for adventure works database, product catalog (Data Source=SQL; Initial Catalog=AdventureWorks).
I get this error form any user in domain A: An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.
Here is the scenario: Intended users are in domian A. Servers, MOSS, SSRS Ent, and SQL standard (with the SSRS databases), are in domain B. Domain A users get the same error when logged on to domain B servers as well. Domain B user have no issues so long as they stay on domain B. All server and svc account are full delegation, require kerberos.
What do you think? Your respinse appreciated.
Hi Reza,
Great blog posting. I have been struggling with getting reports to execute from within MOSS 2007 for a couple of weeks now.
I have a MOSS server, SSRS 2008 server, and an SQL 2005 server, and all services are running under domain user accounts. Because users connect to my web server via the Internet, they cannot authenticate using Kerberos.
I’ve been messing with protocol transition and constrained delegation, but still can’t get reports to run through MOSS.
I was wondering if you had a recommended security configuration for an “Internet-facing” deployment with multiple servers?
Eric,
Is security issue when users try to execute the reports or when accessing the data sources?
Jan,
I have implemented pretty much the same configuration for a customer of mine here in Toronto with no problem. Please look into the event viwere and/or ULS logs to be more specific about what’s going on. I can’t help you by looking at such generic error
This is one of those rare posts which I truly enjoyed. Throughly explained how RS handles authentication(complete with pictures — I’m a picture person).
Service principal names (SPNs) are used by the Kerberos system to ensure that only certain accounts have permission to delegate a specific service on behalf of a user. An SPN needs to be configured for each service and address using the Setspn utility from the Windows Server 2003 Resource Kit. You must have the appropriate privileges to update the Active Directory to use the Setspn tool. As an example, to configure a server running Microsoft Office SharePoint Server in the toft.com domain called moss.tofot.com, with an application pool running as the server farm account (toft\farm), the following command would be used:
What exactly do you mean when you say that security option 2 will not scale? I didn’t follow why it wouldn’t scale. More complex administration inside of SharePoint?
Thanks.
2) Web Application = NTLM or Forms (Figure1), SSRS Authentication Mode in Central Admin = Trusted Account (Figure 3 – 2nd option in the drop down), Data Source = Windows authentication (Figure 5)
Result: Connection Request is under the security context of a trusted account (Application Pool Identity). Permissions checks are performed on behalf the SPUser object that SharePoint impersonates when user logs in.
Advantage: Easy to install and no need to go through Kerberos setup, but remember this is not going to scale. It is good for stand-alone installations and where Kerberos is not an option.
Dear Reza,
Thanks for your great article. It really helps!
As you know, you can connect to a SharePoint list via XML data source and use fields to generate reports in SSRS. The point is if you want to use this feature, the authentication mode in CA should be windows integrated security and Trusted connection doesn’t work. It seems you need a minor modification to your article “ … In this page, you should use Windows Integrated only for the following scenarios, for the rest use Trusted Account mode. Your environment is Kerberos enabled. You have a single-box deployment scenarios.”
Best
Farshid