Second Life of a Hungarian SharePoint Geek

February 14, 2012

Accessing external data from SharePoint timer jobs or from event receivers triggered by incoming mail

Recently we had an issue with accessing external list data. One of my colleagues wrote an ItemAdded event receiver for a document library, that updates the document metadata based on a CAML query run against an external list. The source of the external list is a simple database table. For this post assume it is the Meetings table in the Northwind database.

Everything was OK while we uploaded the documents “manually” – that means from the web UI. However, enabling incoming mail for the document library and configuring it to save mail attachments to the library caused us headache.

Documents sent as e-mail attachments were not handled as expected, instead, generated an exception on the external list query.

We received the following error message on a system configured for Kerberos:

Access Denied for User ”, which may be an impersonation by ‘domain\owsTimerAccount’.

In another configuration without Kerberos, using “simple” Windows authentication:

Access denied by Business Data Connectivity.

The stack trace was the same for the both cases:

Microsoft.SharePoint.SPListDataSource.GetEntityInstanceEnumerator(XmlNode xnMethodAndFilters)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

Of course, the owsTimerAccount user has permissions for BCS, the external list and for the external database.

I’ve tried the same code with elevated permissions, SharePoint and Windows impersonation, but that has no effect, except elevated permissions, where I’ve received just another exception:

Attempted to perform an unauthorized operation.

and the stack trace is a bit different as well:

at Microsoft.SharePoint.SPListDataSource.CheckUserIsAuthorized(SPBasePermissions perms)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

Note: If you would like to apply Windows impersonation, the original identity your process running with (in this case the account configured for the SharePoint 2010 Timer service) should have the Act as part of the operating system user right.

At this point we started to suspect that the source of the issue is somehow related with the SPTimerV4 context. I’ve created a timer job to access our external list just to validate this theory and found the very same results.

Fortunately, this forum thread led us to the solution: instead of running a CAML query against an external list, we should access and filter the external data directly using the BCS API.

The code examples below show the implementation steps for a timer job. If you need the solution for an event receiver, simply replace the name and signature of the Execute method to the one of your event receiver method.

Trace methods in code are included simply to verify the flow of the process.

First, we should add a reference to the Microsoft.BusinessData.dll assembly located at GAC.

Next, add the following namespaces to the code of your SPJobDefinition class:

  1. using System.Diagnostics;
  2. using Microsoft.SharePoint;
  3. using Microsoft.SharePoint.Administration;
  4. using Microsoft.SharePoint.BusinessData.SharedService;
  5. using Microsoft.BusinessData.MetadataModel;
  6. using Microsoft.BusinessData.MetadataModel.Collections;
  7. using Microsoft.BusinessData.Runtime;

I’ve defined the following “constants” in my code. These values should be altered to reflect the properties of your business data.

  1. private static readonly String _nameSpace = "http://sp2010";
  2. private static readonly String _externalList = "ExtCalendar";
  3. private static readonly String _externalCT = "Nortwind Calendar";
  4. private static readonly String _finderView = "Read List";
  5. private static readonly String _textFieldName = "cLocation";
  6. private static readonly String _dateFieldName = "dStart";

In the Execute method the test method is started using elevated permissions (site URL should be replaced):

  1. public override void Execute(Guid targetInstanceId)
  2. {
  3.     Trace.TraceInformation("Starting BcsTestJob execution");
  4.  
  5.     try
  6.     {
  7.         using (SPSite site = new SPSite("http://sp2010"))
  8.         {
  9.             using (SPWeb web = site.OpenWeb())
  10.             {
  11.                 Guid siteId = site.ID;
  12.                 Guid webId = web.ID;
  13.  
  14.                 SPSecurity.RunWithElevatedPrivileges(delegate()
  15.                 {
  16.                     using (SPSite siteImp = new SPSite(siteId))
  17.                     {
  18.                         TestExtList(site, "New York", new DateTime(2010, 9, 21));
  19.                     }
  20.                 });
  21.             }
  22.         }
  23.     }
  24.     catch (Exception ex)
  25.     {
  26.         Trace.TraceInformation("ERROR in BcsTestJob.Execute: '{0}', '{1}'", ex.Message, ex.StackTrace);
  27.     }
  28.     Trace.TraceInformation("BcsTestJob finished");
  29. }

In the TestExtList method we access the business data and filter the results. Of course, in this case we can’t use CAML.

  1. private void TestExtList(SPSite site, string textValue, DateTime dateValue)
  2. {
  3.     try
  4.     {
  5.  
  6.         using (SPServiceContextScope scope = new Microsoft.SharePoint.SPServiceContextScope(SPServiceContext.GetContext(site)))
  7.         {
  8.             BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);
  9.             IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
  10.             IEntity entity = catalog.GetEntity(_nameSpace, _externalCT);
  11.             ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;
  12.  
  13.             IFieldCollection fieldCollection = entity.GetFinderView(_finderView).Fields;
  14.  
  15.             IMethodInstance methodInstance = entity.GetMethodInstance(_finderView, MethodInstanceType.Finder);
  16.             IEntityInstanceEnumerator ientityInstanceEnumerator = entity.FindFiltered(methodInstance.GetFilters(), LobSysteminstance);
  17.  
  18.             int counter = 0;
  19.             while (ientityInstanceEnumerator.MoveNext())
  20.             {
  21.                 IEntityInstance entityInstance = ientityInstanceEnumerator.Current;
  22.                 if (((String)entityInstance[_textFieldName] == textValue) && ((DateTime)entityInstance[_dateFieldName] == dateValue))
  23.                 {
  24.                     counter++;
  25.                 }
  26.             }
  27.  
  28.             Trace.TraceInformation("BcsTestJob.TestExtList item count: {0}", counter);
  29.         }
  30.  
  31.     }
  32.     catch (Exception ex)
  33.     {
  34.         Trace.TraceInformation("ERROR in BcsTestJob.TestExtList: '{0}', '{1}'", ex.Message, ex.StackTrace);
  35.     }
  36. }

Applying this approach no exception is thrown. The external data can be accessed from the event receiver as well from the timer job, both of them running in the OWS Timer process context.

About these ads

1 Comment »

  1. Very interesting solution. It would be interesting to know what the BCS API is doing differently and in what cases one should not directly access the external list via CAML.

    Under what credentials is the code actually running with RunWithElevatedPrivileges? Usually that would be the application pool account, but in the SP4Timer service context?

    Comment by Dennis — February 15, 2012 @ 00:22


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 54 other followers

%d bloggers like this: