The 2009 SharePoint Conference was a total blast.  For those who were not able to attend, Microsoft also announced PowerPivot.  With PowerPivot, IT Managers can empower business users to create and share Excel 2010 workbook applications through SharePoint 2010.

Rob Collie, one of the founding engineers behind PowerPivot has started a blog here and has announced the "Great Football Project” as a non-demoware PowerPivot solution, from start to finish where he’ll be taking a hardcore BI project from his past and re-implement it using PowerPivot.

I myself will be diving deep into PowerPivot over the next few weeks, and will detail all I can as the weeks progress.

Until then, be sure to visit the following links to get an overview of PowerPivot and Rob Collie

• PowerPivot
• Microsoft SQL Server PowerPivot for Excel 2010
• PowerPivotPro featuring Rob Collie

Cheers!

– Keith

Wow, just checked and saw this, I’m now listed in the Zune Marketplace.

http://social.zune.net/artist/Keith-Richie/79d40c00-0600-11db-89ca-0019b92a3933/albums

One thing that isn’t listed is my La famille du solénoïde release, But of course it’s available on iTunes here, as with the rest of my releases.

<sigh> I plan to get back to my music too, I’ve just had a very busy past couple of years, and no time or inspiration for it.

Anyways, just wanted to note the whole listing in the Zune Marketplace real quick, cause it was way too exciting!

- Keith

Head up!   The update to the SharePoint Service Pack 2 expiration date issue is now available.

Direct download links:

• x86: http://download.microsoft.com/download/2/F/5/2F51AB71-1325-49D2-9CB9-18DEC4780E99/office2007-kb971620-fullfile-x86-glb.exe
• x64: http://download.microsoft.com/download/5/B/B/5BBD34A9-C528-42B0-8A5F-9A8997B25C32/office2007-kb971620-fullfile-x64-glb.exe

For more information on the original issue, see the following posting on the SharePoint Team Blog:

Attention: Important Information on Service Pack 2

Cheers!

– Keith

Since I started my new job, I’ve been struggling to find topics to discuss that have not already been discussed before. 

I’m not the kind of guy to just re-hash what someone else has said unless I can add additional value to it.  When I do that, I always start out with “First read what so and so has said about x”.  There are too many of the content thieves out there, and they really provide no real value back to the community.   They’re just going for self gratification in subscribers, etc.  I also don’t like just blasting links as I discover them just to make a blog post, unless they are significant.  Besides, if I come across a cool article these days, I just dump it on twitter.  What I’m saying, is that when I post something, I want to really provide extreme value.

What I’ve realized, is that the things I do want to blog about require too much time to sit down in one setting to post, and thus I never get them done, or take the time to finish them and thus, this blog just gets extremely stale.   Also, I’ve noticed a lot of blog postings recently on very very short samples, about things I figured a lot of people would know.   But I’ve had to take a step back and realize that not all my followers know all the gritty details.

So…From now on, I’m going to blog about just what’s on my mind, what I learned, what I might be able to share.   There may be some overlap in things that were already posted, but I’ve got to stop worrying about “It’s been said before” because I have to realize that my view/insight into the problem/solution may in fact add value not otherwise stated.

- Keith

Maurice and I have been friends for quite awhile. While at Microsoft, he and I collaborated while working through critical SharePoint customer issues, debated on bugs, discussed SharePoint tools, etc.  After both of our departures from Microsoft, we continued to keep in contact sharing ideas and thoughts about the product.   I was excited when I came on board with ShareSquared to not only be working with my friend again, but also to expand my horizons and work with a great bunch of talented folks.

Maurice has just completed the Microsoft Certified Masters training for SharePoint Server 2007 and was awarded the certification along with a small group of elite personnel. Now ShareSquared has a Microsoft Certified Master for SharePoint Server 2007 in our group, and I’m extremely happy for my company and friend.

Maurice did a great write-up on his perspective of the program here, and I encourage you to give it a read if you are considering this for your future.  As well, you can check out Spence Harbar’s write-up here.

Congratulations to the newest Certified Masters for SharePoint Server 2007!

– Keith

Today I’m happy to announce that I have joined the great team at ShareSquared The SharePoint Solution Experts, where I’ll be working with long time friend and cohort Maurice Prather as a Senior SharePoint Architect.

The team at ShareSquared is composed of expert SharePoint architects and developers who are seasoned computer science professionals with a thorough understanding of proper software construction, design patterns and best practices.  I’m so proud to be joining this team.

I want to thank ALL my friends for their support over the past couple of months while I sought ought just who I wanted to be when I grew up .  My new position will allow me to utilize my previous skills as well as further extend myself into areas I had only dreamed about before.

Here’s to new adventures in 2009!

- Keith

I had placed my 3.0 version of SPSiteBuilder (For WSS 3.0/MOSS 2007) on codeplex previously as it was referenced in a book by good friend and SharePoint MVP Ben Curry.  Unfortunately, I forgot to get the code up there   And the reminder for me to do it got lost in the plethora of tasks I needed to accomplish, and therefore the project was removed.  I’m happy to say, I finally took care of that today and you can now get SPSiteBuilder (with source) from http://www.codeplex.com/spsitebuilder

The purpose of the SharePoint Site Builder (SPSiteBuilder) is to assist customers with performing SharePoint Site Collection build outs for testing purposes. Primarily for load testing, etc. This sample source can be compiled to produce a tool that will allow you to easily create multiple hierarchies of site collections.

My apologies to those who followed the link before and found nothing

-Keith

As I prepare for 2009, I thought it would not be a bad idea to go ahead and post my resume here as I seek out new employment opportunities.

Serious inquiries please email me through this blog.

Thanks all and a happy new year!

Resume for Keith Richie

Objective

Seeking a challenging development position where I can continue to expand upon my knowledge of SharePoint and .NET related technologies.

Experience

Senior Software Engineer

January 2007 – December 2008

Barracuda /A Division of Mindsharp/English,Bleeker & Assoc.

• Ported the company’s existing product “DeliverPoint” from a SharePoint V2 codebase to utilize WSS 3.0/MOSS 2007. DeliverPoint is an enterprise class application primarily focused with User Management related operations such as Cloning/Deleting/Transferring account principles within a SharePoint farm.
• Updated .NET/C# code for use of SharePoint V3 Object Model.
• Implemented SharePoint Farm Crawler to alleviate problems with memory pressure issues rising from improper disposal of SharePoint objects. Also allows other developers to hook into the engine, to receive information as it is discovered in SharePoint.
• Implemented Active Directory crawler to provide robust group expansion for the DeliverPoint product.
• Design tables/Create necessary T-SQL stored procedures for DeliverPoint backend database.
• Responsible for design/implementation of new system features.
• Mentor the Barracuda Support Staff on engagements with customers and assist with customer support issues.

Senior Support Consultant

February 2006 – January 2007

Microsoft – Alliance/PFE-DDS

• Supporting enterprise customers with SharePoint.
• Developed/Enhanced the SharePoint Utility Suite for WSS 2.0/SPS2003 which became the number 1 download on the Microsoft Web Component Directory, used by the Product Group to assist customers during Beta for preparation of systems for MOSS deployments (Used to analyze and detect problems on WSS 2.0/SPS2003)
• Provided technical and development assistance to members of the Escalation Services and Product Support Services members for case assistance
• Worked with the SharePoint Product Groups to diagnose issues, report bugs, and called upon frequently to investigate issues for some of the largest Microsoft customers.

Escalation Engineer

March 2000 – February 2006

Microsoft – Escalation Engineer for SharePoint Portal Server and Windows SharePoint Services.

As an Escalation Engineer (EE) we represent the last line of support for the most demanding of issues related to SharePoint Portal Server and Windows SharePoint Services

As an EE I was tasked with:

• Resolving the most the most technically and politically challenging customer issues
• Engaging and being a resource to the larger SharePoint PSS group.
• Researching, diagnosing, and writing code to further diagnose and resolve customer’s issues.
• Employing advanced tools and diagnostics to analyze source code.
• Making recommendations to the development team on improvements to product
• Isolating defects to the faulting line(s) of code.
• Writing tools to further analyze problem or improve the overall SharePoint experience.

All of these things within very compressed timeframes. What this really means is on a typical day the vast majority of my time is spent mentoring, debugging and researching issues I am currently involved in, and overall contributing to the success of those around me. By using knowledge of software design and programming in conjunction with general product knowledge, it allows people in our group to bridge the gap between the typical end to end support troubleshooting methodologies and the very focused, specific code level knowledge of a developer.

Software Developer

March 1996 – March 2000

Custom Credit Systems

• Developed tools and enhancements to company’s in house scripting language for custom software development of commercial financial systems.
• Maintain systems after development and work with customers to ensure system uptime.
• Travel to customer sites and work with them on implementation and design of custom solutions

Software Developer

March 1994 – March 1996

Creative Programming

• Maintain and develop custom user interface library for DOS written in C/C++
• Developed event driven designer for generating code to utilize user interface library
• Converted code base to run on UNIX systems utilizing termcap.
• Maintained and wrote fixes, and service packs for UI library

Interests

I led a virtual team of Microsoft Software Support engineers in producing and releasing a collection of SharePoint diagnostics and administrative tools called “The SharePoint Utility Suite”
This suite of tools (which included SPUserUtil and SPSiteManager) advances and allows our customers to achieve deployment and ROI goals by filling in the gap of necessary administrative tools between releases of the product

Certifications and Awards

Certification: MSCD Visual C 6.0
Date Completed: 7/1/2000

Microsoft MVP – Windows Server: Windows SharePoint Services July 2007-2008

References

References are available on request.

In my previous post Wherefore Art Thou Author? I discussed a problem whereby removing a user from a site collection will cause a “User not found exception” when trying to access the Author property of any web they may have created.

In this post, I’ll provide a supportable solution to resolving this without having to revert to modifying the SharePoint content databases directly.

The following code can be used to reset the author of a SharePoint site:

// BEGIN SPResetAuthor

using System;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace SPResetAuthor
{
  class Program
  {
    static void Main(string[] args)
    {
      if (args.Length < 2)
      {
        string sFormat = "{0,5}{1,-20}{2}\n";
        StringBuilder sb = new StringBuilder();
        sb.Append("\nSYNTAX: SPResetAuthor.exe   \r\n");
        sb.Append("\nwhere:\n\n");
        sb.AppendFormat(sFormat, " ", "", "The Web URL where"
            + " the author needs to be reset");
        sb.AppendFormat(sFormat, " ", "", "The Login Account"
            + " for the new author, i.e. DOMAIN\\USER");
        Console.WriteLine(sb.ToString());
        return;
      }

      // Open the site collection
      Console.WriteLine("Opening site collection for: {0}",args[0]);
      SPSite site = new SPSite(args[0]);

      Console.WriteLine("Opening site (web)...");
      // Open the web via the URL passed in
      SPWeb web = site.OpenWeb();
      Console.WriteLine("Site (web) title: " + web.Title);

      Console.WriteLine("Resetting Author to: {0}",args[1]);
      SPUser user = web.EnsureUser(args[1]);
      web.Author = user;
      web.Update();

      Console.WriteLine("Author for site {0}, reset to {1}", args[0], args[1]);
      Console.WriteLine("Author ID: " + web.Author.ID.ToString());
      Console.WriteLine("Author Name: " + web.Author.Name);
    }
  }
}

// END SPResetAuthor

To build this, simple execute the following at the same location where you save the source.

%WINDIR%\Microsoft.NET\Framework\v2.0.50727\csc /target:exe /out:SPResetAuthor.exe SPResetAuthor.cs
/reference:"%COMMONPROGRAMFILES%\Microsoft Shared\web server extensions\12\ISAPI\Microsoft.SharePoint.dll"

The syntax is as follows:

SYNTAX: SPResetAuthor.exe  

where:

                 The Web URL where the author needs to be reset
              The Login Account for the new author, i.e. DOMAIN\USER

When executed, the results should resemble the following:

C:\Dev\SPResetAuthor>SPResetAuthor.exe http://krichiedev/deadauthorsite krichie\user1
Opening site collection for: http://krichiedev/deadauthorsite
Opening site (web)...
Site (web) title: DeadAuthorSite
Resetting Author to: krichie\user1
Author for site http://krichiedev/deadauthorsite, reset to krichie\user1
Author ID: 13
Author Name: Test User One

