When software attacks!

Thoughts and musings on anything that comes to mind

Netware 6.5 on Hyper-V

As part of a customer project I needed to create a Netware environment for testing. It's been a little while since I did any netware management and I quite enjoyed it. I did, however, encounter a couple of gotchas which I thought I'd write up for the greater good.

Netware OS

Installing the Netware OS was actually pretty straightforward. There are no integration services offered for Netware so from the outset I knew that I would need to use legacy hardware options in the virtual machine.

I created a nice big dynamic virtual hard disk for the server because I will need to install GroupWise and a whole bunch of other services later. I attached this to the virtual IDE controller, gave the machine a single processor core as anything more needs integration services, and (critically!) added a legacy network adapter. Netware isn't a huge memory hog, so I added 1Gb of RAM and off we went.

I hit a snag at the point where the server tried to identify network drivers - it couldn't find any, and I couldn't see any in the list to load manually which matched the emulated hardware.

The solution turned out to be really simple: as you step through the installation screens there is an option to allow unsupported drivers. By default that is set to no. If you change it to yes, the installation recognises the network adapter as an old DEC and loads a driver which works.

Apart from that, I have experienced no difficulties with the server whatsoever, other than I have to run the machine connection window full screen to be able to switch between console screens.

Windows Client

I will admit, this one drove me crazy for a while before my final epiphany. There is a Novell client for Windows Vista now available, but why build a Vista VPC when an XP one would need less horsepower?

I dutifully grabbed an old Virtual PC VHD of our XP base install and fired it up.

Problem number one: In order to install the integration services I need to first uninstall Virtual Server Additions. No sweat, thinks I, clicking the uninstall button. Nope - you get a nice message saying that setup can only run inside a virtual machine!
Slightly surreal, I must say. I had to fire up the machine under Virtual PC and remove the additions, then copy the VHD back on the hyper-v server and start the system so I could install the integration services.

Problem number two: Once I'd installed the Novell client I couldn't get it to see the Netware server. Nothing I did would work - I strapped down every setting I could on the client to point it at the Netware machine but it refused to connect, although I could ping between the two quite happily.
The solution, when I finally figured it out (and I must admit it was pure chance that I thought to try it) was to remove the shiny new virtual network adapter and replace it with a legacy adapter. As soon as I did that, the Novell client could communicate quite happily with the server!

The situation would appear to be that the Novell client stack can't communicate properly through the new virtualised driver provided by Hyper-V. Exactly why this should be, I have no idea, but it drove me wild for a good couple of hours today.

Unable to access My Tasks in Project Web Access

Sometime ago we noticed an issue with My Tasks in Project Server. Certain users were unable to access My Tasks at all - they simply got a SharePoint error page.

A little jiggery-pokery with callstack and custom errors later, we saw that the error referenced a GUID for a task. I then searched the Project Server Publishing DB for the task GUID and subsequently located the project to which it belonged. If I edited the project in MS Project and updated the server, removing the task assignment from the user, they could access my tasks.

For anyone who has a similar problem, here are the SQL queries you need:

select * from dbo.MSP_TASKS where TASK_UID='<Task GUID>'

select * from dbo.MSP_PROJECTS where PROJ_UID='<Project GUID>'

Most odd. So I logged a call with our friends in Microsoft Support.

It's been parked for a while, but I received an email from support today advising me that the Infrastructure updates would help. Funnily enough, I'd already installed them (we keep our SharePoint farm as fully patched as we can), so that was almost all the way there.

Finally, they provided a short SQL script to run against the Publishing DB. This would identify any tasks that were orphaned and correct the issue. Luckily, we had none!

SELECT MP.PROJ_NAME, MAS.PROJ_UID, MAS.TASK_NAME,
MAS.TASK_UID, MR.RES_NAME, MAS.RES_UID,MAS.ASSN_UID
FROM MSP_ASSIGNMENTS_SAVED AS MAS
INNER JOIN MSP_PROJECTS AS MP        
ON MAS.PROJ_UID=MP.PROJ_UID
INNER JOIN MSP_RESOURCES as MR
ON MAS.RES_UID=MR.RES_UID
WHERE TASK_UID NOT IN(SELECT TASK_UID
FROM MSP_TASKS_SAVED)

