No Code secure search with FAST Search for SharePoint 2010

Introduction

Implementing secure database search on the item level with FastSearch for SharePoint 2010 (FS4SP) has been a somewhat confusing journey for me.

At the start I had some questions:

• Can I use BCS?
• Must I write .NET assembly connector?
• Can I use FAST’s jdbcconnector?

The short answer is yes, no and yes. The long answer will follow below.

Secure search

To support secure search at the item level each document will have to be associated with an ACL, describing who has access to the document. At query time the user's group memberships will be looked up against Active Directory and added to the query to match against the ACLs stored in the index. This way only documents the user has access to will be shown in the search result. Out the box, FS4SP will only support Active Directory as the user directory.

Enabling secure search on database content is therefore only a matter of adding a security descriptor to each row, describing who should be able to read this row. This sounds easy enough, but the question is still; what should this ACL look like, and how do I put it on the correct place in my index.

Using BCS

As an example I've used the AdventureWorksDW database and the table DimProduct. The table has been altered to include a column named SecurityDescriptor of type varbinary(MAX). I started with SharePoint Designer (SPD) and created my simple model with ReadList and ReadItem methods. This model was exported from SPD to a BDCM (XML) file. The model doesn’t include any information about where to fetch the security descriptor, it is however described on various places on the internet how to enable security on the item level in the model. MSDN has one example. Note that you only need to update the SpecificFinder method, no BinarySecurityDescriptorAccessor is needed.

I'll recap the steps below, but please check MSDN for the correct placement in your bdcm (or see the attached example):

1. Add a new TypeDescriptor in the SpecificFinder, this will read a bytearray from the column named SecurityDescriptor:

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

2. Add a reference to the the SecurityDescriptor in the property WindowsSecurityDescriptorField (still on SpecificFinder)

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

3. Make sure to remove the UseClientCachingForSearch

 <Property Name="UseClientCachingForSearch" Type="System.String"></Property>

4. Also make sure you have "ShowInSearchUI" (should already be there)

Now, import your model using Central Admin, and once you have your SecurityDescriptors in place you should be ready to crawl.

Creating the ACL

Finding the correct format of the Security Descriptor (ACL) and how to store it in SQL server was the most difficult part digging out. The Security Descriptor should be stored in binary format in the column referenced in the BDC model. On MSDN there is a C# code snippet that retrieves a sample windows Security Descriptor for a specific user/group in binary as a byte array. I've extended the snippet to update my SecurityDescriptor column (I’m using the same ACL for all rows):

 static void Main(string[] args)
{
    Program sdl = new Program();
    byte[] b = sdl.GetSecurityDescriptor("Contoso", "SearchUsers");

    SqlConnection cn = new SqlConnection();
    cn.ConnectionString = "Data Source=demo2010a;Integrated Security=True";
    cn.Open();

    SqlCommand cmd = new SqlCommand("UPDATE [AdventureWorksDW].[dbo].[DimProduct] SET [SecurityDescriptor] = @SecurityDescriptor", cn);
    cmd.Parameters.Add("@SecurityDescriptor", System.Data.SqlDbType.VarBinary);
    cmd.Parameters["@SecurityDescriptor"].Value = b;

    cmd.ExecuteNonQuery();

    cn.Close();

    Console.WriteLine(b.ToString());
}

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

    Console.WriteLine(sd.GetSddlForm(AccessControlSections.All));

    byte[] secDes = new Byte[sd.BinaryLength];
    sd.GetBinaryForm(secDes, 0); 
    return secDes;
}

Once you've run the code snippet above you should be ready crawl, and any users in the SearchUsers groups should be able to see the secured rows in the search results.

JDBC format

The BCS crawler will submit the SecurityDescriptor in string format (SDDL) into the docaclms property. During content processing the docaclms property will be converted to an internal format which consists of the SID's of each user/group which allowed or denied access to the document. Each SID is base32 encoded and put into the property docacl. The docacl property is the one which is actually filtered against at query time. When using the jdbc connector one has the possibility to write directly to the docacl property. To create a correct string use the Get-FASTSearchSecurityEncodedSid:

PS C:\FASTSearch\bin> Get-FASTSearchSecurityEncodedSid -User Contoso\danj

S-1-5-21-331390976-2650875657-2422772959-1705

aecqaaaaaaaakfiaaaaabigacmesoam525kgrefjayaaa

PS C:\FASTSearch\bin> Get-FASTSearchSecurityEncodedSid -User Contoso\SearchUsers

S-1-5-21-331390976-2650875657-2422772959-2004

aecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa

The cmdlet will output both the SID and the encoded SID. To create a proper acl prefix each SID with "win" (allow) or "9win"  (deny). To allow CONTOSO\danj and CONTOSO\SearchUsers, the docacl would be

winaecqaaaaaaaakfiaaaaabigacmesoam525kgrefjayaaa; winaecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa

The ACL above could either be put into the SQL table as showed below, or it can it can be hardcoded in the SQL query of the jdbc connector configuration:

SELECT [ProductKey], 'winaecqaaaaaaaakfiaaaaabigacmesoam525kgrefjayaaa; winaecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa' as docacl

See the attached jdbc connector configuration for a complete example.

Database sample

The screenshot below shows a couple of sample rows. docaclwin contains the base32 encoded SID values, and SecurityDescriptor contains the binary Security Descriptor.

SampleFiles.zip

Comments

• Anonymous
  December 11, 2012
  I like to try it fast for the fast search mainly without the code. I can get the SID from the user details in the AD. Assume if i am getting those SID and updating three rows of the sql table with three different SIDs.  In that case how can i have the access and deny ...is that ok to add it manually in the bdcm. What is encoded SID is that required in the DB?

• Anonymous
  December 11, 2012
  You can't store the SID in the table, you need to store the binary security descriptor as shown with the code. If you use the code sample you can modify it to add deny clauses as well.

• Anonymous
  December 12, 2012
  The comment has been removed

• Anonymous
  December 16, 2012
  Any idea on the error i have posted.

• Anonymous
  December 16, 2012
  What is the jdbc_security.xml in the SampleFiles.zip? Do i need to have docaclwin column in the table? If yes how is that conversion done?

• Anonymous
  December 16, 2012
  Seems like the user running your BCS doesn't have access to your database. The docacl-format for the JDBC connector is described in the article...

• Anonymous
  December 16, 2012
  Do i need to have it as a sql table column name?  If yes can you give the XML code for that access?

• Anonymous
  March 02, 2013
  Hi Murad, Is it possible to convert two user details in binaryformat and store it in the securitydescriptor field with , or senicolon separation? Please advise.

• Anonymous
  March 03, 2013
  If you look at the .NET code for creating the security descriptor you see that you can use the AddAccess method multiple times to give access to multiple users/groups. You cannot use semi colon separation in a binary column in the database. If you're using the JDBC connector you can add multiple ACLs (in the base32 encoded SID format) with semi colon separation (as shown in the article)

• Anonymous
  January 07, 2014
  how you build up base32 encoded SID format?   I used some base32 to encode S-1-5-21-331390976-2650875657-2422772959-2004  and it gives me absolutely different value than  aecqaaaaaaaakfiaaaaabigacmesoam525kgregua3aaa

• Anonymous
  January 07, 2014
  Did you try the cmdlet? Get-FASTSearchSecurityEncodedSid That's what I used.

• Anonymous
  January 08, 2014
  Murad thanks for replau cmdlet works fine and can be solution for me. I was interested if i can build it using .net encoder. I'll check .net FS4SP ContentAPI.