Automatic Fix of Pre Upgrade Issues (SharePoint 2007 to 2010)

Ever tried to run the PreUpgradeCheck command at your MOSS farm and received a frightening long list of issues that takes forever to solve manually?

If you are like me you refuse to do any manual work that takes more than 2 hours (and you’ll have to re-do it for every farm) – so I’ve tried to script all the required fixes. This is one of those posts that I’ve been working on for months so I hope you appreciate it – I’m a bit proud of the script at the end of the post.

The goal is to have a PowerShell (v1.0) script fix every one of those blocking issues you see in the PreUpgradeCheck report. On my own very messy (intentionally so!) farm with 40+ databases and site collections and a large number of solutions I’ve managed to fix all but the one about my operating system needing an upgrade to Server 2008. That one I’ll not work on 😉

Going from:

To this:

Note: It will only work of you have installed SP2 and cumulative update from October 2009. At least. I recommend that you work of as new a version as possible.


The script will remove invalid “stuff” from your sites so be absolutely sure that every solution that you care about is deployed to the farm – otherwise all features, files and webparts from missing solutions will be removed from the sites.

The script will also publish unpublished pages if they contain some of these invalid webparts.

I give you no warrenty – backup your farm first. I’m pretty sure I won’t mess it up though…

What it does

The script will check a number of things in your farm by working on the output of the “stsadm –o enumallwebs” command.

I use some direct SQL to get information out that is not possible through the API, but all modifications are done through the SharePoint 2007 API in a supported way only.

The script currently does 5 things:

Remove Invalid Webparts

(AKA: “The following web part(s) are referenced by the content, but they are not installed on the web server“)

This is the method I started with and by far the biggest timesaver.

It uses the “stsadm –o enumallwebs” to get a list of webparts that’s currently missing in your farm. They have been uninstalled at some point and are now those webpart with “Fatal Errors” on some of your pages. Some of the missing webparts are not even listed as failing with fatal errors (they have socalled “bad markup”) they are just listed on the page with and ID and no description (i.e. if you go to a given page and add “?contents=1” to go to the webpart maintenance page).

Trouble is that there can be lots of these and there is no easy way to find all the pages.

This function will query the SQL for the pages where these webparts are (both the one with fatal errors and the ones with “bad markup”) and then remove them through the API. The offending pages will be published (if not already), whatever checkouts they may have been there is overridden, the webparts are removed and the page re-published.

It will take all pages not just the one in document libraries (i.e. those that came through features)

Note: I’m only handling the case of shared webparts I did not have any user specific webparts in my test farm so I’m uncertain how that will be handled.

Note2: I’ve copied the PublishListItem from some of Gary Lapointes excellent extentions and converted them to powershell. It seems that no one has been as thorough handling special cases as he.

Remove Orphaned Site Collections

(AKA: “Issue : Orphaned site collections“)

Site collections can get orphaned in a number of ways; the fix is simply to delete those.

You will not lose anything (new) as those sites were not accessible before anyway.

Remove Uninstalled/missing Features Activated at Web scope

Features that have been uninstalled at some point often linger in the content databases because they are still activated in some web or site. That activation is not deleted when the feature is removed though the feature will have no actual function anymore.

I wrote quite a bit on this a while ago.

The script will run through all those SPWeb’s and deactivate the offending features.

Note that I’m not crawling all webs in the site I only access those with missing features.

Remove Uninstalled/missing Features Activated at Site Collection Scope

(AKA: “The following feature(s) are referenced by the content, but they are not installed on the web server“)

Similarly features may be activated at site collection level and forgotten. In this case these features are not reported by the “stsadm –o enumallwebs” command and I have to run through every Site Collection in the farm to locate the features.

Remove Missing Setup Files

(AKA: “The following setup file(s) are referenced by the content, but they are not installed on the web server“)

After features have been uninstalled it is quite common that they forget to remove the files they installed on the farm. I suppose it’s just the way SharePoint works. If a feature installs a file in a document library somewhere changes are it will remain.

What are those files? Some examples are stuff in the style library, webpart gallery, master pages, page layouts, etc…

This method uses SQL to find where the missing files are still references and deletes them from the lists. It even deletes files that have been unghosted which could actually still be accessed because SharePoint has made a customized copy of it.

I’m still considering if it would be better to only remove the ones that are still ghosted.

