Published on Friday, December 5, 2014

Single-Sign-On (SSO) with SSRS, SSAS, and SQL

As I mentioned before, software security is one of my favorite topics.  The current trend is for companies to expose their data on the Internet.  My observations are that security is implemented towards the end of a software application/system development.  Once the data is ready for publishing, securing it on the Internet quickly becomes an overwhelming task.  Why is that? 


There are several main challenges in securely publishing data on the Internet.  Here are some of them:


1) Integrating Multiple Components

A common Business Intelligence (BI) Solution will require a) a Web application; b) Report rendering engine; c) Data sources, and d) various supporting services including security. All these components usually does not support a common security model.


2) Various Security Needs   

Both row/horizontal/ and column/vertical/ level security are needed. Some examples: a) a manager can only see the data/rows for his/her subordinates; b) an employee can see his/her own HR data but only some columns/fields of his coworkers HR data.   A combination of the two can create a very complex permutation matrix, which can be very difficult and expensive to implement.


3) Multiple Logins

The user may need to provide different logins to the various components, which makes the solution not very user friendly plus it creates a lot of security concerns.


4) Support and Maintenance

The solution can quickly become very expensive and difficult to support.  Different technical expertise needs to be recruited to support it. For example: security expert, web app expert, database expert, report writer, infrastructure expert, content expert, etc.


A common solution will be to acquire third party components/product and to integrate them with their business needs.  While selecting an eye-candy front page is not difficult,  finding or building a solid security platform could be quite a challenge - more specifically, coupling the user web identity with the data source security.   The usual approach to create multiple data sources with different security settings, and then assign them to the different user roles.  This works for simple data security needs but it fails for more complex deployments.   To further the topic, there is also the need for report/data subscriptions.  Most systems assume that the user is currently logged it; what if the user is offline and the report needs to be prepared and send within the user identity!  These are some of topics most BI vendors do not want to talk about.  


The “BI Portal” offers a security solution that addresses all of the above challenges and business needs.  It does that thru the use of a Single-Sign-On (SSO) for the Web app, SSRS, SSAS, and SQL.   The portal supports both Windows security for the Enterprise/intranet/extranet applications as well as the Internet and its custom security requirements.


How is it done?  The portal integrates with SSRS.  It uses SSRS as a document repository and a report rendering engine as well as its security features. (Note: we do also offer a non-SSRS stand-alone SQL deployments.)  The SSRS custom security extension is provides which makes SSRS Internet friendly as well as email enabled.  When the Internet user logs into the portal, his/her credentials are also passed to the SSRS service. When SSRS service needs to render a report, it will forward the user identity to the data sources, SSAS or/and SQL.  This is done thru a custom SSRS data extension, which can be configured to support either per-Role or per-User security granularity.   Both SSAS and SQL data extension supports all the security models offered by these data sources (see my blog on Security in SSAS). 


Here is an-online example of this integration with the SSO using the secured web site:


1)  A business analyst logs into the web site. The landing page is shown below.  It’s called the Report Previewer since it allows the user to get visual preview of the report without the need of opening it. Note:  It is common for users to open multiple reports until they find what they are looking for, creating a lot of unneeded work on the server in report rendering.  This feature solves this problem. The snapshots are NOT shared among users since this will be a security breach.  The snapshot are per-User.


The user wants to create new report, so s/he right-clicks on the “My Reports” folder and select “New Ad-Hoc Report using MS Analysis Services”. This opens a dialog asking the user to select what SSAS endpoint and cube to use.  This type of report allows the user to dynamically slice-n-dice the data by dragging and dropping SSAS object with the mouse.



2) The user drags-n-drops the geo dimension on rows. S/he also creates a custom calculated measure showing her/his user name identity. The UI is shown in the snapshot below. The MDX reveals that in this particular deployment, the “CustomData()” security implementation is used.  As you can see the user identity is shown in the output grid.  The user saves the report by clicking on the toolbar button.  



3) The user wants to convert the ad-hoc report to a standard SSRS report so s/he needs to prepare an SSRS Datasource that will be used by the new report.  The snapshot below shows the definition of the datasource using the Enterprise Software Solutions (ESS) custom SSRS data extension for SSAS.  Notice the checkbox “Credentials are not required” at the bottom of the dialog box; this instructs the SSRS engine to use SSO for authenticating the user!



4) The user is ready to convert the ad-hoc report to a standard SSRS report. S/he right-clicks on the ad-hoc report and select the “Convert” option. (Note: There are three choices: a) static parameters will use what is currently selected in the UI; b) Dynamic values – it will convert any instant filters into parameters; or c) Query-Based – it will allow the user to prove a query for fetching the parameter values).  With a single click, the ad-hoc report is converted into an SSRS report.  



4) The SSRS report using the SSAS data source shows that the identity of the current user is as expected.  This demonstrates the use of a SSO with the web app, the SSRS service, SSAS ad-hoc reporting (aka cube browsing), and SSRS report rendering!



5) The user also create an SSRS report using the SQL Datasource configured for SSO. The snapshot below shows that the same user identity seen by the SQL engine.  Note: For manually creating a report, the user first right-clicks on a folder, and selects a new report, then it expands a data source and drags-n-drops objects in the UI. After the report is save, the user can right-click on the report and continue editing with more precision using the ReportBuilder.


There are a couple of unique feature worth mentioning:


5.1) Expandable Datasource is concept introduced by ESS.  The datasource is presented as a hierarchy of object: from top to bottom – for SQL: Database, Schemas, Tables/Views/Stored Procedures, and Row Data at the bottom;  for SSAS: Database, Cubes/Perspectives, Measure Groups/Dimensions, Hierarchies/Attributes/Measures, Levels, and Raw Data at the bottom. The snapshot below shows some of the hierarchy objects.  The user identity is used when retrieving the metadata/objects so the user can ONLY see what s/he is allowed to see!


5.2) When a new user logs into the portal and requests access to the SQL data via SSO, her/his account is auto-provisioned.  This means that the user is automatically added to the database application roles, and the stored procedure “usp_ProvisionNewUser” is called, passing the new user identity as a parameter.  This allows the administrator to place code inside this stored proc, which can auto-configure new users!  The newly created Application Roles can be viewed inside the SQL Server Management Studio (SSMS) and further configured.  The schema “SqlRoles” is automatically created and all new users are given read/select/ access to it; it create only one table “tblTest” to confirm that the access is working correctly.  The administrator can change the schema and access settings thru SSMS. Note: the SQL Data extension can be configured for per-User or per-Role based authentication; accordingly, application roles will be created for each user or for each role the user belongs to.