How to enable security trimming on search result for external data indexed in SharePoint 2010: SQL connector

Article
02/22/2011

In continuation to my last post I'll explain how to enable security trimming on search results in SharePoint 2010 for external database. We need to have a BDC model file created with suitable operations for a table that is being indexed in SharePoint. More details on creation of BDC model file can be found here:

Create an External Content Type Based on a SQL Server Table[link]
Business Data Connectivity Models[link]

At this point we assume that we have the below items in place:

External data table with all relevant columns that need to be indexed
BDC model file with List\item operations defined in it for the above table

In order to have item level security implemented and search result trimming based on security, we need to have the security descriptor generated for each record and present in the table for each row. If the external system is already not having this data available, we can generate the same using:

 private Byte[] GetSecurityDescriptor(string domain, string username) {
    NTAccount acc = new NTAccount(domain, username);    SecurityIdentifier sid = (SecurityIdentifier)acc.Translate(typeof(SecurityIdentifier));
   CommonSecurityDescriptor sd = new CommonSecurityDescriptor(false, false, ControlFlags.None,sid, null, null, null);
   sd.SetDiscretionaryAclProtection(true, false);

    //Deny access to all users.
   SecurityIdentifier everyone = new SecurityIdentifier(WellKnownSidType.WorldSid, null);
   sd.DiscretionaryAcl.RemoveAccess(AccessControlType.Allow, everyone, unchecked((int)0xffffffffL), InheritanceFlags.None, PropagationFlags.None);

    //Grant full access to a specified user.
   sd.DiscretionaryAcl.AddAccess(AccessControlType.Allow, sid, unchecked((int)0xffffffffL), InheritanceFlags.None, PropagationFlags.None);
    byte[] secDes = new Byte[sd.BinaryLength];
   sd.GetBinaryForm(secDes, 0);

    return secDes;
}

  
 Follow this msdn link for details.

This can be used for a user or group or can be modified as required for a set of users\groups that can be mapped to windows users\groups. This will generate a byte stream and we need to store it in the table for each row as a varbinary value.

Once we set the table data appropriately, we need to map this new column in our BDC model file. Assume the column containing security details is called "SecurityDescriptor". we will use this name here forward for mapping.

In the BDC model file do the below changes:

In "Read List" section, for Finder method ensure the properties as shown:

In "Read Item" section, ensure we have a parameter for "SecurityDescriptor" column:

In "Read Item" section, for "SpecificFinder" method, ensure mapping for "SecurityDescriptor" with property "WindowsSecurityDescriptorField"

<MethodInstance Type="SpecificFinder" ReturnParameterName="TableRead Item" ReturnTypeDescriptorPath="TableRead Item[0]" Default="true" Name="TableRead Item" DefaultDisplayName="Read Item Table">
<Properties>
<Property Name="LastDesignedOfficeItemType" Type="System.String">None</Property>
<Property Name="WindowsSecurityDescriptorField" Type="System.String">SecurityDescriptor</Property>
</Properties>
</MethodInstance>

After importing the BDC model file, run a full crawl on the content source. Now search results should show the security trimmed data.

How to enable security trimming on search result for external data indexed in SharePoint 2010: SQL connector

Additional resources