Note: In my tests some page layouts could not be removed because they were still being referenced in the farm. That is clearly an error in my farm, however the script will not fix that. It really does require special handling by a human to decide what to do. I suppose it’s a good thing that they are not removed in this case as some things might otherwise break.

What it does not no

Out of scope of script:

  1. Fixing the OS version
  2. Fixing the database version

Sorry 😉

How to Execute

First of all the script requires you to be site collection owner on every site collection, farm admin and have owner rights in every content and configuration database. Pretty much all you can get.

In the beginning of the script there is a few switches that you should know:

  • The logging level, set it to 3 to get lots of info: $verboseLevel = 3. Lower is less.
  • The “$whatif” ´switch. Set it to “$true” to have the script simulate a run but not change anything in your farm
  • The “$targetdb” that can limit the script to a single content database only. Blank equals all databases a name indicates one (note: Not the site name)

I’ve built in a lot of error handling and logging to let you know what’s going on.

Note that STSADM must be included in the path setting.

Recommended sequence of execution

Everything need to run on the SharePoint 2007 server and it must have (at least) PowerShell 1.0 installed.

  1. Backup all content databases. This is not optional. Easiest to use stsadm –o backup
  2. Use stsadm -o preupgradecheck
  3. Run script on one or all databases with the “whatif” statement set to true
  4. Check and recheck the output and verify that it looks correct to you.
    1. You may want to reduce the logging level a bit
  5. Run with $whatif set to $false, i.e. let it work!
    1. Depending on your confidence in me you can ask to work on only one database at a time
  6. Check the output!
    1. Errors and warnings will be written both to the output stream (what is piped down in your logfile) and on the screen
  7. Run preupgradecheck again and see the result J

Note to run the script use something like “powershell -file FixUpgradeIssues.ps1 >>fixupdateoutput.txt”, i.e. always pipe the output to a file that you can investigate later.

Runtime is about 5 minutes on my 40+ database test system so reasonably ok given the task.

I’ve been working on this script for a couple of months and have fixed a huge number of issues and funny edge cases; however I’ve likely missed a few. Let me know in the comments.

Good luck J

Download the Actual Script

All 850 lines of powershelling are not pretty in a blog post, so you can download it here.

[Updaet 25-12-2010: Now with correct file]

How to Setup Kerberos SPNs for SharePoint

This is a fairly short post about how to setup kerberos for SharePoint 2007 and 2010 written because it is a subject that frequently continues to baffle SharePoint consultants.


Kerberos authentication has been available since SharePoint 2003 yet most people are still using NTLM authentication. Why? NTLM is ancient and we all know that it works out of the box where Kerberos comes with a huge number of knowledge base articles and instruction to create Service Principal Names (SPNs) in your AD.

The reason for making the effort is that it enables you to solve the old double hop issue.

Ehh? When you are authenticated on a SharePoint server the server side code runs in “your name” with the privileges of your account. If however that server side code calls a second system, e.g. a web service, that second (the double hop) call is not authenticated and run with anonymous privileges. This is a limitation (or a security feature in your AD).

Now there are a number of ways around this problem, hardcoding some credentials in the server side, escalating privileges in the code, messing with the host file, etc. but the only good way is to use Kerberos.

Now when does this apply to SharePoint, where does it benefit from Kerberos? Then non-complete list is (additional suggestions welcome in the comments)

  • RSS viewer webpart
  • Page viewer webpart
  • Mail, calendar and task webparts (often used in MySites)
  • Excel services
  • Forms services
  • (and your custom code)

How to setup Kerberos

Now this is very far from a complete guide on Kerberos as it’s by far too long and too complicated for most people to read it. I’ll describe the simple bits needed to make SharePoint spin with Kerberos.

SharePoint setup

When you create a new web application you get the choice of using NTLM or Kerberos authentication. After creation you can always go into the authentication provider settings within the Central Administration and change it. Note: That a faulty Kerberos setup is virtually the same as bare NTLM 😉

SPN setup

The hardest part is to setup Service Principal Names in your AD to enable SharePoint to use Kerberos properly. Basically a SPN is an entry into the AD allowing a specific service account for a service (e.g. HTTP) to re-authenticate a user.

The trick is of course what exactly should go into these SPNs and how many you need. The answer is:

  • One for each URL you are serving
  • One for each combination of server and app pool service account (if you are like me you’ll have multiple)

You use the “SetSPN.exe” program to do this and you need very high privileges to do so. Domain Admin or equivalent is sufficient 😉 though it could have been delegated to lower ranks. Note that this can be executed on any server in your domain not only the SharePoint servers.

