Unrestricted access via SharePoint Object Model from Console Applications

[News! I’ve kicked off a crowdfunding campaign for the launch of my next album Xenogenesis Volume 1: Dawn!  Want to be part of it and have a chance to have your name in the liner notes and get an autographed copy? Be sure to check it out by clicking right here!]

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