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.

Comments

Anonymous
October 17, 2011
Can you please provide the steps on how or where to deploy the following code? 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; } Thanks RIdik
Anonymous
October 19, 2011
I figured it out. I added an extra column to the table which was exposed to SharePoint. I called that column "SecurityDescriptor" and used the function mentioned above to fill this column. Afterwards, I followed the steps mentioned above to modify my model.xml file. Search with security worked. Thank you for this great post.
Anonymous
December 20, 2012
Can you reply how to use the AD with permissions for testing this Fast Search Security Trimming which is causing a lot of problems for me.
Anonymous
February 20, 2013
in which Method do i need to call this method private Byte[] GetSecurityDescriptor(string domain, string username) ? in read list or read item? and let me know if you have completed code... post some lines.
Anonymous
February 22, 2013
For item level security each item should have it's security descriptor defining specific access. Either generate the same when data is being populated in database or if you are writing a connector use ReadItem.

Share via

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

Comments

Additional resources