Hope this helps!

– Keith

I’ve been trying to find the definitive Microsoft document/KB on this for a while.  The information is spread out across various Microsoft documentation, and personal blogs, but the personal blogs don’t show any official content to back it up….So, I thought I’d share along with the associated official documents links to back it up.

Depending on the configuration, I’ve seen instances where my apps result in the following error message in the Application Logs of the SharePoint server from which they are run.

SQL database login failed. Additional error information from SQL Server is included below.

Login failed for user DOMAIN\appaccount'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Initial rights necessary for Console Applications accessing the SharePoint Object Model

The SharePoint SDK has the following statement in regards to creating Console Applications:

“Users must be administrators on the computer where the script is executed in order to run a console application in the context of Windows SharePoint Services.“

See “How to: Create a Console Application” from the Windows SharePoint Services 3.0 SDK.

Additional rights necessary for Console Applications accessing the SharePoint Object Model

Along with that, you also need to ensure the account has appropriate rights on the sites you want to access.  The easiest way to accomplish this, is to ensure the account is granted a Full Control policy for all web applications you want to access.  This alleviates the need to individually grant rights to each site.  Also, if you want to access any farm level objects/properties, you also need to ensure the account is in the Farm Administrators Group.  That’s missing from the above link.

What’s also missing, and the most important part, is that the account ALSO has to have read/write privileges on the content databases themselves in SQL.

If your account was running under the Server Farm Account (See  Plan for administrative and service accounts (Office SharePoint Server) http://technet.microsoft.com/en-us/library/cc263445.aspx ), you would not have this issue, as each time a new content database is created, SharePoint automatically grants this account the appropriate permissions on the content database. 

Then why don’t I follow this approach?

Allot of my work requires that my account not only has appropriate SharePoint rights, but also other rights within the organization.  It’s not always right to grant the Server Farm Account additional access to resources, to ensure Least-Privilege administration requirements.

In a situation where you need a completely separate account, you have to take extra steps to ensure your application will work, and then repeat this process when a new content database is added.  This is noted in KB 935751 as follows:

Error message when you try to iterate through Windows SharePoint Services site objects or SharePoint Server 2007 site objects by using the SharePoint object model: “Unhandled Exception: System.IO.FileNotFoundException”

Note: In my particular scenario, I was not getting this exception, but rather a SqlException with the following details:

Exception occured: Cannot open database “WSS_Content” requested by the login.
The login failed.
Login failed for user ‘DOMAIN\cappaccount’.

Test Scenario

To see this in practice, lets start with the following server setup, code, and directions from the original “How to: Create a Console Application” from the Windows SharePoint Services 3.0 SDK.

The test environment consists of the following 2 machines:

CDBPROBDC1

• This machine is configured to be a domain controller for a new forest called cdbprob.dom
• SQL 2005 is configured on this machine as well.
• The following accounts were configured for this setup:
  • CDBPROB\SQLService
    • This account was used as the service account for all SQL Services
  • CDBPROB\SPService
    • This account was used during the SharePoint Configuration Wizard, and thus is the Application Pool identity for the Central Administration Site
  • CDBPROB\SPAppPool1
    • This account was used as the Application Pool identity on the first content web application
  • CDBPROB\SPAppPool2
    • This account was used as the Application Pool identity on the second content web application
  • CDBPROB\CAppAccount
    • This account was used for the context of running the console application, and was added to the CDBPROBMOSS\Administrators local group
  • CDBPROB\SCAdmin1
    • This account was used for the site collection created within the first content web application
    • The Content Database was named WSS_Content_80
  • CDBPROB\SCAdmin2
    • This account was used for the site collection created within the second content web application
    • The Content Database was named WSS_Content_2

CDBPROBMOSS

• This machine is configured to be part of the cdbprob.dom domain, and has MOSS+SP1 integrated installed on it.
• CDPROB\SPService used for the account during the SharePoint Configuration Wizard and thus is the Application Pool identity for the Central Administration Site)
• CDBPROB\CAppAccount is added to the CDBPROBMOSS\Administrators local group

A review of the Logins in SQL will show the following User Mappings:

• CDBPROB\SPService
  • db_owner for WSS_Content_80 and WSS_Content_2 (This is automatically set by SharePoint)
