Power View, Tabular mode databases, SharePoint and Kerberos

yes, the word that any BI or IT pro dreads: Kerberos .. Imagine you want to run Power View in a SharePoint farm on top of a SSAS database running in Tabular mode. You probably want to use security so you can secure the data by the user that is actually running the report using the AS security features.

But most likely you won’t run your AS instance on a machine that is inside of the SharePoint farm. When all of your machines are inside the same SharePoint farm, SharePoint will take care of passing the credentials between the machines. But as soon as one of the machines lives outside of the farm you have to setup Kerberos to make sure the security is correctly passed on between machines. Configuring Kerberos is a lot of (hard) work if Kerberos is not already set up correctly in your organisation.

But there is some good news on this with SQL server 2012. In SQL Server 2012 we introduce the BISM file that allows us to start a Power View report based on the connection information in this BISM file. Whenever a connection is made from Power View to the Tabular database it tries to connect using the credentials of the user that is executing the Power View report. If Kerberos is not configured this connection will fail regardless of him having access.

But there is a fall back scenario, when using the BISM connection to connect to a tabular database ADOMD will give it another try using the execution account the Reporting Services app server is running under and then switch to the actual user who initiated the connection. All you have to do is add the execution account the Reporting Services app server is running under to the administrators of the AS instance and this connection will succeed even without Kerberos is set up. Admin connections are always allowed cross machines, under the covers ADODM uses the effectiveusername connection string parameter to switch to the actual user initiating the connection after connection is being made.

This post is dedicated to Marc Valk my esteemed former colleague with whom we battle the Kerberos beast several times at my previous job :).

  • The Kerberos beast: best kept in a cage 🙂

  • Kaisa

    Thanks for the info on the BISM file. But how good is it really to have the RS execution account be an *administrator* on the AS instance?

    • Kasper de Jonge

      Hi Kaisa,

      Good question. If you created a separate account for RS and give that access to AS I don’t see anything wrong very dangerous with that. It is an additional security risk, if you are not comfortable with that you can use Kerberos to set up security.

      Kasper

  • Enzo

    Kasper

    Great news indeed. Would this work with Form Based Authentication (FBA)??

    I would be even happier if I could use PowerView in internet scenarios with FBA without using Active Directory.

  • Greg E

    After installing and configuring Kerberos on SP 2010, maybe future releases will be easier.
    My boss keeps thinking along the lines of you just click install and you are done.
    Just starting to take a look at PowerPivot. A link or 2 to something to get me started with how to use the PP server (not just Excel with the PP add in) would be appreciated.
    Greg E

  • Greg E

    I just looked at the tabs and see many links to get me started.
    Greg e

  • Good question. If you created a separate account for RS and give that access to AS I don’t see anything wrong very dangerous with that. It is an additional security risk, if you are not comfortable with that you can use Kerberos to set up security.

  • Bill

    Hi Kasper,

    I’m setting up a lab environment and have sharepoint and SSAS tabular running on separate boxes. I’d like to avoid setting up kerberos but I’m having trouble connecting to the Tabular database via Power View.

    I’d like to make sure that a connection is made on the second try, but I’m not sure what the “execution account the Reporting Services app server is running under”…

    is it the account set under Application Management -> Manage Service Applications -> SQL Server Reporting Services -> Execution Account

    or is it the account set under Security -> Configure Service Accounts -> Service Application Pool – SQL Server Reporting Services Service Application

    or is it some other account?

  • Bill

    @Bill
    a few minutes after posting the above, i created a “Report Data Source” document in the data connections library. I set the type to “BI Semantic Model for Power View” and stored the windows credentials and was able to successfully create a power view report based off of this new connection.

    Before, I was trying to create a “BI Semantic Model Connection” document in the data connections library. For this method, there is no option to specify stored credentials.

  • How do you create a BISM file without installing power pivot on Sharepoint?
    I tried a windows connection in a connection file set as “BI Semantic Model for Power View” ,with the execution account being an administrator of the SSAS instance, but it does not connect.
    Only store credentials of a {domain}{user} with saved password and the “as windows account” checked, can make the connection (with or without kerberos)
    Will it work if I also install power pivot and use new BISM file?

  • I am still not able to connect to tabular Model after following these Steps

    1. I have added the user runing under the Service Application Pool of Sql Server reporting Services Service of share point to the administrator list of Analsysis services Tabular Model.

    2.Created a BISM Connection pointing to Analsysis Services Tabular Model.

    3. When i am opening the connection in power View, its giving me UnknownUserName or BadPassword.

    Kindly help me .

    • admin

      Run profiler on your AS instance and see what is happening, does the request come through? What are the credentials it is sending ?

  • Gevorg Tadevosyan

    Hi Kasper, thanks for the ports.

    We have BI Farm based on SharePoint 2013.
    We have created BISM connection file in doc library in order to connect SSAS database running in Tabular mode. Reporting service account has administrator rights on AS.
    Our goal is to create reports based on bism connection and upload them to the portal, to have them working with data refresh.
    From Excel file we are gathering data from AS using bism file. The connection is working as expected only from servers which are connected to the farm. From any machine outside the farm we are getting an error ((
    Seems there is an authorization issue.
    I have tried to use execution account for reporting service application, still the same error.

    Could you advice smth ?