Skip to content

Month: February 2016

Power BI Enterprise Gateway and SSAS – What Account Should You Use?

Published February 23, 2016

With the release of the Power BI Gateway, it is now possible to publish specific data sources for the entire organization. The gateway works in both live connect (data stored on premises) and refresh (cached data stored in the Power BI service) scenarios. A variety of data sources are supported by the gateway including SQL Server Anlalysis Services (SSAS).

SSAS was previously supported by the SSAS connector which has now been deprecated in favour of the Enterprise Gateway. This certainly helps with understanding and complexity, but it does raise one particular concern around data security which you should be aware of.

When a data source is published, credentials are provided that the gateway will use to connect to the data source. All users that use the published data source will connect to it with those credentials. This proxy account will be used for all users and should therefore be a least privilege account – it should have no more access to data than needed. However, there is one data source that notably does not work this way – SSAS.

image

SSAS employs the EffectiveUserName feature to provide fine grained permissions to data in the model. With EffectiveUserName, the proxy account is used only to establish the initial connection to the SSAS server, and all queries are executed with the permissions of the consuming user, allowing the data to be security trimmed.

However, in order to use EffectiveUserName, the proxy account needs to have the highest permission level within Analysis Services – Administrator. This is the exact opposite of the account criteria for all other data sources. What is important is to understand these criteria in order to not unwittingly open up data to the wrong audience. If an admin level account  were used as proxy for a SQL Server data source, potetially sensitive data could be exposed to the wrong users.

In a nutshell, the thing to remember is simply to always use a least privilege account for all data sources except for SSAS, which needs an admin level account.

3 Comments

Power BI and Shark Attacks – Oh My!

Published February 15, 2016

It’s not often that you get to combine interests, but when the Power BI team launched the Publish to Web feature for Power BI reports, I saw just such an opportunity. Quite some time ago, I had played around with the Global Shark Attack File for some Power View demos. One of the problems at the time was that there was no way to keep it automatically refreshed, and there was no real way to publish publicly.

Publish to web removed the second problem, so I doubled down on the first, figuring out a way to automatically download the Excel workbook on a nightly basis from the GSAF site. With that in place, armed with the Personal Data Management Gateway for data refreshes, I set out to do a little data modelling, and report building. The data in the file isn’t in the best shape, so it took a bit of work with both Power Query and DAX to beat it into t shape I wanted it (particularly extracting the species of shark).

However, as seems to be the case with Power BI, most things are possible, and I managed to put together something fairly interesting. I was inspired by a recent article in California Diver magazine, which had been shared around on Facebook. The article claimed that there were zero attacks on SCUBA divers in 2015, which is of course good news. Being an avid diver, that loves the sharks, I’m constantly amazed at the fear these animals generate. Cows kill a lot more people each year than sharks, but I digress.

I was quickly able to see that the figure for 2015 was in fact 1. The attack happened in Brazil on Dec 21, and it was non-fatal. Presumably, the article writer had a Chrstmas deadline. In any event, don’t trust me, check it out for yourself. The report is too wide to embed in this blog post, but I have created a page to host it here:

Global Shark Attacks

It is published with the Power BI Publish to Web feature. Provided that my workstation is up and running, along with connectivity, it will be updated every morning at 6:30 AM Eastern time. If you have any ideas for improvement, please let me know! I am interested to see how well this feature works.

This also seems as good a time as any for one of my favourite diving pictures. The shot below was taken of yours truly by my son while diving with a group of about a dozen Bull Sharks.

IMG_6576

5 Comments