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: https://biportal.entsoftsol.com/

 

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.    

 

 

6) The user is given access to a view called “vw_SecurityTestRecords”. This view dynamically filters the records based on the user identity (i.e. “select * from tblTest where UserName  = user_name()”).  The user creates a report based on this table and confirms that only the assigned records are visible.   The user then decides to share his reports with other users. S/he simply copies them the “Demo Customer\Shared Reports” folder. The security is automatically set.

 

 

7) “User 1” logs into the system. S/he opens the shared reports.  The SSO identity correctly flows to the data sources, the reports confirm the “User 1” identity seen by SSRS, SSAS, and SQL.  While in SSAS, the previous user can all Geo Country members, “User 1” can only see “Canada and United States”.  The SQL records are also correctly filtered as shown below.  As more users log into the system, they will be automatically provisioned and will have access to the reports with data filtered only for their security clearance.

 

 

 

The above example shows very powerful security features that work out-of-the-box. Even if the BI Portal is not selected as the front-end UI, the security framework is still available for purchase.  The solution is ONLY using industry standards (such as WS, SOAP, XML, RDL) and popular BI products (such as SSRS, SSAS, SQL).  The BI Portal works with all MS SQL Editions from the free SQL and SSRS Express Editions to the Enterprise.  Note: Alternative scaled-down SSAS engine can be offered for deployments where MS SSAS is a major cost consideration.

A deployment document comes with the BI Portal that gives step by step instructions on how to set it up.  The administrator can select from various configuration options. For example, if the SSRS service is running with a low privileged account, the SSAS and SQL data sources can be configured to impersonate Windows accounts with elevated rights while connecting to the corresponding data source.  There is also a data extension for OLEDB data sources.  

The portal also offers advance report subscriptions features and extensions.  A report can be subscribed to by any user and rendered in off-peak hours and emailed to the user.   The report emailing can be configured to be triggered by a business rule – for example: send me only the report if the sales are down 10% compared to last week.  The user identity is preserved so the report is custom prepared for the given user using his/her security rights.


It is important to point out that with a SINGLE Report and Datasource we are able to serve unlimited number of users with unique security requirements! A step further is to host multiple clients on the same web site using the same data sources; each client gets a company-tailored UI while all the reports, data, and servers are shared under the hood. We are currently using this platform to provide BI services to multiple clients while hosting them on the same machine and using the same reports and data sources. The alternative will be multiple reports and data sources, which can be quite expensive to develop and support.

Please, let me know if you are interested in a demo.  I can give you login credentials to the web site listed above so you can try on your own, or we can do an online web session and I can offer you assistance and additional information.  

Rate this article:
No rating
Comments (0)Number of views (14339)
Print
Constantin Ivanov
>

Constantin Ivanov

Constantin "Koce" Ivanov has been a developer for over 20 years. He is known in the industry for his MS SQL and SSAS skills. He is also a .Net and Web expert. In 1996, he has achieved his Microsoft Certifications (MSCE, MCSD, MCDBA, MCT). He has been working with MS SQL since 1996 and

Other posts by Constantin Ivanov
Contact author Full biography

Please login or register to post comments.