Recover BDC application after an AD account is deleted

On a fine Friday morning, just a couple of days before production release a watchful IT manager deleted AD account of one of administrators no longer with the company. The account had permissions to multiple BDC objects. We found out about it a little later, by chance, but first we saw our SharePoint 2010 application built on Business Data Connectivity and InfoPath completely fall apart.

Instead of any meaningful data all External Lists, administrator pages, SharePoint Designer, and thousands of lines of SharePoint logs showed the same dreadful message: "The specified user or domain group was not found". Worse, trying to set object permissions in Central Administration resulted in Null reference exception. SharePoint Designer did not even show any External Content Types, as if they did not exist!

Online search did not uncover any easy solutions, with possibility of reconfiguring a production farm. Rebuilding the application from scratch normally took us a couple of days. The weekend was beginning look very grim.

But with a few hours left in the day and a full admin access to the server we just had to try finding an easy way out. Armed with SQL Server Profiler and a test farm to experiment on, we reproduced the error and watched queries against the SharePoint database server. We reviewed data in the BDC database. We also tried recreating BDC Service Application and re-attaching existing BDC database. Turned out that settings in the BDC database caused the error.

So in the end, (after backing up the database!) we had to remove references to the deleted AD account from AR_MetadataObjectSecurity table. Sample query:

delete from [AR_MetadataObjectSecurity]
where IdentityName like '%username%'

We got the application back, but learned our lesson: do not to give individual user account permissions to the ECTs directly, but rather through AD groups.

Refresh All Published Content Types On Next Update for All Site Collections.

Recently, I had an issue where some content types published to the content type hub did not get published on the first try. The reason is that I had site collections where the Document Set feature had not been enabled for the initial content type publish, so all content types derived from Document Set failed to be published.

It’s easy enough to activate the document set feature for all site collections, right? PowerShell. I can do that in one line:

   1:  (Get-spWebApplication <web app url>).Sites | % {enable-spfeature "DocumentSet" -url $_.url}

Done. But that’s not what this post is about.

Now that the Document Set feature has been enabled, I needed a way to republish the content types that failed. I didn’t want to modify the content types in any way, so my only real option was to go into the content type publishing settings for each site collection and click the “Refresh all published content types on next update” checkbox. This magically makes all the content types re-publish themselves to the site collection the next time the Content Type Subscriber timer job runs.



That’s all well and good if you have one or two site collections – go to each site collection, Site Actions –> Site Settings –> Content Type Publishing (under Site Collection Administration) –> Click the checkbox –> Click OK. It’s easy.

But I had a lot of site collections. This was my My Site web application. With personal site collections for a lot of users. 200 or so users. That’s a lot of clicking.

Unfortunately, I could find no apparent way to “click” this checkbox through PowerShell. There is no obvious member or property on either SPSite or SPWeb that seemed like it would do the trick. Frustrated, I turned to ILSpy.

ILSpy is what has replaced Reflector in my toolbox now that Reflector is no longer free. I highly recommend it. But this isn’t a post about ILSpy…

The first step in trying to determine what SharePoint does when that checkbox is checked was to open the aspx file to find out where the code behind is located. Here’s the key line in ContentTypeSyndicationHubs.aspx in the Template\Layouts folder:

<%@ Page Language="C#" DynamicMasterPageFile="~masterurl/default.master" Inherits="Microsoft.SharePoint.Taxonomy.OM.CodeBehind.ContentTypeSyndicationHubsPage"       %>

Inherits attribute tells me the namespace I’m looking for, but not the assembly. First I checked Microsoft.SharePoint.dll, but when I didn’t find it there, I checked Microsoft.SharePoint.Taxonomy.dll (in the ISAPI folder). There I found what I was looking for:



So if the checkbox is checked, a method called Subscriber.RefreshAllTimeStamps is called for the current site. What does that function do? If I click on it, I get a warning message with the latest version of ILSpy (previous versions would just silently fail…)



With “Show internal types and members” selected under the View menu, I find this:



Ahhhhhh – now we’re getting somewhere. Even though this is an internal method that can’t be called from PowerShell, now that I know what I’m doing, it’s easy enough to convert:

