Power BI Data Security – Power BI Report Server

Power BI Report Server was released as a way to host reports on premises. It was one of the highest requested features for Power BI. Power BI Report Server offers a subset of Power BI Service capabilities and as such does not have the rich collaborative or security options as seen in the service (online). I have a number of other thoughts on this tool, but that is not the purpose of this post. I am often asked in sessions on Power BI Data Security about Power BI Report Server. The problem is that Power BI Report Server is not what Power BI was intended to be and as such security is very different.

Power BI Report Server Uses Files and Folder Security Model

Unlike Power BI Service which leverages the Office 365 security model with workspaces and apps, Power BI Report Server only supports deploying Power BI Desktop files as Power BI Reports in SQL Server Reporting Services. Each file is uploaded to the Report Server and it is viewed by opening the report.

You have essentially three layers of access to the report file security in Power BI Report Server.

1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server Report Nuances

If you have read many of my posts around Power BI Data Security, I have discussed gateways, workspaces, and even Office 365 groups. The following items are uniquely related to Power BI reports stored in the report server.

Data Sources

Because it is not possible to use a Reporting Services Shared Data Source with your Power BI reports at this time (not sure if this will change), the data source information will need to be managed by report. This is not dissimilar to the Power BI Service. However, for on premises data we use the gateway with the service. There is not a common or shared data access feature in Power BI Report Server. Data sources are included in the report and can be managed in the deployed report by clicking the ellipses on the icon and selecting manage.

Only the report creator, Content Managers, and Publishers have permissions to see and modify this information.

Protecting Data Sources

One concern raised is whether data sources with credentials are downloaded when the Power BI file is downloaded. First key idea is that only users with the Publisher and Content Manager roles can download the files

When a Power BI report file is downloaded, you are prompted for credentials when refreshing data. Passwords are encrypted so they must be reentered when the file is opened or data refreshed in the desktop.

Mapping SSRS Roles to Power BI Functions

The portal, folders, and files are secured using SSRS roles. Here is a high level summary of the roles and how it impacts Power BI Reports

• Browser – This is similar to a read only function or if you would deliver the report as a Power BI App in the service. This should be used by anyone who needs access to the report, but does not create content.
• Content Manager – This is for a content creator with admin privileges. This role can manage content and user access.
• Publisher – This is for content creators who do not need to manage users. Most content creators fall into this category.
• Report Builder – does not apply to Power BI Reports which use Power BI Desktop to develop reports.

Avoid Deploying to the Portal Home Page

I would recommend you not allow Power BI reports to be deployed to the primary portal, but create folders to manage the group of content creators and managers based on the department or group level needs.

Reports deployed to the home page of the portal should be managed as enterprise assets. Use the folders and related security groups to manage who can deploy at this  level.

Wrap Up

I would like to extend a special thanks to Kathy Vick, Principal Consultant at Pragmatic Works. She provided guidance on implementing Power BI Report Server. Thanks Kathy for the help on this topic. Check out Kathy’s Twitter (@MsKathyV)  and LinkedIn account to learn more.

Check out more about Power BI Data Security in my blog series.

Oracle Tips for MSBI Devs #3: Choosing Drivers

When working with Oracle, drivers are truly a pain to get working correctly.  I will discuss my preferred choice and why for the following tools – SSIS, SSAS, and SSRS.

SSIS Drivers

Without much question, you should use the Attunity tools for working with Oracle data in the Data Flow task.  In SSIS 2008, the SSIS Connector is free and can be found here:  http://www.microsoft.com/download/en/search.aspx?q=oracle%20connector.  It includes the connection manager, source component and destination component.  Without a doubt this is the only way to work with Oracle data components in the Data Flow task.  (NOTE: I cannot find the SSIS 2012 equivalent at the moment.  However, Matt Massan’s blog post after PASS Summit 2011 notes more work is being done with Attunity.)  UPDATE: I wrote this prior to a blog post from Matt Massan on support for SSIS 2012 with v2.0 of the Microsoft Connector.  Check out Matt’s update on this: http://blogs.msdn.com/b/mattm/archive/2012/04/04/microsoft-connectors-v2-0-for-oracle-and-teradata-now-available.aspx.

However, this connector does you no good when working with the Execute SQL task.  In SSIS 2008, I use the OLE DB provider from Oracle to create the connection used with Execute SQL Task.  In my work with procedures in my first tip, I used the OLE DB provider with 2012 as well and it worked fine.

SSAS Drivers

When working with SSAS 2008 and, until I know differently, SSAS 2012, I would recommend using the Oracle OLE DB driver from Oracle.  This driver is not the fastest I have seen (third party drivers are marginally faster and the .NET driver is faster as well), but it has provided consistent results for the right price.  Third party drivers will improve the throughput, but not substantially.  The Oracle provided .NET driver is faster as well, but has an unchangeable active query timeout of one hour.  If you have any processing times that exceed this, it will unceremoniously drop the connection.  For these reasons, I have stuck with the OLE DB provider from Oracle which is not necessarily the fastest, but it has been the least painful to work with.

