Office365 users integration with SSAS Tabular for security

[Anonymous]

Hi all,

My client users are currently on Office365 subscription. We are implementing a PowerBI solution for them. They do not have any Active Directory server to store their credentials.

Now there's a gap on the security. We need to setup the security matrix to define who can and cannot see what kind of content in the tabular data model. Basically, a role based security. 

Anyone has any idea or suggestion on how we can implement this?

Thanks and much appreciated.

[Bjoern]

@Anonymous

The role-based security has to be activated in the backend (in the source itself) and the users would have to use the database authorization instead of the windows authentication, if I am not mistaken.

This seems hard to replicate, as in Power BI you are using personal accounts tied to your personal mailadress/id within the organisation.

How do they use PowerBI right now, if they do not have an Active Directory? 

[Anonymous]

@Bjoern
Hi, thanks for the reply.

They are using their own organization O365 account as the login to PowerBI. They are integrated right?

I don't understand the part "role based security has to be activated in the back end". U mean Azure SQL DB or SSAS Tabular?

Btw, their data source is Azure SQL DB. We extract and load data into PowerBI/PowerPivot data model.

[Bjoern]

@Anonymous

I have not tested the integration of O365 and the AD itself, but usually, if you subscribe with the same mailadress, i would assume that their is some kind of integration. Have you tried to search for the users when enabling the row-level security? How do you logon to your servers? With the localhost\xxx users? 😮

Regarding Azure Sequel DB / Backend Security: You have to enable the row-level based security in the SQL DB itself. Power BI does not (as far as I know) offer any additional security layer for the database. The user authenticates with his O365-account towards Power BI and then authenticates against the source.

One remark from my side: If you talk about SSAS Tabular I would assume that you use the SSAS Tabular Server (via live connect) and not an integration of Azure SQL DB (live connect / data import), as you had written. For both Row-level based security should be possible.

Sorry that I can not confirm your approach, but my knowledge of O365<>AD is limited. 

[Anonymous]

@Bjoern

Actually we are currently not using SSAS Tabular yet. Our dataset model source is from Azure SQL DB only. We are exploring options on how to implement the row level/role based security

I'm very curious how do my data model authenticate using users O365 account without a SSAS server.

Btw, many thanks for ur quick response.

[Bjoern]

@Anonymous Row-level Security also works directly in Azure Sequel DB, you do not need an SSAS Tabular for that. 

https://msdn.microsoft.com/en-us/library/dn765131.aspx

If this would be the only reason for SSAS Tabular, I would try to stress your pro- and con-arguments a bit more. SSAS Tabular hardware can be costly (lots of cores, lots of RAM). It might be cheaper to use the PBI WebService PaaS (=import data, model in a file, upload to Power BI, establish gateway => as soon as live connect works better [measures, calc. columns] => rebuild model live connect)

[Anonymous]

@Bjoern

Understand that there's RLS in Azure SQL DB, however how do I authenticate against O365 users? Do you mean by creating multiple user login in Azure SQL and define each login user's RLS, then when creating dataset, use the login user according to security cases?

Appreciate your helps. 🙂

[jldaley86]

@Anonymous did you manage to solve this as we're trying to accomplish the same task?

Thanks! 🙂

[Anonymous]

Hi @jldaley86,

Nope. We suggested something else to the client.

However, I have an idea on how to implement this but yet to try it out myself yet.

Firstly, understand this, in order for SSAS role based security to work, it will require to authenticate the users in Active Directory. So, a sync between Active Directory and the Office365 is needed. If client does not have AD server, you would probably need to advise them to get a cheap server, or install the AD into the MS SQL Server machine (or Azure VM if all cloud based solution). Use the Azure Active Directory Sync to sync the O365 users with the local machine or Azure VM AD machine.

Once it's sync, the AD will know how to link the O365 users. In SSAS role based security, define the role security accordingly to the model. Then install and configure the Enterprise Gateway to allow PowerBI to authenticate with the SSAS model. 

When an O365 login and access the SSAS model through PowerBI, the SSAS will authenticate with the AD by checking the O365 email account to determine it's user and it's role based security in SSAS.

I'm not sure whether this might work or not. Still yet to try it out. Hopefully you are able to try it out and let me know if it works. 🙂