When I experienced the problem there were no hits in my old friend Google so hopefully this will help somebody, somewhere.

Here are the links to the infrastructure updates for completeness. Remember to read the docs carefully on installing these babies!

Infrastructure Update for Windows SharePoint Services 3.0 (KB951695)

Infrastructure Update for Windows SharePoint Services 3.0 (KB951695), 64-bit edition

Infrastructure Update for Microsoft Office Servers (KB951297)

Infrastructure Update for Microsoft Office Servers (KB951297), 64-bit edition

Infrastructure Update for Project 2007 (KB951547) - English

Configuring ActiveSync on Windows Mobile for Exchange Push

I'm not certain how many of you will find this useful, but I had a question about configuring the Touch Diamond to talk to Exchange which I regrettably failed to notice.

It's an interesting point of debate, now I come to think of it. When I got my Diamond the first thing I did was confgigure ActiveSync directly on the device. Whilst I do connect to my laptop from time to time, I don't actually have any real sync going on between the two, other than possible for OneNote and Notes. How many people out there still use Outlook to sync their phone information if Exchange Push is available?

Anyway, back to the plot. To get this all going without using Windows Mobile Device Center or ActiveSync, grab your phone and go to Programs via the Start menu.

  1. In Programs, find ActiveSync.
  2. In ActiveSync, select Menu and then choose Configure Server
  3. In Server address, enter the name of your Exchange server. This is the same as the hostname you use for OWA (e.g. mail.mycompany.com)
  4. If your server needs https rather than http, tick the option monikered 'this server requires an encrypted (SSL) connection)'
  5. Tap Next and you will be asked to provide login information - username, password and the name of your Active Directory domain. Make sure 'Save password' is ticked or the automatic sync won't work and you'll have to enter your password each time you trigger a manual sync.
  6. Tap Next once more and you will be able to select what gets synchronised. I have an unlimited data plan and I hate losing stuff, so I tick every box - Contacts, Calendar, E-mail and Tasks.
  7. If you're being clever at this point, tapping Menu will allow you to get to the advanced options, where you can configure how conflicts are handled and what connection to use (normally this should be 'Internet')
  8. Tap Finish and the phone should sync.

To make sure Push is enabled, where new items get sent to your device as they arrive you need to alter the Schedule,  which is an option on the main Menu in ActiveSync. Pick your options to suit your preferences. I would stongly suggest that you make sure 'use the above settings when roaming' is NOT ticked unless you have deep pockets!

As ever, I hope this helps somebody out there.

SharePoint Website Schematic

I find myself drawing the same diagram over and over again in meetings to explain how SharePoint sites relate to IIS web sites, how managed paths and alternate access mappings fit and why you need to extend the SharePoint web application if you want more than one authentication provider.

After some of my colleagues pestered me to draw it again, I decided to create an electronic version, and since everybody seems to find it so useful I thought I'd post it here as well.

Rather than talk about it, I'm going to post it 'blind' and invite comments from you, my enthusiastic audience as to how easy it is to understand and any errors or omissions there may be.

With a little luck, somebody will find it useful!

SharePoint Website Architecture

Site Policies and FBA in SharePoint: Update

My apologies to Craig, who posted a comment to my earlier post about our FBA problems and I didn't notice until today.

To update you all on the situation, the fault is still with Microsoft and I have not yet received a hotfix.

However, for anybody considering FBA in their deployment, I would not let this issue stop you. There are two reasons I say that:

  1. Normally with FBA you would extend the web application in question, having both FBA and Windows authentication available on the same content via different URLs. This makes your life easier with things like indexing and management.
  2. The workaround I detailed is a good temporary solution to the problem with only minimal impact on the user experience (in that certain options are offered which may not work too well in Office when using FBA).

Hopefully this answer's Craig's question and assuages any doubts about the wisdom of deploying FBA in your SharePoint solution.

Workflow and SQL Error: Update