SSRS Drivers

It is with SSRS I have seen mixed results.  Primarily because of the better performance in the Oracle .NET driver.  If you can guarantee that your reports will return their data in under an hour, this seems to be the best option.  However, if you want to manage to a single driver set across all tools, you may find that the management of the OLE DB driver as the only driver makes sense in your organization.

Test, Test, Test

I have given you my experience using the drivers above.  However, you may find value in purchasing a third party driver or you may find a different experience when you implement in your environment.  Be sure to test and understand the implications in maintenance and system cost when choosing different drivers across your solutions.

Recovering SharePoint 2010 with BI on Windows 7

While working with Microsoft BI projects, I am often required to deliver the visualizations through SharePoint.  On my current project, I have a SharePoint 2010 development environment on Windows 7 that has SharePoint and all the SQL Server services running.  Last week I was preparing to demo our progress when the site became unresponsive.  The demo site had the wonderfully descriptive message:  “An unexpected error has occurred.”  So, I attempted to open the management console but that page could not be found (404 error).  I turned on the expanded error messaging by modifying the web.config file and checked the log files.  The errors pointed to the inability to connect to a database.  Upon further investigation I discovered that three of our databases went suspect including the admin and content databases.

At this point, I proceeded to try to restore my full SharePoint backup.  Which works in all situations except for recovering the admin database. Based on the online resources, this is to be expected.  After conferring with the coworkers, I was able to determine that the admin database was unrecoverable and the best bet was a reinstall of SharePoint.  After working through some options I was able to get the site back up and running without recreating the entire solution.  The rest of the blog walks through the recovery scenario and calls out the nuances of having BI components as part of the content.

Prerequisites

First, the SharePoint Farm must be Farm backed up.  I did this through the Central Administration console. 
(http://technet.microsoft.com/en-us/library/ee663490.aspx)

Reinstalling SharePoint

The next step involves uninstalling SharePoint.  I did this by running Setup and choosing the Remove Option.  (The Repair option did not work.) Once I had SharePoint uninstalled, I proceeded to reinstall it.  After SharePoint was installed, I ran the Configuration wizard. This step recreated the Admin database.  Once this was complete, I verified that the starter site and the Central Administration site were operational. 

Recovering the Farm

You can recover the farm from either the Central Administration site or by using PowerShell.  In this case, I used Central Administration as it is easier to select and understand the options. 

I first browsed to the Backup and Restore menu page. From this page, I selected the “Restore from a backup” option under the Farm Backup and Restore header.

NOTE:  In order to run the backup or restore farm functions, the Administration service must be running.  If it is not running, start it in the Windows Services console.

There are three pages or steps to recovering the backup. 

Step 1.  Select the backup to restore.

Step 2.  Select the components to restore.

For my purposes, I selected the Farm option which restores all of the options to the farm from the backup.

Step 3.  Select Restore Options.

The first two selections are the most important.  I selected the “Restore content and configuration” and “Same configuration”.  This uses the same application and site names for the full restore of your site.  The “New configuration” option would have required me to rename all of the applications and services I needed to restore. There were a lot of applications and services to rename which I chose not to do.

When I restored at both the client and on my personal development environments I needed to remove the following services before running the restore:

• Application Registry Service
• Web Analytics Service Application
• User Profile Service Application
• Search Service Application.

When I did not remove these, my restore failed due to duplicate names.  You may run into a similar issue, so removing them before the restore is easiest.  These applications and services should be removed from the Central Administration site.  Once these services are removed, run the restore.

Bringing BI Back Online

At this point, my site would have been operational except I had a Reporting Services webpart on the homepage of my site.  Because of that I saw an error below.

As the error message notes, the issue is with the Reporting Services webpart. The issue is that the full restore did not restore the Reporting Services integration.  The integration needed to be redone manually. 

Once Reporting Services was reintegrated I was able to open the site.  However, neither the Reporting Services or PerformancePoint webparts were working. 

These issues are primarily security related.  To fix the Reporting Services issue, I openend the data source and reset the credentials.  Once reestablished, the report webpart came back online. 

To resolve the PerformancePoint issue, I first went to Central Administration to refresh the Secure Store Service key.  I was able to use my passphrase refresh the key.  (NOTE: if necessary you can create a new key.) Next, I went in to the PerformancePoint Service Application settings and reset the unattended service account. 

Once these steps are complete, the site was fully restored.  While the full backup is extremely useful for site failures it will not recover all of the BI components and as such feels very incomplete. I hope this saves someone some time later.