Ust Oldfield's Blog

Data Source Permissions and On-Premises Data Gateway: SQL Server and Analysis Services

In Microsoft’s documentation surrounding the On-Premises Data Gateway, the advice on permissions for the account used to authenticate the Data Source in the Power BI Service can be concerning for most, especially DBAs.

In the Analysis Services section of the documentation, the advice is:

The Windows account you enter must have Server Administrator permissions for the instance you are connecting to. If this account’s password is set to expire, users could get a connection error if the password isn’t updated for the data source.

Server Administrator permissions…? What happened to the principle of least-privilege?

In a practical sense, the On-Premises Data Gateway has to deal with two very different implementations of Analysis Services: Multidimensional and Tabular. Each are setup and configured differently from the other, and the nature of their security models are also different. As a one size fits all approach, it works. As we will soon see, the permissions do not have to be set as Server Admin

The SQL section of the documentation, on the other hand, doesn’t actually specify what permissions are required for the Data Source to be established in the Power BI Service.


Exactly what permissions are required for these common data sources, I hear you ask. As data sources are established at a database level, so too are the permissions set.

Data Source

Minimum Permissions Level

SQL Server Database


SSAS Tabular Database

Process database and Read

SSAS Multidimensional Database

Full control (Administrator)

Principle of least-permissions is now restored.

Though there still are the curious incidents of Analysis Services data sources requiring permissions in addition to read. I am unsure, I have my suspicions, and have tried to find out. If you know, please leave a comment below!

Comments (2) -

  • Brent

    1/31/2018 5:25:26 PM | Reply

    I appreciate you sharing the permission level info. We currently use a Gateway for SQL Server databases, authenticating via a UserID with db_datareader authority. Works well for us except we must run multiple Gateways to achieve desired security. Not only do we secure tables based on UserID, but we also have implemented Row Level Security based on UserID. Current Gateway only supports a single UserID per data source. Thus for every unique security profile for my report authors we must run a separate Gateway. Anyone else challenged by this?

    • UstOldfield

      1/31/2018 6:02:04 PM | Reply

      Hi Brent,
      It does sound like a huge challenge to overcome with your current model. Sounds like you're importing the SQL data... Is it possible to use DirectQuery instead, so that the security is handled in the DB rather than in the Gateway; or think about using Analysis Services and Live Connection instead?
      If you have data sensitivity challenges, letting the data source handle the security is often the best way forward, especially in terms of effectiveness and simplicity.