The major point of this post is that I’ve created a small PowerShell script to generate a bat file that will create the SPNs required.

Given a config file (CreateSPNBatFile_config.xml) like this:

 <?xml version="1.0" ?> <Config> <Servers> <Server name="moss1" domain="" /> <Server name="moss2" domain="" /> <Server name="moss3" domain="" /> </Servers> <Urls> <Url hostheader="intranet" serviceaccount="contoso\sa_intranet" /> <Url hostheader="" serviceaccount="contoso\sa_extranet" /> </Urls> </Config> 

The script (CreateSPNBatFile.ps1) (download here if quotes and stuff are messed up):

# Create a bat file for SPN creation based on config file # # 2010 Søren L. Nielsen function CreateSPNBatFile( $configFile, $outputFile ){ $config = [xml] (Get-Content $configFile) $output = "" #Gather all the service accounts $serviceAccounts = @{} foreach( $url in $config.Config.Urls.Url ){ $output += "Setspn.exe -A HTTP/" + $url.hostheader + " " + $url.serviceaccount + "`r`n" $serviceAccounts[ $url.serviceaccount.ToLower() ] = 1 } foreach( $server in $config.Config.Servers.Server ){ foreach( $serviceAccount in $serviceAccounts.Keys ){ #every combination of server and service account $output += "Setspn.exe -A HTTP/" + $ + "." + $server.domain + " " + $serviceAccount + "`r`n" } } $output += "pause`r`n" Set-Content -LiteralPath $outputFile -Value $output Write "Batch file $outputFile created" } CreateSPNBatFile "./CreateSPNBatFile_config.xml" "./CreateSPN.bat" 

Will generate the following bat file (CreateSPN.bat):

Setspn.exe -A HTTP/intranet contoso\sa_intranet Setspn.exe -A HTTP/ contoso\sa_extranet Setspn.exe -A HTTP/ contoso\sa_extranet Setspn.exe -A HTTP/ contoso\sa_intranet Setspn.exe -A HTTP/ contoso\sa_extranet Setspn.exe -A HTTP/ contoso\sa_intranet Setspn.exe -A HTTP/ contoso\sa_extranet Setspn.exe -A HTTP/ contoso\sa_intranet pause 

Ready to ship off to your domain administrator.

Trust for Delegation (Updated)

You need to configure the servers to be trusted for delegation in the AD (sorry I don’t know the command line). The procedure is:

  1. On any computer in the domain start Active Directory Users and Computers (“dsa.msc”)
  2. In the left pane, click Computers.
  3. In the right pane, right-click the name of your SharePoint server, and then click Properties.
  4. Click the Delegation tab (or General for WinSrv2000), click to select the Trust computer for delegation check box.
  5. Repeat step 3 and 4 for every SharePoint server in your farm including the SQL server

And you also need to configure the service accounts the same way (provided that they are domain accounts):

  1. On any computer in the domain start Active Directory Users and Computers (“dsa.msc”)
  2. Locate your service account in your AD (I generally prefer to search for the samAccountName)
  3. click Properties on the service account
  4. Click the Delegation tab (or General for WinSrv2000), click to select the Trust this user/computer for delegation to any service (Kerberos)  check box.
  5. Repeat step 3 and 4 for every service account in your farm including SQL account


Kerberos uses port 88 to communicate with the AD server therefore that port need to be open between the AD and your servers. There likely are a bunch of additional ports – ask google not me.


There are of course a number of ways to test this – look at the list above.

I generally start by enabling fiddler and check that the authentication part looks like a Kerberos ticket – fiddler will tell you this on the “Auth” tab.

The easiest test is to go to a list and get the RSS feed url. Then add a RSS viewer webpart to a page with that URL. If Kerberos works (for that site! You need to test all) you should see the list feed with the same elements as you would browsing directly to the RSS url.

If it does not work you’ll see the RSS feed as an anonymous user (note: Pick a list without anonymous access).

There are a large number of potential problems that would make this fail. Kerberos is very hard to debug – I can’t help you all but do try some of Microsofts tools.

I hope this help some of you with Kerberos. I had to hunt down a surprising number of post, documents and trial runs to compile this. Sorry for not sharing them all I simply don’t recall all of them as I did the installation over a year ago.

Note: If some users cannot authenticate but others get a “400 bad request” have a look here.