• CDBPROB\SPAppPool1
  • db_owner for WSS_Content_80 (This is automatically set by SharePoint)
  • No Mapping for WSS_Content_2
• CDBPROB\SPAppPool2
  • db_owner for WSS_Content_2 (This is automatically set by SharePoint)
  • No Mapping for WSS_Content_80

The Source for console application is as follows:

// BEGIN EnumSiteCollections
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;

namespace EnumSiteCollections
{
  class Program
  {
    static void Main(string[] args)
    {
      foreach (SPWebApplication wa in SPWebService.ContentService.WebApplications)
      {
        foreach (SPSite sc in wa.Sites)
        {
          SPWeb web = null;
          try
          {
            Console.WriteLine("Get sc.Url...");
            string scUrl = sc.Url;
            Console.WriteLine("Do something with site at: {0}", scUrl);
            Console.WriteLine("Opening root web for site collection...");
            web = sc.RootWeb;
            Console.WriteLine("Get web.Title...");
            string webTitle = web.Title;
            Console.WriteLine("Get web.Url...");
            string webUrl = web.Url;
            Console.WriteLine("Web title: {0} url: {1}", webTitle, webUrl);
          }
          catch (Exception e)
          {
            Console.WriteLine("Exception occured: {0}\r\n{1}", e.Message, e.StackTrace);
          }
          finally
          {
            sc.Dispose();
            if (web != null)
              web.Dispose();
          }
        }
      }
    }
  }
}
// END EnumSiteCollections

Test Results

Running the application under the context of the account CDBPROB\SPService results in no errors

This is because this is the Server Farm account, and all appropriate rights are automatically set by SharePoint.

Running the application under the context of the account CDBPROB\CAppAccount results in a System.NullReferenceException. 

The reason being, the WebApplications collection cannot be accessed unless the account is a member of the Farm Administrators group.  Therefore, you need to add the account to the Farm Administrators group as noted in “Additional rights necessary for Console Applications accessing the SharePoint Object Model” above.  (Note: The Server Farm account (CDBPROB\SPService in this example) is automatically added to the farm administrators group when the configuration wizard is run.)

When the account is added to the Farm Adminstrators group, SharePoint adds the login to SQL, and grants it db_owner rights on the Configuration database.

Now running the application under the context of the account CDBPROB\CAppAccount results in the SQLException I note above when trying to access the Title property from the rootweb.

At this point, if you granted db_owner rights for CDBPROB\CAppAccount to the content databases, it’s still not enough as a Microsoft.SharePoint.SPGlobal.HandleUnauthorizedAccessException will be raised, because the account does not have any rights to the sites in the web application.  You have to allow the account sufficient rights on the sites you want to access (thus my recommendation for a Full Control policy)

If you add, for instance, a Full Control policy to the web application for the users, but don’t grant db_owner rights on the content database, you will still recieve the SQLException as noted above.

Proper configuration

In order to properly have Unrestricted access via SharePoint Object Model from Console Applications you have to ensure you do one of the following:

Use the Server Farm Account

Using this account, SharePoint takes care of the details to ensure the account has proper access.

Ensure proper rights are granted for your custom account

If you need a custom account for your applications to run under, then the following four steps must be followed to allow the account appropriate access.

• The account must be part of the local administrators group on the server you are running from
  • Per “How to: Create a Console Application” from the Windows SharePoint Services 3.0 SDK
• The account must be a member of the Farm Administrators Group to access Farm Level objects
  • If you don’t need to access farm level objects, this isn’t necessary
• The account must be granted appropriate rights for the sites you wish to access
  • Granting the account a full control policy on each web application is the easiest if you need to access all data
• The account must be granted read/write permissions on each content database
  • The easiest thing to do, is grant the db_owner role to the account for each content database, just like the Server Farm account is.

If your scenario does not allow you to have your account granted db_owner rights to the content databases, you’ll have to rely on the Server Farm account..but otherwise, this is my recommendation until later debunked, corrected, etc

I’ll be following up to this article, with a script/app that will enumerate all existing SharePoint content databases, and ensure that a specified account has appropriate rights…to eliminate the manual step process.

Hope you find this information useful!

- Keith