Function RemoveAllTimeStamps([Microsoft.SharePoint.SPSite] $site)
{   
    if ($site -eq $null) { return }
    $rootWeb = $site.RootWeb
    if ($rootWeb.Properties.ContainsKey("MetadataTimeStamp"))
    {
        $rootWeb.Properties["MetadataTimeStamp"] = [string]::Empty
        $rootWeb.Properties.Update()
    }
}

Now that we’ve got that, it’s just a matter of calling RemoveAllTimeStamps for each site collection in the web application.

$webapp= Get-SPWebApplication <web app url>
$webapp.Sites | ? {$_.AllWebs.Count -ne $null} | % {
    $site = $_
    RemoveAllTimeStamps $site
    $site.Dispose()
}

Now, simply run the Content Type Hub job, followed by the Content Type Subscriber job for your web application. If you have a lot of content types – be prepared to wait. I typed this up this entire blog post while my job went from 9% to 19%.

- Andrew Moore

Powershell error - The registration of the custom connector for protocol is corrupted. Cannot be removed

Recently came across this error when trying to remove a deployed custom SharePoint 2010 search connector in PowerShell:

Remove-SPEnterpriseSearchCrawlCustomConnector -Identity Inmagic -SearchApplication $searchapp

Remove-SPEnterpriseSearchCrawlCustomConnector : The registration of the custom connector for protocol 'Inmagic' is corrupted. Cannot be removed.
At line:1 char:46
+ Remove-SPEnterpriseSearchCrawlCustomConnector <<<<  -Identity Inmagic -SearchApplication $searchapp
    + CategoryInfo          : InvalidArgument: (Inmagic:String) [Remove-SPEnterp...CustomConnector], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Office.Server.Search.Cmdlet.RemoveCustomConnector

It turned out that the message was wrong and protocol name was case-sensitive.  Just sharing as it took a while for me to figure out.

Indexing SharePoint 2007 MySites and User Profiles with SharePoint 2010 with FAST

Technorati Tags: FAST,Search,SharePoint 2010

Recently I was approached by a customer who wanted to replace the Google Search Appliance on their SharePoint 2007 farm with the FAST search engine in SharePoint 2010.  For a couple of reasons they didn’t want to upgrade the whole farm at this point and were concerned about indexing the MySite and User Profile information on the SharePoint 2007 farm.  I told them that I thought it was possible but hadn’t tried it so would get back to them.  I did some quick research but couldn’t find a definitive answer.  So I decided to setup an environment and try it for myself.  I started with the Microsoft provided SP2010 with FAST environment found here:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=751fa0d1-356c-4002-9c60-d539896c66ce&displaylang=en

I then built a SP 2007 environment on another VM that I had added to the Contoso domain.  I imported the user profile information into the SP2007 farm, and copied over the ‘Ask Me About’ descriptions.  I put the ‘Ask Me About’ information in the Skills profile column in SP2007 since the ‘Ask Me About’ profile column is new to SP 2010.

First lets look at a screen shot of the search results from the SP2010 MySites and User Profiles:

Then compare that to a screen shot of search results when indexing the SP2007 environment MySites and User Profiles.

As you can see they are nearly identical.  To get the ‘Ask Me About’ text to show up I did add the Crawled Properties People:SPS-Skills(Text) to the Responsibilities managed property but other than that it was seamless it worked without changes.  You will notice that the ‘Add as colleague’ and ‘Browse in organizational chart’ don’t appear in the SP2007 crawled information that is because SP2007 doesn’t support these features.

So with that happy indexing.

Andrew

Improving SharePoint performance

After working on a highly available and heavily customized - MOSS Publishing Portal farm, here are few lessons learned on improving SharePoint performance.

General web page improvements: These are applied to branding files and application resources to improve the page loading speed and user experience. Most of them are applicable to any web application. SharePoint handles some of these features natively using blob cache, core files, etc but for custom developed solutions, these items should to be handled manually:

• CSS Sprites - are built by grouping small images into one large image and referring part of the image using CSS class. This reduces number of images downloaded from the client browser and improves the overall page loading speed. More details on CSS sprites can be found at http://css-tricks.com/css-sprites/. Since, CSS sprites are not supported in IE6, it cannot be used in organizations that still support IE6.