I posted last week about a couple of issues we were experiencing with SharePoint. I made some traction on the Workflow History issue at the end of last week and the revelation was pretty far-reaching, so I'm posting again.

It turns out that the stuff I said about systemupdate was wrong... up to a point.

There is a bug with systemupdate and triggering events, but it's not the one we thought it was! It turns out that the behaviour we are seeing is correct - systemupdate is supposed to trigger events, just not update things like the modified by and last updated columns. It's actually the behaviour within a workflow which is at fault, in that events aren't being triggered when they should be.

I had a chat with our developers about this and they told me that there are plenty of articles on the web suggesting that systemupdate is the way to update an item in a list without triggering events. Don't do it! I was told by Microsoft that whilst the fault is not high on the list because there is a workaround (which I will list in a moment), it will be fixed. At that point, anybody who is using systemupdate expecting events not to fire will get a shock.

The MSDN documentation for system update is pretty clear:

When you implement the SystemUpdate method, events are triggered and the modifications are reported in the Change and Audit logs, but alerts are not sent and properties are not demoted into documents.

The explanation as to why events don't fire is:

When you used in other places such as windows/console app, another workflow or webparts, you are not seeing the event trigger the workflow, this is due to the Workflow runs on separate threads from the main thread, so we cannot fire up the workflow and simply quit. Quitting an app before the async worker threads are finished causes those threads to simply abort, and in the case of workflow, nothing will appear to have happened.

And the fix:

Currently, all standalone applications must call spsite.workflowmanager.Dispose(). This call waits for the threads to complete and causes workflow to go into an orderly shutdown.

And the solution to the problem of wanting to not trigger events? Well, it looks like the method I described in my earlier post is the way to go.

Useful .Net Search and Replace tool

I need to update something like 300 web.config files today, with the same change in each. I turned to my old friend Google for his recommendations and up popped a magnificent free tool from Nodesoft.

Why magnificent? It's advanced mode allows you to specify start and end tags and update everything in between. That's fantastic for dealing with big chunks of web.config files! Better yet, give it a file mask and a path and it will run through the entire folder tree if you like. Update whole servers in seconds.

I just wish I'd found it sooner...

Workflow History and SQL Error

When trying to view an item in a list which has workflows run against it, you get an error:

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

Problem Background

Trying to explain the exact nature of our configuration in this case would break many people's heads. This, therefore, is a bit of a simplification.

