Second Life of a Hungarian SharePoint Geek

March 12, 2012

Solving the external data access security issue in the case of the OWS process

In the recent two posts I wrote about a security related problem we found when tried to access an external data source from the SharePoint 2010 Timer service process.

After presenting a workaround in the first part, last time I promised a real solution for the issue.

The key to the solution seems to be the information one can found on this MSDN page.

As it states, “The user security token is not available in every context. Without the security token, PassThrough security will not work.

As you may know, PassThrough is the standard and usually recommended authentication method to an external data source, but in this case we should switch to an alternative one.

The trivial solution would be to use the RevertToSelf authentication. Since this type of authentication is not recommended in a production environment, it is disabled by default. Before using it, you should enable it, for example with the help of PowerShell (see an example here).

After you enabled RevertToSelf, you can find the equivalent BDC Identity option in the list of the available authentication modes:

image

(To access the settings above, you should select the External Systems view at the administration of the Business Data Connectivity Service, then click the name of the external system you would like to manage, and then click the name of the external system instance.)

After you selected BDC Identity authentication mode, you can use this code to access the external system:

  1. using (SPSite site = new SPSite("http://sp2010"))
  2. {
  3.     using (SPWeb web = site.OpenWeb())
  4.     {
  5.         Guid siteId = site.ID;
  6.         Guid webId = web.ID;
  7.  
  8.         SPSecurity.RunWithElevatedPrivileges(delegate()
  9.         {
  10.             using (SPSite siteImp = new SPSite(siteId))
  11.             {
  12.                 // access external list here
  13.             }
  14.         });
  15.     }
  16. }

The other, and recommended option for authentication is to use the Secure Store Service (SSS).

Note: Secure Store Service is not included in SharePoint Foundation 2010, so this option is unfortunately limited for SharePoint Server 2010 Standard and Enterprise versions.

Create a target application in SSS,

image

then set the credentials of an account with permissions for the external system.

Next, configure your external system to Impersonate Windows Identity, set the name of the Secure Store Target Application Id as created in the previous step, and set  Secure Store Implementation as Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c.

image

After you configured your external system instance as described above, you can use this code to access the external data from the timer process job / event receiver:

  1. using (SPServiceContextScope scope = new SPServiceContextScope(SPServiceContext.GetContext(web.Site)))
  2. {
  3.     // access external list within this block
  4. }

Important to note, that based on the MSDN article mentioned at the beginning of this post, workflows and sandboxed solutions might suffer from the same security problem, so if you have such issues accessing an external data source, the solutions described above might help you in these cases as well.

February 19, 2012

Efforts taken to fool (or fix?) external data access security from the OWS timer process code

Based on the feedback I received to my last post, I think it would be useful to provide a bit more details about the different impersonation / context change methods I’ve tried to applied in my code when experimenting with external data access from OWS timer process. I will include a few helper methods in this post as well, for example one that can be used to trace out the current process context and identity details:

  1. private void TraceUserNames(string msg, SPWeb web)
  2. {
  3.     string httpContextUser = ((HttpContext.Current != null) && (HttpContext.Current.User != null) && (HttpContext.Current.User.Identity != null)) ?
  4.         HttpContext.Current.User.Identity.Name : string.Empty;
  5.     string windowsIdentity = (WindowsIdentity.GetCurrent() != null) ? WindowsIdentity.GetCurrent().Name : string.Empty;
  6.     string spUserName = ((web != null)) ? web.CurrentUser.LoginName : string.Empty;
  7.     Trace.TraceInformation("MailTestEventReceiver.ItemAdded, {3}. httpContextName: '{0}', windowsIdentity: '{1}', spUserName: '{2}'",
  8.        httpContextUser, windowsIdentity, spUserName, msg);
  9. }

You should include

using System.Diagnostics;

for the Trace commands. Using a method like this one is always suggested when you need to get information about what context your the process is running in.

The following code access the external list using the default context of the process:

  1. SPWeb web = properties.Web;
  2. HttpContext originalContext = HttpContext.Current;
  3.  
  4. TraceUserNames("Before impersonation", web);
  5. TestExtList(web);

In this case, TestExtList was a simple method to test external list access from code:

  1. private void TestExtList(SPWeb web)
  2. {
  3.     try
  4.     {
  5.         SPList extList = web.Lists[Global.ExtList];
  6.         SPListItemCollection items = extList.GetItems(extList.DefaultView);
  7.         Trace.TraceInformation("MailTestEventReceiver.TestExtList item count: {0}", items.Count);
  8.     }
  9.     catch (Exception ex)
  10.     {
  11.         Trace.TraceInformation("ERROR in MailTestEventReceiver.TestExtList: '{0}', '{1}'", ex.Message, ex.StackTrace);
  12.     }
  13. }

