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

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]

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:

<Properties>
<Property Name=”RootFinder” Type=”System.String”></Property>
</Properties>
</MethodInstance>

• In “Read Item” section, ensure we have a parameter for “SecurityDescriptor” column:

<TypeDescriptor TypeName=”System.Byte[], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″ IsCollection=”true” Name=”SecurityDescriptor”>
<TypeDescriptors>
<TypeDescriptor TypeName=”System.Byte” Name=”SecurityDescriptorElement” />
</TypeDescriptors>
</TypeDescriptor>

•   In “Read Item” section, for “SpecificFinder” method, ensure mapping for “SecurityDescriptor” with property “WindowsSecurityDescriptorField”

<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.

1. 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);

SecurityIdentifier everyone = new SecurityIdentifier(WellKnownSidType.WorldSid, null);

sd.DiscretionaryAcl.RemoveAccess(AccessControlType.Allow, everyone, unchecked((int)0xffffffffL), InheritanceFlags.None, PropagationFlags.None);

byte[] secDes = new Byte[sd.BinaryLength];

sd.GetBinaryForm(secDes, 0);

return secDes;

}

Thanks

RIdik

2. 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.

3. AD for Security Trimming says:

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.

4. Lokesh sharma says:

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.

5. Paresh Sen says:

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.