PerformancePoint connections to PowerPivot quit working after patching!

I recently patched a Data Warehouse farm (March 2015 CU + several security hotfixes) and users reported that their connections from PerformancePoint to PowerPivot workbooks quit working.  Error messages indicated that the PerformancePoint Unattended Service Account were getting accessed denied.

There were no Failure Audits in the Security Event log.  I validated kerberos settings such as SPNs for everything, web app authentication, AD delegations, etc.  I couldn't find anything.

Involved MS and we found the issue within 45 minutes and reminded me of a Kerberos configuration I'd forgotten about.

PowerPivot Redirector
There's a PowerPivot redirector that lives on each server in the farm, or at least the configuration for one does.  It has its own private web.config file that also has to be set to Negotiate from NTLM to get the PerformancePoint -> PowerPivot connection to work.  According to timestamps the last time I'd updated the web.config was a couple years ago.

The web.config for the redirector lives in <14hive>\ISAPI\PowerPivot

There are two custom bindings that need to be updated to look like below:

<customBinding>
<binding name="RedirectorBinding">
<webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
<httpTransport manualAddressing="true" authenticationScheme="Negotiate" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>
<binding name="RedirectorSecureBinding">
<webMessageEncoding webContentTypeMapperType="Microsoft.AnalysisServices.SharePoint.Integration.Redirector.RawContentTypeMapper, Microsoft.AnalysisServices.SharePoint.Integration" />
<httpsTransport manualAddressing="true" authenticationScheme="Negotiate" transferMode="Streamed" maxReceivedMessageSize="9223372036854775807"/>
</binding>
</customBinding>

By default this is set to NTLM.

After making the change on a server, perform an IISReset.  Make this change on each server in the farm.

EDIT:
thought it would be good to include the file location.

EDIT2:
Given this is in the PowerPivot Redirector, I believe this was probably caused by the SQL Patches that were also applied.  SQLServer 2012 SP2 and CU6 were also applied.

BCS Models - Part 9: Crawl-Time Security

This post is part of an eight nine part series describing the process I have followed to be able create a BCS model capable of indexing well over 10 million items.

Series Index:
BCS Models - Part 1: Target Database
BCS Models - Part 2: Initial BCS External Content Types
BCS Models - Part 3: Crawl Results
BCS Models - Part 4: Bigger Database
BCS Models - Part 5: The Bigger Database
BCS Models - Part 6: How to eat this elephant?
BCS Models - Part 7: Changes to the BCS Model to support segmented crawl
BCS Models - Part 8: Crawl Results 
BCS Models - Part 9: Crawl-Time Security  <-- You are here

In this final post of the series I add the crawl-time security that I mentioned in the very first post.  This feels like an easy step after all the prior work.

SharePoint can perform the security trimming either at crawl time or query time.  With crawl time security trimming a Windows SID is captured for each item being crawled, one time for each item crawled.  With query time security, the back-end system is queried for security information when each and every query request is being processed, while the user is waiting.  I've always chosen to use crawl time security as the security of the items I've dealt with haven't been dynamic to warrant the query-time security.

With Crawl-Time security there are again a couple options.  One is to provide a WindowsSecurityDescriptorField on the finder, or AssocationNavigator, and the other is to provide an additional Method/MethodInstance to return the WindowsSecurityDescriptorField.  

Initially it seems like including the WindowsSecurityDescriptorField on the Finder/AssociationNavigator would be more efficient, however if you do the SpecificFinder is called for every item being crawled.  If the Finder/AssocationNavigator provides all the data needed to populate the entity, the call the SpecificFinder strictly speaking isn't needed, except for one detail.  The Windows security construct can easily grow to consume all the item cache, so BCS just doesn't use it, even if specified.  I've run multiple test iterations to see if I could come up with a combination of Properties, etc. that would enable this to work better but have failed.

The usually most efficient solution is to create a new Method with a BinarySecurityDescriptorAccessor.  The Search Gatherer will use this method if present.  This is the method I use.  Even better, my use cases haven't required unique SecurityDescriptors to be returned per row, but rather per Entity.  To simulate this I'm going to inject a new requirement for the model that was completed in BCS Models - Part 7: Changes to the BCS Model to support segmented crawl.  I'm going to say that the business group has decided that the Response Entity is to be secured with the AD group named 'SuperSecretStuff'.

I need just a few things to continue:

1. A hexadecimal representation of the SecurityDescriptor
2. A stored procedure that takes a RequestID and returns the SID for the requested ID.
3. The new BCS Method and MethodInstance.

Getting the Windows SID

The SecurityDescriptor isn't too hard to get.  I don't recall where I got this powershell, I may have created it based upon some c# example.  It creates a security descriptor, removes all rights for Everyone and adds a specific grant for the requested user or group.  The resulting security descriptor is then dumped out as a hex value

 param($domain, $username)

function Convert-ByteArrayToHexString

{

[CmdletBinding()] Param (

 [Parameter(Mandatory = $True, ValueFromPipeline = $True)] [System.Byte[]] $ByteArray,

 [Parameter()] [Int] $Width = 10,

 [Parameter()] [String] $Delimiter = ",0x",

 [Parameter()] [String] $Prepend = "",

 [Parameter()] [Switch] $AddQuotes )

if ($Width -lt 1) { $Width = 1 }

 if ($ByteArray.Length -eq 0) { Return }

 $FirstDelimiter = $Delimiter -Replace "^[\,\\:\t]",""

 $From = 0

 $To = $Width - 1

 Do

 {

 $String = [System.BitConverter]::ToString($ByteArray[$From..$To])

 $String = $FirstDelimiter + ($String -replace "\-",$Delimiter)

 if ($AddQuotes) { $String = '"' + $String + '"' }

 if ($Prepend -ne "") { $String = $Prepend + $String }

 $String

 $From += $Width

 $To += $Width

 } While ($From -lt $ByteArray.Length)

}