When uploading documents from the UI, the result of the TraceUserNames method:

Before impersonation. httpContextName: ”, windowsIdentity: ‘domain\user’, spUserName: ‘domain\user’

However, when the code is triggered by an incoming mail, the result is pretty different:

Before impersonation. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

That is true, even if the E-mail security policy at Incoming e-mail settings is set to Accept e-mail messages based on document library permissions (A).

image

So this setting seems to have no effect on the context identity.

The differences I found between this one and Accept e-mail messages from any sender (B).

  1. In the first case (A) a user is looked up based on the e-mail address, and if no one is found or this user has no write permission, the mail won’t be delivered to the document library. In the case (B) the mail is delivered without this kind of verification.
  2. In the case (A) Created By / Modified By fields are set to the user determined by the e-mail address. In the case (B) theses fields are set to SHAREPOINT\system. This latter one correlates with the SharePoint user identity I’ve found for the incoming mail process (see above).

Note: It means that no really security check / authorization happens here. The SMTP mails required no authentication, and it is not so complex to fake a mail with a sender mail address that has write access to the library.

As you may remember from the former post, accessing the external data from code was successful when the document was uploaded from the browser, however it failed with the exception below, when sent as a mail attachment to the library.

Access denied by Business Data Connectivity.
at 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()

To fix this, I first tried to use “simple” elevated permissions:

  1. Guid siteId = properties.SiteId;
  2. Guid webId = properties.Web.ID;
  3.  
  4. SPSecurity.RunWithElevatedPrivileges(delegate()
  5.     {
  6.         using (SPSite siteImp = new SPSite(siteId))
  7.         {
  8.             using (SPWeb webImp = siteImp.OpenWeb(webId))
  9.             {
  10.                 TraceUserNames("Using elevated privileges", webImp);
  11.                 TestExtList(webImp);
  12.             }
  13.         }
  14.     });

The output of the TraceUserNames method was:

Using elevated privileges. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

The exception on external data access was in this case:

Attempted to perform an unauthorized operation.
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()

My next thought was that I should probably apply some kind of impersonation to reproduce the context I found when uploading the document from the browser (see the first TraceUserNames output above).

To get the user I should to impersonate, I read the value of the Modified By field (as discussed earlier, this field contains the sender in scenario A).

  1. SPListItem item = properties.ListItem;
  2. Object editorFieldValueRaw = item[SPBuiltInFieldId.Editor];
  3. SPFieldUserValue editorFieldValue = (editorFieldValueRaw is String) ?
  4.     new SPFieldUserValue(web, (String)editorFieldValueRaw) :
  5.     (SPFieldUserValue)editorFieldValueRaw;
  6. SPUser editor = editorFieldValue.User;
  7. Trace.TraceInformation("User to impersonate: '{0}'", editor.LoginName);

The following code used to impersonate the SharePoint user:

  1. using (SPSite siteImp = new SPSite(siteId, editor.UserToken))
  2. {
  3.     using (SPWeb webImp = siteImp.OpenWeb(webId))
  4.     {
  5.         TraceUserNames("After impersonation", webImp);
  6.         TestExtList(webImp);
  7.     }
  8. }

The output of the TraceUserNames method was:

After impersonation. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘domain\user’

I’ve received the original Access Denied exception when tried to get the number of item in the external list.

Next, I’ve injected a HttpContext as described in this and this posts.

  1. IPrincipal impersonationPrincipal = new WindowsPrincipal(new WindowsIdentity(GetUpn(editor)));
  2. HttpRequest request =
  3. new HttpRequest(string.Empty, properties.WebUrl, string.Empty);
  4.  
  5. HttpContext originalContext = HttpContext.Current;
  6.  
  7. HttpResponse response = new HttpResponse(
  8.      new System.IO.StreamWriter(new System.IO.MemoryStream()));
  9.  
  10. HttpContext impersonatedContext = new HttpContext(request, response);
  11. // these lines required to inject SPContext as well
  12. // if you don't need that it can be deleted
  13. impersonatedContext.User = impersonationPrincipal;
  14. if (web != null)
  15. {
  16.     impersonatedContext.Items["HttpHandlerSPWeb"] = web;
  17. }
  18. HttpContext.Current = impersonatedContext;
  19.  
  20. TraceUserNames("Dummy HTTP context", web);
  21. TestExtList(web);
  22.  
  23. HttpContext.Current = originalContext;

The output of the TraceUserNames method was:

Dummy HTTP context. httpContextName: ‘domain\user’, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

Again, I’ve received the original Access Denied exception.

