Using SharePoint 2007 and SharePoint 2010 with Encrypted Databases

With SQL 2008, a new facility is provided called "TDE" or "Transparent Data Encryption". This is sometimes required by clients whose corporate governance rules require files in a filesystem to be encrypted. What do you have to do to get this working with SharePoint 2007 or SharePoint 2010?

Nothing!

As the name of the feature suggests, you simply have to set it up on the SQL Server side (as per http://technet.microsoft.com/en-us/library/bb934049(SQL.100).aspx), and your underlying database files (and SharePoint Content) and any backups thereof will be encrypted without any extra effort on your part.



DDK

LINQ to Objects - Performing a wildcard (LIKE in SQL) match between 2 different lists (aka Converting For Loops to LINQ queries or a Cool Feature of Resharper)

We'll start with an example. How would I get a list of any items in the "letterList" List below that matches (ie Contains) any of the numbers in the "numbersList" List below?

var letterList = new List<string>() { "A1", "A2", "A3", "A4", "B1", "B2", "B3", "B4", "C1", "C2", "C3", "C4"};

var numberList = new List<string>() { "1", "2", "3" }; 

We could do this in a looping fashion, or we could use LINQ to perform the query in a more declarative fashion.

For loop solution:

[TestMethod]
public void TestForEach()
{
    //We want all items in the letterList that wildcard 
    //match numbers in the numberList. The output for this example should
    //not include any items in the letterlist with "4" as it is not in the 
    var letterList = new List<string>() { "A1", "A2", "A3", "A4", 
        "B1", "B2", "B3", "B4", "C1", "C2", "C3", "C4"};
    var numberList = new List<string>() { "1", "2", "3" };
    var outputList = new List<string>();

    foreach (var letter in letterList)
    {
        foreach (var number in numberList)

            if (letter.Contains(number))
            {
                outputList.Add(letter);
            }
    }
}

How would we do this in LINQ?
One of the problems is that the LINQ Contains method only matches one value at a time (not Numbers 1,2,3 at the same time). We also can't use a normal LINQ equijoin as the LINQ join syntax doesn't support wildcard matches.

The answer is to do the below:

[TestMethod]
public void TestForEachLINQ()
{
    //We want all items in the letterList that wildcard 
    //match numbers in the numberList. The output for this example should
    //not include any items in the letterlist with "4" as it is not in the 
    var letterList = new List<string>() { "A1", "A2", "A3", "A4", 
        "B1", "B2", "B3", "B4", "C1", "C2", "C3", "C4"};
    var numberList = new List<string>() { "1", "2", "3" };
    var outputList = (
        from letter in letterList 
        from number in numberList 
        where letter.Contains(number) select letter).ToList();
}

This effectively does a wildcard match between 2 different lists. When you look at it, it really is very similar to a SQL Server wildcard join - but just using a WHERE statement.

The simplest wayway to make a conversion like this is to use one of the new features of Resharper 5 - the "Convert Part of body into LINQ-expression" refactoring functionality. This will automatically convert the for each syntax to the declarative LINQ syntax. EASY!

DDK

Anatomy of an IT Disaster - How the IBM/SAP/Workbrain Queensland Health Payroll System Project Failed

There has been a lot of media interest in the failed SAP payroll project at Queensland Health recently. It has been termed as an "unprecedented failure of public administration''. Just today in the Australian Financial Review, it was stated that even the superannuation calculations have become a tangled web of manual overrides and inconsistency (due to the original payroll amounts being incorrectly calculated). There is also going to be an internal government hearing today to work out how this failure happened. Surprisingly though, the Queensland Minister for Health will apparently keep his job (as per the following news article in The Australian Newspaper http://www.theaustralian.com.au/australian-it/minister-keeps-job-despite-queensland-health-payroll-debacle/story-e6frgakx-1225886060838). Now disaster on such a large scale (like a large train crash) drew my curiosity and I just had to ask:

How did this massive project failure happen, and how did it go so wrong, so far,  for so long?

This blog article is something akin to "Air Crash Investigation" on TV - but from an IT software perspective. As the US philosopher George Santayana (1905) said - "Those who cannot remember the past are condemned to repeat it." - and I'd like to learn from such a systemic failure in the Australian IT context.


Project Statistics:
The project was large by anyones's measure:

More recently, blame has been levelled at problems sourcing from the management by the CorpTech Shared Services - as per this computerworld article:(http://www.computerworld.com.au/article/352346/corptech_called_account_shared_services_failing/).
I know some SAP developers who worked on the project and they had some explanations as to what the main reasons for failure. They bailed out themselves as they could see the trainwreck that would happen down the line. They identified that IBM wasn't the sole point of failure - they were simply the last company to try and come in and fix the mess.

The Queensland Government is now attempting to sue IBM even though it has signed the application off as satisfactory. In terms of fallout from the disaster, the 2 top people in Queensland IT have been sacked, and it is likely that CorpTech middle managers involved will be disbanded.

Problems with the Queensland Health Project (aka Project Post-Mortem):

1. [Project Management Issue] There was NO contingency plan (aka "Plan B") in place in case the payroll system went belly up (and it did). Way too much trust was put into the contractors to deliver a perfect, bug free result (no real-world software is 100% bug free) and not enough common sense was used to mitigate risks. 
2. [Project Management Issue/Testing and Reporting Issues] - Testing Plan and Numbers were fiddled (!) so the application passed testing criteria - According to the Courier Mail Newspaper article(http://www.couriermail.com.au/news/queensland/queensland-health-payroll-fallout-to-reshape-awards/story-e6freoof-1225885400871 - they (quite heinously) fiddled the numbers - "Instead of slowing the process, the project board agreed to revise the definition of Severity 1 and 2 defects – effectively shifting the goalposts so the project passed exit criteria."
3. [Project Management Issue] - There was no parallel run for the payroll between the WorkBrain System and SAP Payroll. This is what was recommended by SAP itself. I've had the SAP QA team come out to my clients and they do a pretty thorough job.
4. [Project Management Issue] - There should have been a Gradual Rollout (you can't do ANY large payroll system in one hit/using a "big-bang" approach).
5. [Architecture Issue] - The Architectural design is questionable. The Integration between the 2 systems is wrong - as WorkBrain rostering is writing directly to SAP (using flat files to pump data into SAP) rather than using the timesheets as the intermediary entry mechanism first. SAP Payroll systems are effectively bypassed by using WorkBrain and a bespoke system for payroll calculation and generation.
6. [Testing Issue - Government Due Diligence Issue]  - The system had been signed off by Queensland Government without proper checking on their part (they are subsequently trying to disavow themselves of this responsibility though the end decision to go live was theirs and done through their project board).
7. [Architecture and Project Management Issue] - Whether WorkBrain should have been used at all as it is a rostering application. Other States have just SAP systems and they operate acceptably.
8. [Project Management/Procedural Issue] A failure of a contractor [IBM] and CorpTech to Follow SAP's recommendations.
9. [Change Management Issues/Lack of training] - The training plans for this project were very limited and didn't take account of the difficulty in operating a new payroll system. 

DDK
[NOTE: I have no affiliations to IBM/Queensland Government/SAP]

Fix for WCF Client Proxy deserialization issue (related to svcutil.exe) when referencing Non-Microsoft Services (e.g. SAP services from SharePoint) - "Unable to generate a temporary class (result=1)."

When creating a client proxy for the SAP Service Registry (so I could dynamically set endpoints for my other WCF client calls), I had the following issue today when running a unit test:

Test method DDK.UnitTest.UDDIProxyTest.GetEndPointBasicTest threw exception: System.ServiceModel.CommunicationException: There was an error in serializing body of message findServiceDefinitionsRequest: 'Unable to generate a temporary class (result=1).

error CS0030: Cannot convert type 'DDK.BusinessService.UDDIRegistrySearchProxy.classificationPair[]' to 'DDK.BusinessService.UDDIRegistrySearchProxy.classificationPair'

This error is a result of issues with .NET commandline tools wsdl.exe or svcutil.exe incorrectly creating multidimensional arrays in the strongly typed proxy class (Reference.cs), as per screenshot below:

Cause:
This problem occurs when the svcutil.exe or the Web Services Description Language Tool (Wsdl.exe) are used to generate the client information. When you publish a schema that contains nested nodes that have the maxOccurs attribute set to the "unbounded" value, these tools create multidimensional arrays in the generated datatypes.cs file. Therefore, the generated Reference.cs file contains incorrect types for the nested nodes.

The problem and fix is described in the following kb articles:
http://support.microsoft.com/kb/891386 and 
http://support.microsoft.com/kb/326790/en-us

The fix is to basically change the multi-dimensional array in the Reference.cs file related to your service reference to a single dimension.
e.g.

classificationPair[] [] 

instead becomes

classificationPair[] 

Note that you will of course need to update all parameter references in the Reference.cs file to this multi-dimensional array, not just the original declarations.
DDK