Wednesday, 7 May 2014

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: (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:

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.
   <clear />
   <add value="WSS_WPG" />
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 (, 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 -
TechNet page for the Unattended Execution Account -

Wednesday, 2 April 2014

PowerView Issues following SQL 2012 SP1 CU9

Not so long ago, I helped to build a SharePoint 2013 farm for some testing and more recently, we (some colleagues and I) started to plan how we'd upgrade to SharePoint 2013 SP1 and SQL Server 2012 SP1 CU9.

The farm consists of two servers:
  1. server01 - SharePoint 2013 Web Front End and Application Server 
  2. server02 - SQL Server 2012, hosts config and content databases; other services include Database Services, SSAS, SSAS Tabluar etc. 
Unfortunately I didn't have the time to commit to installing the updates myself, but here's roughly what happened, according to the install log:
  1. server01
    1. SharePoint 2013 SP1 was installed
    2. Some post update cleanup tasks on server01; Disk cleanup, log trimming etc.
    3. Reporting Services Add-In for SharePoint installed (2012_SP1_RSShrPnt_CU9_2931078_11_0_3412_x64; rsSharePoint.msi) 
  2. server02
    1. SQL Server 2012 SP1 CU9 installed (SQLServer2012_SP1_CU9_2931078_11_0_3412_x64)
SQL Server 2012 SP1 CU9:

Once the updates were applied, a colleague committed to some testing. We were looking to be sure that some of the more complex Business Intelligence architectural elements were still operating as we'd expect them to.

That was not the case:
  • PowerView data retrieval failed
  • PowerPivot rendering failed
  • PowerPivot / PowerView previews were not working in the Carousel or Gallery mode
In order to determine what the issue was, I set about tackling these one by one - divide and
conquer style - starting with the PowerView data retrieval.

The Issue

In a document library, there's a .bism file which has been configured to connect to the AdventureWorks tabular database on an instance of Analysis Services (SSAS) on server02. When you click on the .bism file, a PowerView canvas opens and allows navigation of the data model. That works fine.

What doesn't work fine is attempting to add dimension columns to the canvas. This error would appear:
Full details of the error:

System.Exception: Error: Invalid token in RPDS stream. StringTable token: 0x12.
at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReader.ReadStringTable(Int64 offset, RPLContext context) at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReader.ReadVersionedReport(RPLReport report, RPLContext context)
at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReader.ReadReport(RPLReport report, RPLContext context) at Microsoft.ReportingServices.Rendering.RPDSProcessing.RPLReport..ctor(BinaryReader reader) at Microsoft.Reporting.AdHoc.DataManagement.Internal.DataManager.PopulateRimWithRplData(Stream stream, HashSet`1 itemsToNotParse) at Microsoft.Reporting.AdHoc.DataManagement.Internal.DataManager.HandleRenderEditResponse(IAsyncEnumeratorCallback aec)


When trying to figure out what was wrong, I did the usual:
  • Check Event Logs - Nothing unusual here. IIS WAMREG Local Activation issue showed up. I fixed this to eliminate there being any possibility of it having an impact on this situation. (guide:
  • Checking of SharePoint ULS logs - SharePoint appeared to be calling the Reporting Services endpoint just fine. Although there was an exception at the time of data retrieval:

    Error occurred while canceling the command for DataSet 'Tablix1DataSet'. Details: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Connect() at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.CancelCommand(AdomdConnection originalConnection) at Microsoft.ReportingServices.DataExtensions.CommandWrapper.Cancel() at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.HandleImpersonation(IProcessingDataSource dataSource, DataSourceInfo dataSourceInfo, String datasetName, IDbConnection connection, Action afterImpersonationAction) at Microsoft.ReportingServices.ReportProcessing.CommandWrappedForCancel.Cancel() at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.CancelCommand() bb43839c-cec4-50c6-4146-1d1167b52b19

    As it was different to the original issue, I noted it down and progressed with the original error. 
  • Run SQL Profiler - I captured the communication between PowerView and the SQL Server SSAS Tabular instance. I even copied the query and went and executed it separately to be sure there was no issue. Again, all fine:
  • Further head scratching...

So what next, what could I be missing?

After some more looking around and searching online, it got me thinking about whether all of the SQL Server 2012 Shared Components on the SharePoint server were updated or not.

Then a light-bulb moment: didn't the installation report say that that rsSharePoint.msi add in was installed/updated on the SharePoint server? Sure... But what about the other SQL Server Shared Components?

Sure enough, when I ran the SQL Server 2012 SP1 CU9 update package on the SharePoint server, it reported that the 'Upgrade Status' of all of the components was 'Not Installed'.


I noted down the patch level, hit next a few times and let the update package do it's thing.

Once complete, I ran the package again and this time the 'Upgrade Status' reported 'Installed' for the PowerPivot and the Shared Features. The Patch Level had also changed from 11.1.3128.0 to 11.1.3412.0.
I also went and tested the issues that we'd previously experienced - most had been resolved. One or two were still present and waiting on timer jobs to execute in order to fix.

Why did you run the SQL Server 2012 SP1 CU9 update on a SharePoint server?

It's very simple: there are SQL Server Shared Features that must be upgraded too. It's not enough to install only the Reporting Services Add-In for SharePoint.

In short, in this CU9 Scenario, SQL Server patching that should be applied to SharePoint 2013 is as follows:
  • SP Web Front Ends
    • 2012_SPx_RSShrPnt_CUxx_KBxxxxx_11_00_xxxx_arch
    • Applies only to WFE Servers that wish to integrate with Reporting Services (adds pages, features and content types) 
  • SP App Servers
    • SQLServer2012_SPx_CUxx_kbxxxxxx_11_00_xxxx_Arch
    • Applies only to servers running PowerPivot for SharePoint and any additional Shareds Features such as Reporting Services in SharePoint Mode 
  • SP Web Front End & App Server
    • Install/update all that would apply; in this case, both of the above packages applied.
  • SQL Server - SQL Server Software Update Package
    • SQLServer2012_SPx_CUxx_kbxxxxxx_11_00_xxxx_Arch
    • ...and any other additional CU update package that applies to the services you have installed.
SQL Server Package Naming Convention:

The message here is simple.... The landscape has become rather complex, what with the many interfaces between SharePoint and SQL Server now. So make sure you're updating all of the SQL Server Shared Features as well as core SQL Services!

Thursday, 20 March 2014

Take a photograph, make it searchable

The digital workplace has had the ability to photograph a Whiteboard for some time, whether for personal notes or to share with others. There are also mobile apps dedicated to this - even Google Drive has a 'Scan' feature with OCR text recognition, allowing the text to be indexed and thus discoverable.

Now there's something from Microsoft. It's called Office Lens. Though unfortunately only in the Windows Phone Store at the moment. Let's hope they release also an Android and iOS version.

Wednesday, 27 March 2013

SharePoint Configuration Guidance for 21 CFR Part 11 Compliance

In April 2012, Microsoft published a White Paper titled SharePoint Configuration Guidance for 21 CFR Part 11 Compliance – I urge any SharePoint person involved in with Computer Systems Validation (CSV) subject to read it.

It’s rudimentary and unlikely that you will use exactly the use cases mentioned, but the concepts are there. It mentions one or two server technologies which may not be available in tightly governed organisations but I think that the real value is where it goes on to detail what can be done with Microsoft Office 2010 and SharePoint 2010 out-of-the-box.
Whether you agree or disagree, it then goes on to explain how exactly the Office clients and the SharePoint server platform meet aspects of 21 CFR Part 11 Compliance.
Direct link to the white paper:

Wednesday, 4 July 2012

SharePoint ECMA libs in non-master page

If you have a SharePoint .aspx page that has no master page content or containers and you want to include the SharePoint ECMA client object model libs, SharePoint is very particular about how you do that.

As we know, including the libraries is as simple as including the following line of markup:

<SharePoint:ScriptLink runat="server" ID="sl" Name="SP.js" OnDemand="false" Localizable="false" />

Note: the OnDemand attribute is set to false here to ensure that the script files are loaded immediately and so that we don't have to over-complicate with ExecuteOrDelayUntilScriptLoaded()

The key thing to remember with this piece of markup is that it MUST appear after the form element. So it will look a little like this:

<form id="form1" runat="server">
<SharePoint:ScriptLink runat="server" ID="sl" Name="SP.js" OnDemand="false" Localizable="false" />

I had originally tried to put this in the <head> ... </head> element and was left head-scratching as to why it failed to include the scripts.

Friday, 30 March 2012

Configuring SQL Server Denali Reporting Services SharePoint 2010Integration

Great guide pointing out that if you should happen to install SQL Server Reporting Services 2012 before installing SharePoint, you must then let SharePoint 2010 know it's there and install the service.

Thanks Dan!

Friday, 14 January 2011

XSLTListViewWebPart: Using CAML Query Parameters

After much searching around followed by a fair bit of trial and error, I figured out how to make use of Data View Parameters in a CAML Query in an XSLTListViewWebPart. Bear in mind I am configuring this using SharePoint designer.
So I have a list web part on a page, I open up that page using SharePoint Designer 2010 and begin editing under the hood. Once you've added a Data View Parameter you're then able to embed it as a parameter into your CAML query like so (Parameters in Bold):

     <OrderBy> … </OrderBy>
           <FieldRef Name="FileRef" />
           <Value Type="Text">{year}/{month}</Value>

Despite being so easy to implement, not once did I find it documented anywhere. I've labelled this post as I would have searched for it in the hope it helps out others looking for the same thing.