Solving SharePoint FullTextSqlQuery Wrong Site Collection Error

The FullTextSqlQuery class is really nice for building readable query statements, all in familiar SQL syntax. While it’s a lot easier to use when querying data, the returned content is limited to that which has been indexed by SharePoint.

This is something to keep in mind, the indexing part. Consider the following code snippet, delivered as a generic static method:


RunQuery(“SELECT FileExtension, ContentClass, IsDocument, title, path, author from scope() “);

public static RunQuery(string queryText)
FullTextSqlQuery query = new FullTextSqlQuery(SPContext.Current.Site);
query.ResultTypes = ResultType.RelevantResults;
query.QueryText = queryText;
ResultTableCollection resultCollection = query.Execute();
DataTable resultDataTable = new DataTable();
ResultTable resultTable = resultCollection[ResultType.RelevantResults];

While running such code, you may encounter a problem where the wrong site collections are returning data. It may crop up in the form of a particular site collection being always used, or a weird permutation of site collections. If this problem occurs, there are generally two causes of the problem.

1) Ensure you are referencing Microsoft.Office.Server.Search.Query.FullTextSqlQuery and not Microsoft.SharePoint.Search.Query.FullTextSqlQuery
2) Ensure in Central Administration / Site Settings / Search And Offline Availability /Indexing Site Content the content is being indexed.


Use LINQ To Get Central Administration Web Applications

I needed a quick and dirty way to go through the SPWebService.AdministrationService to get a collection SPWebService.WebApplications and then test whether the SPWebApplication is the Central Administration application. To do this you need to use the SPWebApplication.IsAdministrationWebApplication boolean property. To keep things succinct you can just chain a LINQ query to return the representative SPSite of the desired administrative SPWebApplication. Consider the following RetrieveAdminApplication method that is consuming a SPContext object as a parameter:

public static SPSite RetrieveAdminApplication(SPContext context)
return SPWebService.AdministrationService.WebApplications.Cast().
Where(application => application.IsAdministrationWebApplication).FirstOrDefault().

This is probably not ideal, but will work!


SharePoint MySites and Pluggable Authentication Providers

Introduction To Problem with MySites and Pluggable Authentication Providers
A problem that people commonly encounter in a SharePoint site that they are facing externally on a perimeter is they want to use SharePoint MySites, however also want to implement a pluggable authentication provider that will allow them to give their external users easy access to the environment. The problem that arises is that when you implement pluggable providers, the MySite control that normally appears in a default sites:

< SharePoint:DelegateControl ControlId=”GlobalSiteLink1″ Scope=”Farm” runat=”server” id=”DelegateControl1″/ >

(why it wasn’t given a friendly name, I am not exactly sure)

will disappear when you install the pluggable provider and navigate to the default instance of your site.
This is a large problem for persons that want to implement MySites in SharePoint for a perimeter facing deployment that will allow customers to have their own little space. Although when you implement forms based on your portal the problem is not immediately resolved by the system, it is relatively straight-forward, simple fix that requires a little text editing and the bulk of the labor done through various SharePoint GUI’s.

On the good end, this was a larger problem without the concept of pluggable providers as provided because it typically required the users to be added into the local domain or for local users accounts to be added to the server in SharePoint 2003 which tended to be a user management nightmare and complicated the Active Directory forest.

Analysis of the Problem and The Proposed Solution
The reason that the problem arises is because by default you are going to have two config instances that exist that are going to look their own authentication routines, each acting as you specify it to. If you enable the sqlmembershipprovider and implement Forms Based Authentication on your core SharePoint site this is only going to effect one configuration element, what about if other application elements exist outside of your primary SharePoint web application?

As an example, let’s assume that there are two web application instances that exist in your environment, one with Windows, and another that exists under your FBA routine that resolves to the default SQL membership provider, so it would look like the below:

This is, in essence, is what you get by default when you solely implement a membership provider on your main SharePoint site, so in central administration you have made the changes for only one web application, and specified for external users that they can use the SQL membership provider (we are ignoring the concepts of Zones and AAM right now since we are only examining the overlying problem that is encountered). You can see that your mySites are generally pointing to a different web application in your at _layouts/PersonalSites.aspx or navigate to your SharePoint Service provider through WCAM and then select “My Site Settings”.

In the image referenced above this is demonstrated, two different web applications, one is your main SharePoint site and the other is your mySite, each using a different authentication routine to resolve itself, Web Application 1 is using Windows Based Authentication, whereas Web Application 2 is using Forms Based Authentication. In this scenario, Web Application 1 will have the MySite link available, whereas Web Application 2 will not have the MySite link available.

The evidence for why your are not seeing this link should be becoming more clear, and even has a formal definition (at least in multi-access security models)!. It is called in multi-level, MAC based environment, with various sensitivity label providers (for a brief introduction to MAC and sensitivity labels select here), “Cross-Provider Clashing”. When there are multiple applications that have inherent dependencies on the security routines that are provided for environmental, not local, cross-application stability, the inherent provider store must remain aggregate and assimilated by all the inherent assets. Meaning, if you are using forms based authentication with a custom authentication provider on one web application that provides security trimmed functionality to another web application, you MUST used forms based authentication with the same authentication provider for those related web applications, if you indeed do wish to to share those security trimmed objects.

How Can I Accomplish This?
Solving this problem is as simple as implementing Forms Based Authentication, even less so since the backend data store has already been setup. Seeing as that if you have run into this issue, you have already implemented a custom provider of some sort, you simply have to follow the same steps that you did in the other web application that is housing the other content that is cross context, and then make some changes to some administration sections in WCAM and WAT. So, both web applications that you have should use the same provider:

To do this:

1) Navigate and open the web.config file of both of your web applications (the less labor intensive way to find them and negate having to figure out the GUID’s is to open up the IIS MMC snap-in, select the virtual server, and select “Explore”) and make sure that the connection string and provider settings are identitical since you will be using the same provider for both your SharePoint Web Application and MySite Web Application (and as a best practice you should be encrypting your connections strings as detailed in my TechNet article here).

2) Open up SharePoint Central Administration (WCAM) and ensure that both of your web applications (core SharePoint Web Application and your mySite web application) are set to use forms authentication, this is done by navigating to Central Administration>Application Management>Authentication Providers which should place you at _admin/authenticationproviders.aspx. This can optionally be configured by manually editing the < authentication > < forms > elements in the web.config file, either way the end result will be same. Ensure while you are in this screen that the membership providers are identical that you have configured previously in the first step of the solution. If these is not the case, set them now to the custom provider and check the settings in the web.config for sanity purposes and to eliminate possible issues during the remaining configuration steps.

3) Following, you are going to have to change the site collection administrator settings, because there are both primary and secondary options here it is of no concern so a normal user account / service account can still retain some management options. Firstly, you have to change the site collection administrator of both sites (it is important to make this change for both of the site collections) to the admin user that you created in WAT (Web Site Administration Tool), for a better overview of the WAT tool select here. You should also add the ASP.NET admin account as an administrator in the Shared Service Provider. This can be done through the command line:

stsadm.exe -o editssp


        [-ssplogin ]

        [-ssppassword ]

        [-ssl ]

or you can optionally achieve it through the GUI by navigating to Central Administration>Application Management>Manage this Farm’s Shared Services>the SSP you need to change>Edit Properties>SSP Service Credentials.

In essence, this administrator account should become a secondary service account to those which you used when setting up the SharePoint implementation, and as the administrator will become your crutch account for troubleshooting administration issues as they arise.

4) RUN an IISRESET /noforce and your link will appear and be fully functional.

Is This A Bug?
This is in no way a bug, and I would imagine that Microsoft designed this flow to act as it does. To expect the option for an authentication provider to immediately blanket the rest of the provider settings would take away from the pluggable architecture that is introduced in this new version of SharePoint. Although it does require due diligence of the SharePoint administrator to really look at the changes that they are making to the authentication providers for all web applications, it ensures that your users are having the exact entry experience that you design them to have.