Finally, I’ve applied Windows impersonation (after granting the Act as part of the operating system user right to the SharePoint 2010 Timer service identity):

  1. WindowsImpersonationContext impersonationContext = null;
  2. try
  3. {
  4.     WindowsIdentity userIdentity = new WindowsIdentity(GetUpn(editor));
  5.     impersonationContext = userIdentity.Impersonate();
  6.  
  7.     using (SPSite siteImp = new SPSite(siteId))
  8.     {
  9.         using (SPWeb webImp = siteImp.OpenWeb(webId))
  10.         {
  11.             TraceUserNames("After Windows impersonation", webImp);
  12.             TestExtList(webImp);
  13.         }
  14.     }
  15.  
  16. }
  17. catch (Exception ex)
  18. {
  19.     Trace.TraceInformation("ERROR in MailTestEventReceiver.ItemAdded impersonation: '{0}', '{1}'", ex.Message, ex.StackTrace);
  20. }
  21. finally
  22. {
  23.     if (impersonationContext != null)
  24.     {
  25.         impersonationContext.Undo();
  26.     }
  27. }

The GetUpn method used in the former code (using System.DirectoryServices.ActiveDirectory namespace is required):

  1. private static string GetUpn(SPUser spUser)
  2. {
  3.     string[] userName = spUser.LoginName.Split('\\');
  4.     Domain domain = Domain.GetCurrentDomain();
  5.     string upn = userName[1] + "@" + domain.Name;
  6.     Trace.TraceInformation("MailTestEventReceiver.GetUpn result: '{0}'", upn);
  7.     return upn;
  8. }

The output of the TraceUserNames method was:

After Windows impersonation. httpContextName: ”, windowsIdentity: ‘domain\user’, spUserName: ‘SHAREPOINT\system’

Again, I’ve received the original Access Denied exception.

As you can see, none of this methods resulted the same output as the one from the user interface-based upload, however mixing the methods (Windows + SharePoint impersonation) the output was the same. However, it did not help to avoid the exception, so the problem seemed to be a little more complex.

After introducing a workaround in the last post, in the next post I will show you a solution for this issue.

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.

November 17, 2010

March 17, 2010

Enumerating timer jobs for service applications and a new way of forcing the immediate execution of a timer job from code

Filed under: Service applications, SP 2010, Timer jobs — Tags: , , — Peter Holpar @ 00:15

You can enumerate SharePoint timer job definitions using the following method:

  1. private void EnumerateJobs(SPSite site)
  2. {
  3.     foreach (SPJobDefinition jobDefinition in site.WebApplication.JobDefinitions)
  4.     {
  5.         Console.WriteLine("JobDefinition Name ‘{0}’, DisplayName: ‘{1}’, Title: ‘{2}’",
  6.             jobDefinition.Name, jobDefinition.DisplayName, jobDefinition.Title);
  7.     }
  8. }

If the timer job is part of a service application, this method has to be modified a bit:

  1. private void EnumerateServiceJobs(SPSite site)
  2. {
  3.     foreach (SPService service in site.WebApplication.Farm.Services)
  4.     {
  5.         Console.WriteLine("Service Name: ‘{0}’, DisplayName: ‘{1}’, TypeName: ‘{2}’", service.Name, service.DisplayName, service.TypeName);
  6.         foreach (SPJobDefinition jobDefinition in service.JobDefinitions)
  7.         {
  8.             Console.WriteLine("  JobDefinition Name ‘{0}’, DisplayName: ‘{1}’, Title: ‘{2}’",
  9.                 jobDefinition.Name, jobDefinition.DisplayName, jobDefinition.Title);
  10.  
  11.         }
  12.     }
  13. }

As Ayman El-Hattab wrote in his post the timer jobs in SharePoint 2010 can be started immediately from the admin user interface using the Run Now button. Although it can be done in WSS 3.0 from code either using the Execute method or by creating a new schedule on demand, there is a new way to do the same from code in in SharePoint 2010. The SPJobDefinition class has a new method called RunNow. The following code shows how to start a specific job definition immediately by specifying the service type name and the job name:

  1. private void StartServiceJob(string serviceTypeName, string jobName)
  2. {
  3.     foreach (SPService service in _site.WebApplication.Farm.Services)
  4.     {
  5.         if ((serviceTypeName == null) || (serviceTypeName == service.TypeName))
  6.         {
  7.             foreach (SPJobDefinition jobDefinition in service.JobDefinitions)
  8.             {
  9.                 if (jobDefinition.Name == jobName)
  10.                 {
  11.                     jobDefinition.RunNow();
  12.                     break;
  13.                 }
  14.             }
  15.         }
  16.     }
  17. }

Or using just the job name:

  1.         private void StartServiceJob(string jobName)
  2.         {
  3.             StartServiceJob(null, jobName);
  4.         }

The following code line starts the UserProfile – Profile Synchronization Job that is part of the User Profile Service.

StartServiceJob("User Profile Service", "UserProfile_ProfileSynchronizationJob");

The Shocking Blue Green Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 54 other followers