• Consolidate JS and CSS files - Since number of HTTP requests are more expensive than downloading single large file, consolidating multiple JS or CSS files into single large file would improve the overall resource load times. Also, we observed that combining most commonly used files together yielded better performance (combining too many files may adversely impact the download speed & processing performance due to large file size).

• Group CSS files by Browser type - Since, browser type is unique for each customer request, we observed it as the best way to combine the commonly used CSS files. Also, CSS expressions were avoided due to performance concerns.

• Anonymous access for CSS, JS and image files - Since NTLM requests require at least one additional roundtrip (typically for 401 unauthorized responses), enabling anonymous access to unsecure branding resources reduces the number of roundtrips and improves the page download speeds.Also, when SharePoint return “401 unauthorized” response, it includes the entire Error page response (see in Fiddler), reducing the size of “401 unauthorized” page would reduce the response payload and reduce bandwidth utilization.

• Cache JS, CSS and image files in browser - Since SharePoint supports this natively, no additional work is required to cache the resource files. However, if the resource files are dynamic in nature (changes with releases) then these resources should be accessed using unique querystring to invalidate the browser cache.In our implementation, to ensure unique query string for JS & CSS files, a simple HTTP handler was implemented to render unique query string based on file checksum. Files hosted in SharePoint layouts folder can use MakeBrowserCacheSafeLayoutsUrl() function to render unique url.

• Avoid Minification of JS and CSS - Minification removes extra characters and spaces in the JS & CSS files to reduce the file size and to improve the download speed. However, when the file is compressed, the minified file has little or no change in the download. Also, a minified file is harder to troubleshoot than a regular file. So, Minification was avoided for better maintainability.

• Include JS files and CSS files in the header - Since browser interpret HTML head content and body content differently, i.e., all script references in head tag are loaded after the page is loaded and references in body tag are loaded at the time page load, reducing the referenced content in body improves the page rendering experience.
  This is achieved - by referencing all required scripts at the time of page load in body tag and rest of them in the head tag. CSS files are always referenced in head tag.

• Content delivery network (CDN) - has unique advantages such as geographical distribution of data and parallel loading of resources in the web page. This can be utilized to improve the SharePoint performance. Also,
• Since CDNs have higher percentage of availability and throughput, moving the branding elements to CDN can benefit the performance.
• When CDN files are accessed over multiple domain names, it enables Browser to download the files in parallel (applicable to IE6 or older browsers).
• JS and CSS files compression - Natively supported for files hosted in SharePoint. For files hosted outside SharePoint, compression greatly improves the download speeds.

• Use JQuery library or an equivalent library - There are number of reasons to use JQuery but based on our observation the performance benefits were clearly visible as the code grew larger.  Also, improved overall quality of code.

To be continued with Caching, Webpart & other development guidelines.

What’s This Destination Folder Field On My File Upload Page?

We recently performed a MOSS 2007 to SPS 2010 upgrade. From a SharePoint perspective, it was a pretty vanilla upgrade where we moved all the content (sites, webs, documents, lists, etc) from a 2007 farm to a 2010 farm. After we performed the migration we noticed some differences between the "migrated" and "newly created" versions of our document libraries. We were noticing on most "migrated" document libraries, when a user uploaded a file, they were now seeing a "Destination Folder" field in addition to the File Upload and Overwrite checkboxes.

This new Destination Folder field input was showing regardless of whether there were sub folders or not. While this is not likely a major drawback for most sites, it was a difference, and our end users wanted answers.

I analyzed two sites, one migrated, one newly created. Both were created from the same site template (which did not need to change for upgrade to 2010). Each URL to the Upload page was the same for the equivalent document library in both sites:

/_layouts/Upload.aspx?List={GUID}&RootFolder=&IsDlg=1

I pulled up the Upload.aspx page from the SharePoint Root (aka 14 hive) and looked for some insight, but to my surprise, there were no references to the Destination Folder input controls in either the markup. I checked the referenced javascript to see if maybe these were being built dynamically, but found nothing. Using the Page directive from the Upload.aspx, I located the assembly containing the base class: Microsoft.SharePoint.ApplicationPages.dll and loaded it up in my favorite disassembler. This assembly was not in the GAC as I expected, but rather in my web application's _app_bin directory.

A copy of the Microsoft.SharePoint.ApplicationPages.dll assembly is copied into the _app_bin folder for each web app. I haven't researched why this assembly is copied here, rather than installed to the GAC, but suspect it has something to do with CAS and running with least amount of permissions. For another day…

After looking in the obvious places and finding no references to these controls, I had one place left to look: OnPreInit(). Sure enough, I found some logic in here used the SPWeb.CustomUploadPage and does a Server.Transfer to the page if one is specified.

I put together a quick PowerShell script to help me compare my two webs:

$newWeb = Get-SPWeb http://newweb/

$migratedWeb = Get-SPWeb http://migratedweb

Write-Host "New custom upload page: " $newWeb.CustomUploadPage

Write-Host "Migrated custom upload page: " $migratedWeb.CustomUploadPage

Sure enough, the new web did not have a CustomUploadPage specified, but my migrated did. It was pointing to: /_layouts/UploadEx.aspx. Sure enough in the markup for this page were references to target folder's input controls, however, they were surrounded with some conditional logic that was checking to see if the list "HasFolders" (i.e. does the list have SubFolders?). But my migrated web's document library doesn't have any SubFolders…What gives? Time to peek at the UploadEx base class in the disassembler (Microsoft.Office.Policy.Pages.dll, again in the _app_bin folder for each web application).

After some tracing through, I found that ultimately, the decision on whether a folder has subfolders, at least in the context of this particular page, is based on an entry in the Folder's property bag: folder.Properties["vti_foldersubfolderitemcount"]

I wrote some more PowerShell and sure enough, in the case of my migrated document library, this had a value of "1". Again, my migrated library has no sub folders, so why would this value be set to "1".

Disclaimer: the rest is theory as I did not invest the time to fully research this and by this point I already had the information needed to explain the situation to the end users and get a script written to clear the SPWeb.CustomUploadPage value in all places needing it which would be sufficient to eliminate the display of the "Destination Folder" input controls since the regular Upload.aspx page doesn't make use of these controls.

I suspect, that the 2007 to 2010 migration set the ["vti_foldersubfolderitemcount"] property to be "1" and/or this property was handled differently in 2007 as compared with 2010. You see, there is actually a SubFolder, but it's the "Forms" folder, which is generally hidden from view and not available for users to upload to. Perhaps during 2007 -> 2010 migration, the upgrade process wasn't checking for the "Forms" folder if it was resetting the "vti_foldersubfolderitemcount" property? Also, the migration must have been responsible for setting the SPWeb.CustomUploadPage property on each of the migrated webs because we had not explicitly made this change. Perhaps this is a change to how document libraries are provisioned between 2007 and 2010?

To test this theory out a bit, I created a brand new document library in my migrated web. I compared the newly created library to the migrated library with some PowerShell:

$migratedWeb = Get-SPWeb http://migratedweb

$migratedDocLib = $migratedWeb.Lists["Migrated Doc Lib"]

$migratedRootFolder = $migratedDocLib.RootFolder

Write-Host "RootFolderUrl" $migratedRootFolder.Url

Write-Host "folders count" $migratedRootFolder.SubFolders.Count

Write-Host "folder[0] url" $migratedRootFolder.SubFolders[0].Url

Write-Host "Prop value" $migratedRootFolder.Properties["vti_foldersubfolderitemcount"]

write-host""

write-host""

$newDocLib = $migratedWeb.Lists["New Doc Lib"]

$newRootFolder = $newDocLib.RootFolder

Write-Host "RootFolderUrl" $newRootFolder.Url

Write-Host "folders count" $newRootFolder.SubFolders.Count

Write-Host "folder[0] url" $newRootFolder.SubFolders[0].Url

Write-Host "Prop value" $newRootFolder.Properties["vti_foldersubfolderitemcount"]

The results were as I expected: The newly created document library (in the migrated web) had a vti_foldersubfolderitemcount property value of 0, whereas my migrated document library had a value of "1" (presumably a reference to the "Forms" folder).