We have a custom webpart which allows users to log an enquiry. We create an item in a list with the enquiry details, and send an email to the account responsible for dealing with those enquiries. A copy of the list item is created in another list (we'll leave out the why and wherefore of that for now). The two copies must be kept in sync. More details on that later.

Those enquiries must be closed within 30 minutes. If not, an escalation email is sent. An enquiry is closed if a particular column changes value. To ensure the two lists are kept in sync, when an item is changed a workflow is triggered. If the column we care about has changed we sync up the item in the other list.

The escalation process is a timer. It checks the items and sends emails. It updates a column with the time of the last email sent so we can repeat the process every 30 minutes.

What we found was that the enquiries weren't being closed for a few days and in that time we could then not access the enquiry item at all via the web interface (although datagrid view still worked!). We saw the error at the top of this post.

The Root of the Matter

This fault is currently with our Microsoft Support team and they are working through it. I do, however, have enough knowledge and understanding of why the fault occurred to explain it, and a few dirty hacks to avoid it.

The reason we can't access the items is because when SharePoint pulls up the item for edit/view it checks the Workflow History for that item. If there are more than about 200 entries for that item in the Workflow History list, we get the SQL query error and boom! That's the long and short of it.

The deeper question is why? More importantly, why do we have over 200 workflows running on the item?

Workflow History first. The Workflow History list is a hidden list which does exacly what it says on the tin. Items are created each time a workflow runs. It turns out that items in the Workflow History list have a time-to-live and that time is 60 days. That means that any item in the list will automatically be deleted after 60 days. With roughly a 200 item limit before you hit trouble that means about 3 workflows per list item per day is your maximum.

Personally, I think that is a scalability issue. I can envision a scenario where we might want to run that many workflows by design, perhaps more.

Back to the plot. I suspect you're sitting there thinking that in our case, having that many workflows run is bad design or a fault. Well, you're not wrong, although you're not quite right either.

We knew when we built the workflow that we had to avoid circular references and update the lists as little as possible. There is code to make sure that changes made by the workflow itself are not reflected back, and if the change is not the column we care about then the workflow exists cleanly.

We also knew that because the timer job updates a different column in the list item, that would trigger the change event on the list item, running the workflow. As a result the timer performs a system update on the list item which should not trigger events (and indeed does not when we have used the method elsewhere).

What this means is that we actually have two problems:

  1. The system update method when used in our timer is not working correctly and events on the list item are being triggered. This means that the workflow is running too often.
  2. The issue with Workflow History means that we very quickly hit the 200 item limit and meet our end with the SQL query error.

A Legion of Dirty Hacks

As I write, these issues are with Microsoft Support who are ably working to resolve them. In the meantime, we have made the problem go away with two approaches, both of which I regard as dirty hacks.

The Workflow History Conundrum

Whilst investigating this problem I came across a discussion on the TechNet support forums. Ironically this was coming at the same problem but from a wholly opposite angle, whereby people wanted to keep items in the Workflow History list for longer!

What I found in that list was a post by Fred Morrison containing a PowerShell script. I am re-posting it here for completeness in case the forum disappears, but all credit to Fred for this - I didn't write it!

   1: # SPAdjustAutoCleanupDays.ps1
   2: # Author: Fred Morrison, Senior Software Engineer, Exostar, LLC
   3: #
   4: # Purpose: Adjust SharePoint Workflow Association AutoCleanupDays value, where necessary
   5: # on all workflow associations for a specified List.
   6: #
   7: # Parameters:
   8: # siteName - The SharePoint Site to look at
   9: # listName - The SharePoint List to look at
  10: # newCleanupDays - The number of days to set the workflow association AutoCleanupDays value to, if not already set.
  11: #
  12: # Example call: SPAdjustAutoCleanupDays http://workflow2/FredsWfTestSite FredsNewTestList 180
  13: #
  14: # following makes it easier to work with SharePoint and also means you have to run this script on the SharePoint server
  15: [void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | Out-Null
  16: # capture command line arguments
  17: $siteName = $args[0] # ex: http://workflow2/FredsWfTestSite/
  18: $listName = $args[1] # ex: FredsNewTestList
  19: [int] $newCleanupDays = [System.Convert]::ToInt32($args[2]) # ex: 1096
  20: Write-Host $siteName
  21: Write-Host $listName
  22: Write-Host $newCleanupDays
  23: # get a reference to the SPSite object
  24: $wfSite = New-Object -TypeName Microsoft.SharePoint.SPSite $siteName
  25: [Microsoft.SharePoint.SPWeb] $wfWeb = $wfSite.OpenWeb()
  26: Write-Host $wfWeb.ToString()
  27: # get a reference to the SharePoint list we wish to examine
  28: [Microsoft.SharePoint.SPList] $wfList = $wfWeb.Lists[$listName];
  29: Write-Host $wfList.Title
  30: [Microsoft.SharePoint.Workflow.SPWorkflowAssociation] $wfAssociation = $null
  31: [Microsoft.SharePoint.Workflow.SPWorkflowAssociation] $a = $null
  32: [int] $assoCounter = 0
  33: [string] $message = ''
  34: # Look at every workflow association on the SPList and make sure the AutoCleanupDays value is correctly set to the desired value
  35: for( $i=0; $i -lt $wfList.WorkflowAssociations.Count; $i++)
  36: {
  37: $a = $wfList.WorkflowAssociations[$i]
  38: [string] $assocName = $a.Name
  39: Write-Host $a.Name
  40: if ( $a.AutoCleanupDays -ne $newCleanupDays )
  41: { 
  42: $oldValue = $a.AutoCleanupDays 
  43: $a.AutoCleanupDays = $newCleanupDays
  44: # save the changes 
  45: $wfList.UpdateWorkflowAssociation($a) 
  46: $message = "Workflow association $assocName AutoCleanupDays was changed from $oldValue to $newCleanupDays"
  47: }
  48: else
  49: {
  50: $message = "Workflow association $assocName AutoCleanupDays is already set to $newCleanupDays - no change needed"
  51: }
  52: Write-Host $message
  53: }
  54: Write-Host 'Done'
  55:  

I simply ran that script on our system, setting the value for newCleanUpDays to 1. I waited a day and voila! All the list items were now accessible. Note that, as repeated in the forum discussion time and again, messing about with this is not a good idea. I simply have no choice right now.

The Timer Incident

It was all very well fixing the Workflow History list, but we really shouldn't be seeing all those workflows in the first place. For some reason, our method of updating the list item from the timer, whilst being the official approach, triggered the workflow anyway.

To the rescue, a method we found on the blog of Paul Kotlyar. In that post, Paul talks about disabling event firing for the list item to ensure that no events get triggered. Why do I think this is a hack? Because the functionality is not normally found in workflows and timers - the method is part of SPEventReceiverBase.

Where Do We Go From Here?

Right now, I have support cases logged with Microsoft and engineers are working on the matter. We've already been via the SQL team, who looked at the original query that triggered the whole shebang, and they have returned an updated query for the SharePoint guys to look at. We also need to get to the bottom of a 'correct' way of updating list items without triggering events. As soon as I get a resolution from Microsoft, I will let you know.

Problems with Site Policies and FBA in SharePoint 2007

If you are using Forms Based Authentication and try to access Site Policies you may well find that you get an Access Denied response. If you do, this post will help you!

I've been meaning to post this for a while because I'm sure it may help somebody. As usual, it's been pushed back and back until now I finally have some time. I also have another, workflow-related post on another problem which will follow shortly.

Problem Background

We have been working on a SharePoint solution which involves a large number of web applications, each of which is hosting an internet-facing site using SharePoint Publishing. Each of these web applications uses forms-based authentication (FBA) with the AD provider in order to provide a better user experience.

Why a large number of web application? The format of the required urls, and the number of urls per site led us down that path.

Why only FBA and not extend the web app to have one with FBA and one with windows auth? There are enough web applications already, and since we would almost never use the windows auth ones why load up the system?

One of the roles of these site is to allow end users to upload files. However, our customer wants the document library that is used for upload to automatically purge files after a given time.

No problem, site polices will do that...

The Problem Itself

Unfortunately, when you authenticate as the site collection administrator, or a member of the site owners group, or a farm admin, you can't access the Site Policies administration pages within the site collection hosted in the web application that uses FBA.

We spent a while checking group memberships and trying different users, and we established that toggling between FBA and windows auth would make the problem go away. We then called in the Microsoft Support guys and continued to investigate. Those guys deserve a mountain of praise as they carefully replicated the fault and kept us firmly in the loop whilst working on the call.

Using Gary Lapointe's stsadm extensions we were able to examine the rights granted to the admin user when the site used FBA. The gl-enumeffectivebaseperms command showed interesting results.

stsadm -o gl-enumeffectivebaseperms -url <url>

FullMask

stsadm -o gl-enumeffectivebaseperms -invert -url <url>

UseClientIntegration, FullMask

The first command tells me what rights I have. The second tells me what rights I don't. Note the confusion!

It turns out that when using FBA, the rights are not correctly applied for full control. The Microsoft team provided a workaround, in that enabling Client Integration fixes the application of rights. You either do this using stsadm to reset the authentication provider:

stsadm -o authentication -rul <url> -type forms -membershipprovider <membership provider> -rolemanager <role manager> -enableclientintegration

Or through the central admin. We have lots of sites, so we do it through the stsadm route.

The Long-Term Solution

I have been informed by Microsoft Support that this has now been logged as a bug with SharePoint and that they are working on a fix. As soon as I know more, I will post again.

A great article on handy SharePoint controls

I don't know about you, but I always mean to gather various bits of knowledge into one place, but just like tidying my filing at home, I never quite get around to it. Fortunately for me, Chris O'Brien is a bit more organised and in my ever expanding blogroll today I saw a great article about really useful SharePoint controls to use in custom pages for that handy bit of functionality.