$acct = new-object System.Security.Principal.NTAccount($domain, $username)

[System.Security.Principal.SecurityIdentifier]$sid = $acct.Translate([System.Security.Principal.SecurityIdentifier])

$controlFlagNone = [System.Security.AccessControl.ControlFlags]::None

$sd = new-object System.Security.AccessControl.CommonSecurityDescriptor($false, $false, $controlFlagNone,$sid,$nil,$nil,$nil)

#define some enums...

$worldSID = [System.Security.Principal.WellKnownSidType]::WorldSid

$accessAllow = [System.Security.AccessControl.AccessControlType]::Allow

$inheritanceNone = [System.Security.AccessControl.InheritanceFlags]::None

$propagationNone = [System.Security.AccessControl.PropagationFlags]::None

#get the wellknown sid for everyone

$everyone = new-object System.Security.Principal.SecurityIdentifier($worldSID,$nil)

#Deny access to all users...

$sd.DiscretionaryAcl.RemoveAccess($accessAllow,$everyone,-1, $inheritanceNone,$propagationNone)

#Grant full access to the specified user/group.

$sd.DiscretionaryAcl.AddAccess($accessAllow,$sid,-1, $inheritanceNone,$propagationNone)

#Now get the binary representation of it and return that

$secDes = new-object byte[] $sd.BinaryLength

$sd.GetBinaryForm($secDes,0)

Convert-ByteArrayToHexString -bytearray $secDes -width $sd.BinaryLength -delimiter "" -prepend "0x"

This emits the security descriptor to the powershell window.  

Security Stored Procedure

The stored procedure for my example is really simple.  Regardless of what ID is passed it will always return the same value and name the column SecurityDescriptor.

CREATE PROCEDURE usp_getPostSecurity @postID integer as 

-- the binary representation of the SuperSecretStuff group's SID

select 0x0100048014000000000000000000000030000000010500000000000515000000DAC6A38FD11C9E4A6C3101385B04000002002C000100000000002400FFFFFFFF010500000000000515000000DAC6A38FD11C9E4A6C3101385B040000 as SecurityDescriptor

GO

grant execute on usp_getPostSecurity to spSearchCrawl

go

With a BCS model based upon SQL Server, we cannot reference a .net object to calculate the security descriptor on the fly.  If you truly need to implement item level security you may be best served by adding the security descriptor to the underlying data and pre-calculate it.

New BCS Method

This is pretty straightforward by this time too.  I generally take a SpecificFinder and clone it and then twist it to my needs.

Here's my end result:

<Method IsStatic="false" Name="usp_getPostSecurity Response">

  <Properties>

    <Property Name="BackEndObject" Type="System.String">usp_getPostSecurity</Property>

    <Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine</Property>

    <Property Name="RdbCommandText" Type="System.String">usp_getPostSecurity</Property>

    <Property Name="RdbCommandType" 

      Type="System.Data.CommandType, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

        StoredProcedure

    </Property>

    <Property Name="Schema" Type="System.String">dbo</Property>

  </Properties>

  <Parameters>

    <Parameter Direction="In" Name="@postID">

      <TypeDescriptor TypeName="System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" IdentifierName="ID" Name="@postID" />

    </Parameter>

    <Parameter Direction="Return" Name="usp_getPostSecurity Return">

      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="usp_getPostSecurity DR">

        <TypeDescriptors>

          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="usp_getPostSecurityElement">

            <TypeDescriptors>

              <TypeDescriptor Name="SecurityDescriptor" TypeName="System.Byte[]" IsCollection="true" ReadOnly="true"/>

            </TypeDescriptors>

          </TypeDescriptor>

        </TypeDescriptors>

      </TypeDescriptor>

    </Parameter>

  </Parameters>

  <MethodInstances>

    <MethodInstance Type="BinarySecurityDescriptorAccessor" ReturnParameterName="usp_getPostSecurity Return" ReturnTypeDescriptorPath="usp_getPostSecurity DR[0].SecurityDescriptor"  Name="usp_getPostSecurity Response Instance">

      <Properties>

        <Property Name="WindowsSecurityDescriptorField" Type="System.String">SecurityDescriptor</Property>

      </Properties>

    </MethodInstance>

  </MethodInstances>

</Method>

While I don't plan on implementing this on other Entities, I named the stored procedure fairly generic and I like to incorporate the stored procedure name in the method name. Here I added the Entity name to the method name so it would be more obvious in the ULS.

A significant difference with this MethodInstance is that it's returning a single value to the caller instead of a DateReader.  Check the highlighted section that makes that happen.

Crawl Time

The proof is in the crawl.  So what's this look like in the ULSViewer?  As is my custom when dealing with Search Crawls over BCS, I filter by event id c73i and see this:

We see the usual methods executing as always followed by masses of calls to the new MethodInstance.  Each item won't be considered 'crawled' in the SharePoint Search Log until the item-level security has been crawled, same as any other link.

Search Time

I created two new users, Joe and Ralph, and made Joe a member of the SuperSecretStuff group.  I did a search query for jQuery and here's the results:

If you click to expand the picture I also show the AD members of the SuperSecretStuff AD group.

Crawl-time security is working.  To ensure that the users have a good experience however, ensure that proper security groups are granted execute rights on the proper BCS entities so they don't see screens like

but if you're still here you probably know this already.  The rights granted on the BCS Entities within BCS probably need to align with whatever crawl-time security you've configured.  It doesn't help much to trim the search results if the user can manipulate the URL on the profile page or get a link from a friend and see the data that was crawled.  Also it's nice to use security or audiences to hide the web parts that will continue to throw this error when they get to one of these they shouldn't.