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.

image

SampleFiles.zip

Comments (12)
  1. Ananth says:

    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?

  2. Murad says:

    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.

  3. Error says:

    I got the binary descriptor in the database table but after crawling am getting the following error as

    Error while crawling LOB contents. ( Cannot open database "xxxxx" requested by the login. The login failed. Login failed for user 'domainusername'. )

  4. Error says:

    Any idea on the error i have posted.

  5. More Queries says:

    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?

  6. Murad says:

    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…

  7. Docacl says:

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

  8. Semicolon separation says:

    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.

  9. Murad says:

    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)

  10. Tomas says:

    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

  11. Murad says:

    Did you try the cmdlet?

    Get-FASTSearchSecurityEncodedSid

    That's what I used.

  12. Tomas says:

    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.

Comments are closed.

Skip to main content