SharePoint 2010: Using Visual Studio 2010 to Create a List Definition and List Instance (Based on a new Content Type)

I have expanded on the Content Type solution I posted here to now include List Definitions and a List Instance. It is very easy to do with Visual Studio 2010. All of the details are in this post.

InfoPath Holiday Poem

Since the holiday season has officially started along with the upcoming release of my second book, I decided to create a holiday poem based on InfoPath with SharePoint 2010. The post is located here.

Preupgradecheck Failure: Content database with modified database schemas

While recently running a preupgradecheck on a MOSS 2007 farm, I received the following scary error:

Failed : Content database with modified database schemas 
User modifications to the SharePoint content database, including but not limited to table schemas, index, stored procedures, are not supported and will cause upgrade to future versions of SharePoint to fail.The databases in the following list seem to have been modified from the original schema:
Data Source=SEVER;Initial Catalog=DB;Integrated Security=True;Enlist=False;Connect Timeout=15

My first thought was that someone must have been messing around with the database but I certainly don’t think any reasonable SharePoint developer or admin would make a change to the schema of a content database. After some hunting, I turned up this page. Buried at the very end is the following:

If you did not make any manual schema changes to the database, you can ignore this error and continue with the ugprade [sic]. This is a residual error from the upgrade process from SharePoint Portal Server 2003 to Office SharePoint Server 2007.

So, if you upgraded from SPS 2003 to MOSS 2007, you should expect to see this complaint from preupgradecheck. The upgrade to 2010 will work just fine. Now if I can just figure out where all those missing features came from….

Automate Search Site Customizations

Managing a search sites and all their webpart settings can be a difficult process requiring many manual steps. Keeping the Fetched Properties and XSL of the Core Search Results webpart on the default search results page in sync between multiple environments can be time consuming and error prone. It’s bad enough to have to do this manually in a production environment, but add development, test, and stage environments to the mix and you end up with a lot of clicking and pasting; never a good way to arrive at a stable and repeatable process for your deployments. Luckily, PowerShell provides some relief to the copy, click, paste, repeat method of deploying search webpart customizations.

The following PowerShell script can read the values from an external XML or XSL file (which can now be placed under source control), check out the appropriate page (results.apsx in this case), update the property values on the web parts, check the page back in, and publish it:

function LoadXmlData([string]$filePath)
{
    return [System.IO.File]::ReadAllText($filePath)
}

$executingFolder = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
$resultsPageUrl = "http://localhost/sites/search/pages/results.aspx"
$siteUrl = "http://localhost/sites/search"

$searchSite = new-object Microsoft.SharePoint.SPSite($siteUrl)
$searchWeb = $searchSite.OpenWeb()
$publishingWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($searchWeb)
$resultstpage = $publishingWeb.GetPublishingPage($resultsPageUrl)

$resultstpage.CheckOut()
$webpartManager = $searchWeb.GetLimitedWebPartManager($resultstpage.Url,  [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

foreach($webpart in $webpartmanager.WebParts)
{
    switch ($webpart.Title)
        {
            "Refinement Panel"
                {
                    write-host "Setting Filter Category Definition"
                        $webpart.FilterCategoriesDefinition = LoadXmlData(join-path $executingFolder "RefinerDefinition.xml")
                }
               
        "Search Core Results"
                {
                    write-host "Setting Fetched Properties"
                        $webpart.PropertiesToRetrieve = LoadXmlData(join-path $executingFolder "FetchedProperties.xml")

                        write-host "Setting XSL";
                        $webpart.Xsl = LoadXmlData(join-path $executingFolder "ResultView.xsl")
                }
        }
     $webpartmanager.SaveChanges($webpart)
}

$resultstpage.CheckIn("Automated Update");
$resultstpage.listItem.File.Publish("Automated Update")

$publishingWeb.Close()
$searchWeb.Close()
$searchSite.Close()

No more edit page, edit web part properties, save, check in, and publish in every environment. Simply copy the configuration files to the server and execute the script. Less chance for errors and less time spent deploying changes. The approach here can easily be expanded to manage much more than the three properties in the example.