Microsoft BI: PowerView Data Source Authentication - Debugging Security Issues

When opening a BISM connection file in SharePoint 2013 – assuming you've installed Reporting Services in SharePoint Integrated mode and the Reporting Services add-in for SharePoint – PowerView fires up automatically in the browser and you’re presented with the data field list to start building your report.

On this particular occasion, in PowerView, when we went to add a field to the visualisation, we were presented with this:

The error is far from intuitive: “Cannot create a connection to data source ‘EntityDataSource’". After a little digging around, I fathomed out that this had something to do with authentication through to the data source.

I should mention at this point that we had configured an Execution Account (Central Admin -> Manage Service Applications -> SSRS Svc Application -> Execution Account) for the purpose of testing unattended data refresh and report generation and that the BI Semantic Model (.bism) connector was configured to connect to a SQL Server Analysis Services tabular instance, hosted on a separate SQL Server.

More information about the Execution account here: http://technet.microsoft.com/en-us/library/ms156302.aspx (Native Mode documentation)

Investigating why - a look at the Authentication process flow

In order to understand why we were seeing the error, I set about documenting the authentication and authorisation process through to the data source:

• When a user navigates to a SharePoint site, that person is making an anonymous request for a SharePoint page and SharePoint requests credentials from the user.
• Once credentials have been submitted, SharePoint will seek to validate those credentials against Active Directory. If successful, an NT token is generated and sent back to SharePoint.
• Once validated, SharePoint will query security group membership of the user and create a Claims Based security token using the Security Token Service (STS).
• An authorisation code is generated and sent to the user’s client computer, if the user is authorised to access the page/report, through analysis of the claims, SharePoint will send the page/report.
• The authorisation code is then used for further requests to SharePoint.

Note: In the event of a user browsing to SharePoint with Internet Explorer, domain credentials may be sent automatically and further validated by Active Directory.

The authentication process flow can be a lot more complicated than that but that’s not really within the scope of this post. Full details on the client authentication flow can be found here: http://technet.microsoft.com/en-us/library/cc262350.aspx

All right, so we’re authenticated up to and including PowerView. What happens next?

When a user opens up the BISM connector in his/her browser, the user is authenticated and authorised to view the PowerView interface, following the process detailed above. But that’s only the PowerView interface. What about the connection through to the data?

Now remember, PowerView is a Reporting Services component. Ultimately, when Reporting Services is connecting to a data source (in this case SQL Server Analysis Services Tabular mode), it must present some credentials for authentication or some form of user authorisation.

What’re the options for Authenticating?

Whether Kerberos Delegation is in place or not, the options for specifying Reporting Services data connection credentials are:

• Prompt the user for credentials
• Store credentials (e.g. Secure Store Service)
• Windows Integrated security
• Use no credentials

If no credentials are used, then the report will run in the context of the account that is set to run the Application Pool that hosts the Reporting Services Web Services. In addition, provided the account running the Application Pool has Administrator rights on the data model, it will then proceed to use the EffectiveUserName parameter to pass the name of a Windows Domain account in order for Analysis Services to impersonate that user.

Our Authentication Scenario

On this occasion, I’d mentioned already that we had specified an Execution Account and the documentation for the Execution account states that the Execution account is used to… “Send connection requests over the network for reports that use database authentication, or connect to external report data sources that do not require or use authentication. For more information, see Specify Credential and Connection Information for Report Data Sources in SQL Server Books Online.”

When an Execution Account is used to authenticate against a Tabular instance of Analysis Services, provided it has administrative privileges, it will also utilise the EffectiveUserName parameter.

We made sure that the Execution Account had access to the data source, but we were still receiving the error in PowerView.

So what’s the problem and how do we fix it?

Well… Before it can query the SQL Server, the Execution Account must convert its claims into an NTLM Windows Token. This is so that SQL Server will understand who is trying to gain access to the data (SQL Server does not understand claims). In order to do that, it must call the C2WTS (Claims to Windows Token Service).

The C2WTS can be found in C:\Program Files\Windows Identity Foundation\v3.5\ and has a .config file (c2wtshost.exe.config) that contains a section specifying who is permitted to call the service.

<allowedCallers>
   <clear />
   <add value="WSS_WPG" />
</allowedCallers>

In this  case, only the group SP_WPG was permitted to call the C2WTS.

In most cases, SharePoint Application Pool accounts are automatically added to the SP_WPG group.

Why that’s important? Well, it means that only members of the WSS_WPG group may call the Claims to Windows Token Service.

Aha! So how to solve?

Well, there are a couple of possibilities here:

• Add the Unattended Execution Account to the WSS_WPG group
• Add the Unattended Execution Account to the Claims to Windows Token Service .config file.
• …Or configure the Execution Account to be a user that is already a member of the WSS_WPG

If you consider the privileges granted to the WSS_WPG group (http://technet.microsoft.com/en-us/library/cc678863(v=office.15).aspx), the preferred approach in this case is to add the Unattended Execution Account to the c2wtshost.exe.config file, granting the minimum privileges required for the PowerView report to run using the Execution Account.

Additional Information
Technet documentation for the Execution Account -
http://technet.microsoft.com/en-us/library/ms181156.aspx

TechNet page for the Unattended Execution Account -
http://technet.microsoft.com/en-us/library/ms156302.aspx