SAML Authentication support with SQL Reporting Services

Couple of days back I received the following set of questions from a customer and in this blog post I would like to discuss the answer to his question for the benefit of others

How to get SSRS working with SAML Claims Authentication?

If SSRS in Native Mode doesn’t support SAML Authentication how does Microsoft Dynamics CRM support SAML Token Authentication using SSRS in native mode?

Can we leverage Microsoft Dynamics CRM to allow SSRS to support SAML Authentication?

Let us first start with basics before we answer customer’s question here.

SSRS in Native Mode supports

  • Windows Authentication
  • Basic Authentication
  • Custom Authentication

SSRS in Sharepoint Integrated Mode

  • Classic Windows Authentication
  • Claims Authentication  (Starting Sharepoint 2010 & SQL 2012 SSRS since SSRS is available Shared Service in Sharepoint and further C2WTS can convert a STS Token to Windows token which can be authenticated by external data sources)

So we all are very clear that Sharepoint natively supports Claims & SSRS and hence SSRS in Sharepoint Integrated mode is one of the favorite solution to get SSRS working with SAML Claims which is tried and tested but Customer is looking for options outside Sharepoint to save some cost

Although SSRS in Native mode doesn’t support Claim Authentication out of the box, it does support Custom Authentication. Custom Authentication gives a developer flexibility to develop his own security extension to authenticate the user and further authorize the user permissions. So if we have good programmers available, we can integrate any custom web application which supports SAML token to integrate with SSRS in native mode by developing security extensions for custom authentication.

References

http://msdn.microsoft.com/en-us/library/ms155029.aspx

http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008!Security%20Extension%20Sample

Microsoft Dynamics CRM Team has leveraged the custom authentication of SSRS in native mode by developing their SSRS Security Extension ( this should explain why you need to install SSRS extensions for Dynamic CRM)  to authenticate & authorize the users in SSRS. Further the Microsoft CRM Team leverages the APIs exposed by the Reporting Service Web Service to deploy, delivery, subscribe for the reports.

While using SSRS in Dynamic CRM, the security is completely controlled from within CRM and as the user & security roles are defined in CRM.

While using SSRS in CRM mode, some of the functionalities & features of SSRS may not be available for e.g you cannot have custom code within your SSRS Reports since CRM uses RDL Sandboxing which doesn’t support custom code

When it comes to building SSRS reports for Microsoft Dynamics CRM 2011 using Visual Studio (Business Intelligence Development Studio, a feature that can be installed as part of SQL Server), there are two options available that provide you the ability to query and format your CRM data into flexible dynamic reports. The options are SQL reports querying the CRM Database Filtered Views or using Fetch, a proprietary query language commonly referred to as FetchXML, this language utilizes the CRM Report Authoring Extension that is to be installed alongside Visual Studio’s Business Intelligence Development Studio.

Although you can use develop SSRS reports in Dynamics CRM, you have very limited functionality & features as compared SSRS in native mode or Sharepoint Mode which practically makes your SSRS deployment useless.

You can read the following blog from a fellow PFE on the challenges of custom report development in Dynamic CRM

http://blogs.msdn.com/b/crminthefield/archive/2012/11/27/custom-reporting-in-microsoft-dynamics-crm-fetch-vs-filtered-views.aspx

To answer customer question

  • Technically we can use SSRS with Microsoft CRM to support SAML but it will be available with restricted functionality which customer should be ready to accept
  • Approach 2: would be develop security extension for SSRS to support SAML but this would require skilled resources and would involve lot of efforts in developing & testing the security extension.
  • Preferred Approach would be Sharepoint 2010-2013 and SSRS 2012 which seamlessly supports SAML with all the SSRS functionality and further with SSRS 2012 you can set the execution context while using stored credentials which can eliminate the pains of Kerberos authentication and make life easier. Further SSRS in Sharepoint Integrated Mode is supported by Standard Edition of Sharepoint.

Hope this helps !!!

Parikshit Savjani
Premier Field Engineer

5 comments

  1. Thank you for this post. I’m using reporting service in native mode and have to integrate it with ADFS. Unfortunately I have seen that single sign on technologies are not supported; only by developing a security extension for SSRS. The alternative is to use SharePoint, a product that I’m not really a fan… Seems strange to install, administrate and licence SharePoint only to be able to have SSO… Are there any examples of developing a security extension for SSRS? I would like to evaluate the effort involved in the process in order to make a decision.
    Once again, thank you.

  2. Thank You for the post.

    Basically looking for information that are in the previous query message from ‘Isabel Marcelino’.

    With SP2016 Integrated SSRS lagging in features compared to Native Mode and no specific direction from Microsoft yet, in terms of SharePoint Integrated SSRS road map about when or if at all the same features would be support in integrated mode, SP2016 integrated mode SSRS option to have SSO (SAML based authentication) is off.
    Thus, are there any attempts to have creation of sample/reference custom code for SAML that could be used as base to develop extensions for Native mode SSRS ?

  3. It took a lot of trial and error to figure out the appropriate configuration changes and what custom code needed to be written. We were finally able to get both SSRS Report Manager and Report Server to work with SAML. We have tested the solution with Shibboleth and Okta, but am confident that the solution would work with just about and SAML Identity Provider.

    1. @Netoulook
      Are you able to provide details of how you achieved this please? We want to integrate Okta with SSRS but can’t seem to find any information on how to achieve this. Any help would be much appreciated.

      Thanks

Leave a Reply to Isabel Marcelino Cancel reply

Your email address will not be published. Required fields are marked *