Saturday, December 3, 2016

Problem with missing users in group membership page in Sharepoint

Some time ago we faced with strange problem: on one of Sharepoint 2013 sites some users were not shown in their groups’ membership page (Site settings > Users and groups: http://example.com/_layouts/15/people.aspx?MembershipGroupId=groupId). Such users were able to login to Sharepoint and had all necessary permissions, but they didn’t appear on this page. Also need to notice that this site was migrated from SP2007. When I checked groups of the missing user via PowerShell:

   1: $u = Get-SPUser -Id "loginName" -Web http://example.com
   2: $u.Groups

they were properly returned. Also it worked as expected when I checked it in opposite way: check members of the group:

   1: $web = Get-SPWeb http://example.com
   2: $g = $web.SiteGroups["Foo"]
   3: $g.Users

User was shown there as well. And when I checked permissions via the following useful script which shows both permissions granted explicitly to the user and permissions granted via groups:

   1: $url = "http://example.com"
   2: Get-SPUser -Web $url -Limit All | sort-object UserLogin | select UserLogin,
   3:     @{name=”Exlicit given roles”;expression={$_.Roles}},
   4:     @{name=”Roles given via groups”;expression={$_.Groups | %{$_.Roles}}},
   5:     Groups | format-Table -auto

It showed that user has necessary permissions. I.e. the problem was only that user was not shown on the group membership page. In order to fix the issue the following script was used:

   1: $web = Get-SPWeb http://example.com
   2: $g = $web.Groups["Foo"]
   3: $users = $group.Users
   4: foreach ($u in $users)
   5: {
   6:     Write-Host "Fix user" $u.LoginName
   7:     $g.RemoveUser($u)
   8:     $g.AddUser($u)
   9:     $g.Update()
  10: }

It goes through all members of specified groups and then re-add them to this group. After that user appeared on group membership page.

Monday, November 7, 2016

One reason of “The underlying provider failed on Open” Entity Framework exception in Sharepoint

When you use Entity Framework data model in Sharepoint (e.g. in custom web part with server object model) you may face with the following problem: when try to query database the following exception is thrown:

The underlying provider failed on Open

If you will search for solution for this problem most of suggestions in forums will say that you need to grant access for account of IIS application pool to the database. In Sharepoint however it is only half of solution. The problem is that in Sharepoint impersonation is used by default, which mean that if you just grant access to app pool account it still may not work, because actual code will run under account of currently authenticated user (check Thread.CurrentPrincipal property in order to know exact account). As in most cases we don’t want to explicitly grant database access for all users of our web application the simplest solution will be grant access to account of application pool as it is suggested and then switch current context user to account of application pool. The simplest way to do it in Sharepoint context is to use SPSecurity.RunWithElevatedPrivileges:

   1: SPSecurity.RunWithElevatedPrivileges(
   2:     () =>
   3:     {
   4:         // query database via EF
   5:     }
   6: );

In this example we don’t need to reopen SPSite under SPSecurity.RunWithElevatedPrivileges as context user switch will happen anyway. Also note that SPSecurity.RunWithElevatedPrivileges has effect only when code runs in context of Sharepoint web app, but not e.g. in console applications – in last case connection will be done with account of the user under which current console application is running.

After that mentioned error should disappear.

Tuesday, November 1, 2016

How to export OTB List view web part and use it in different sub site in Sharepoint

As you probably know in Sharepoint it is possible to add standard List view web part on the page which is located on the same site with referenced list or doclib. But what if we need to use List view web part for displaying list from other sub site (not the one where web part is added)? By default it is not possible but after several additional steps it become possible. Here are these steps:

  1. Create new test page on the site where referenced doclib is located
  2. Add list view web part for appropriate list and publish the page
  3. Open page in Sharepoint designer
  4. Detach page from page layout
  5. Edit the page and change <ExportControlledProperties> property from False to True
  6. Save the page in Designer
  7. Go back to browser and edit the page. Now Export option will be available for List view web part
  8. Export web part to local file system
  9. In browser open other sub site where you need to add exported web part
  10. Open developer tools panel and in Console tab execute the following javascript code for getting id of the current web:
    SP.ClientContext.get_current().load(SP.ClientContext.get_current().get_web())
    SP.ClientContext.get_current().executeQueryAsync()
    SP.ClientContext.get_current().get_web().get_id()
  11. Copy web id from output
  12. Open exported .webpart file and find <WebId> property. After export it will contain empty guid: 00000000-0000-0000-0000-000000000000
  13. Replace it with guid copied from developer console and save .webpart file
  14. Now again in browser go to sub site where you need to add exported web part and edit the page where web part should be added
  15. Click Insert web part in some web part zone and click Upload web part. Choose modified .webpart file
  16. After upload again click Insert web part on web part zone and choose web part from Uploaded web parts category

In result you will have OTB List view web part working with list from other sub site.

Friday, October 21, 2016

Build connection string for Entity Framework programmatically via code or use Entity Framework in Sharepoint Timer jobs

Before to start I would like to mention that in this post Sharepoint timer jobs are used as example of case when app.config is not available (or it is better to not change it for adding EF configuration). But the same idea can be used also in other places where you don’t want to store EF connection string in configuration file or when it should be built dynamically.

Timer jobs is convenient mechanism when we need to do some actions in Sharepoint by scheduler. They are executed in separate process – Sharepoint Timer Service (it can be found with other services in Control panel > Administrative tools > Services). One of the common problem related with timer jobs is management of connection strings and other configuration data. Of course we can store it in app.config of owstimer.exe which is located here "C:\Program Files\Common Files\Microsoft shared\Web Server Extensions\15\bin". The problem however is that in most cases the same connection strings are also needed in basic web application and already specified in web.config of Sharepoint site. I.e. if we will add them to owstimer.exe.config they will be duplicated which will cause maintenance issues. In this article I will show how to avoid duplication when we need to use Entity Framework inside custom Sharepoint timer job. Described technique allows to avoid changing of owstimer.exe.config.

So suppose that you created simple console application, generated model from existing database (i.e. used Database First approach in terms of Entity Framework), debugged application and now want to move the code to Sharepoint custom job. After you will try to run the same code in Sharepoint job the following exception will be thrown:

Code generated using the T4 templates for Database First and Model First development may not work correctly if used in Code First mode. To continue using Database First or Model First ensure that the Entity Framework connection string is specified in the config file of executing application. To use these classes, that were generated from Database First or Model First, with Code First add any additional configuration using attributes or the DbModelBuilder API and then remove the code that throws this exception.

Message is a bit unclear, but the following article describes in more details reasons of this exception: Don’t use Code First by mistake. In short it happens because when we generated model from existing database, mappings between tables and classes were generated automatically and stored in generated .edmx file. EF DbContext must load it before first use which is done via special Entity Framework connection string. This string was added to app.config of your console application when you generated the model and that’s why it worked there. This EF connection string looks like this:

res://*/MyNamespace.DataModel.csdl|res://*/MyNamespace.DataModel.ssdl|res://*/MyNamespace.DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=MyDb;Integrated Security=True;multipleactiveresultsets=True; App=EntityFramework&quot;

As you can see it differs from regular Sql connection string (last one is included into EF connection string as “provider connection string” part). So we need to provide this special connection string to the EF DbContext. Here is how we can do it:

   1: public partial class MyDataContext
   2: {
   3:     public MyDataContext(string dbConnectionString)
   4:         : base(getEFConnectionString(dbConnectionString))
   5:     {
   6:     }
   7:  
   8:     private static string getEFConnectionString(
   9: string dbConnectionString)
  10:     {
  11:         var builder = new EntityConnectionStringBuilder
  12:             {
  13:                 Metadata = "res://*/MyNamespace.DataModel.csdl|" +
  14:                     "res://*/MyNamespace.DataModel.ssdl|" +
  15:                     "res://*/MyNamespace.DataModel.msl",
  16:                 Provider = "System.Data.SqlClient",
  17:                 ProviderConnectionString = dbConnectionString
  18:             };
  19:         return builder.ToString();
  20:     }
  21: }

I.e. we create partial class for our DbContext (it should be located in the same namespace) and create new constructor with one parameter – Sql database connection string (note that we pass there exactly classic database connection string, not EF connection string). Then we build EF connection string by using EntityConnectionStringBuilder class and include Sql connection string to it.

Why we chose regular Sql database connection string as parameter and how we will get it in custom timer job? The answer is because in web.config of Sharepoint site there is exactly regular connection string:

   1: <connectionStrings>
   2:   <add name="MyConnectionString"
   3: connectionString="Data Source=.;Initial Catalog=MyDb;Integrated Security=True" />
   4: </connectionStrings>

I.e. if we would read connection string from web.config inside the job we would be able to build Entity Framework connection string dynamically and would avoid duplication of configuration data because connection string still would be stored in single place – in web.config. Also in this case no manual changes would be needed in owstimer.exe.config.

This can be achieved by using property bag of custom Sharepoint job: SPJobDefinition.Properties. The idea is that inside feature receiver which provisions the job we read connection string from web.config and store it in job’s property bag:

   1: public class MyEventReceiver : SPFeatureReceiver
   2: {
   3:     public override void FeatureActivated(
   4: SPFeatureReceiverProperties properties)
   5:     {
   6:         var parent = (SPWebApplication)properties.Feature.Parent;
   7:  
   8:         var config =
   9: WebConfigurationManager.OpenWebConfiguration("/", parent.Name);
  10:         if (config.ConnectionStrings
  11:             .ConnectionStrings["MyConnectionString"] == null)
  12:         {
  13:             return;
  14:         }
  15:         string connStr = config.ConnectionStrings
  16:             .ConnectionStrings["MyConnectionString"].ConnectionString;
  17:  
  18:         this.deleteExistingJob("My job", parent);
  19:         this.createJob(parent, connStr);
  20:     }
  21:  
  22:     public override void FeatureDeactivating(
  23: SPFeatureReceiverProperties properties)
  24:     {
  25:         var parent = (SPWebApplication)properties.Feature.Parent;
  26:         this.deleteExistingJob("My job", parent);
  27:     }
  28:  
  29:     private void createJob(SPWebApplication site, string connStr)
  30:     {
  31:         var job = new MyJob("My job", site);
  32:         var schedule = new SPDailySchedule();
  33:         schedule.BeginHour = 1;
  34:         job.Schedule = schedule;
  35:         job.Properties.Add("ConnectionString", connStr);
  36:         job.Update();
  37:     }
  38:  
  39:     private void deleteExistingJob(string jobName, SPWebApplication site)
  40:     {
  41:         foreach (var job in site.JobDefinitions)
  42:         {
  43:             if (job.Name == jobName)
  44:             {
  45:                 job.Delete();
  46:                 return ;
  47:             }
  48:         }
  49:     }
  50: }

In example above we assume that feature has WebApplication scope. Then inside the job we can get connection string from property bag:

   1: public class MyJob : SPJobDefinition
   2: {
   3:     public MyJob() : base() { }
   4:  
   5:     public MyJob(string jobName, SPService service)
   6:         : base(jobName, service, null, SPJobLockType.None)
   7:     {
   8:         this.Title = jobName;
   9:     }
  10:  
  11:     public MyJob(string jobName, SPWebApplication webapp)
  12:         : base(jobName, webapp, null, SPJobLockType.ContentDatabase)
  13:     {
  14:         this.Title = jobName;
  15:     }
  16:  
  17:     public override void Execute(Guid targetInstanceId)
  18:     {
  19:         string connStr = this.Properties["ConnectionString"] as string;
  20:         // connect to database
  21:     }
  22: }

After that we pass this connection string to EF DbContext constructor, it creates EF connection string and connects to database. Described approach allows to use Entity Framework without having EF connection string in app.config e.g. inside Sharepoint custom jobs. Hope it will help someone.

Tuesday, October 18, 2016

How to get events history for specific list or document library in Sharepoint

Sometime we need to retrieve events history for specific Sharepoint list or document library for particular period. Internally events log is stored in EventCache table in content database. In order to get it programmatically we can use SPChange* classes from Sharepoint object model. E.g. the following example shows how to get list of all documents which were added today:

   1: private static List<ItemChange> getListItemChanges(SPWeb web,
   2:     SPList list)
   3: {
   4:     try
   5:     {
   6:         if (list == null)
   7:         {
   8:             return new List<ItemChange>();
   9:         }
  10:  
  11:         var today = DateTime.Today;
  12:         var changeTokenStart = new SPChangeToken(
  13:             SPChangeCollection.CollectionScope.List, list.ID,
  14:             today.ToUniversalTime());
  15:         var changeTokenEnd = new SPChangeToken(
  16:             SPChangeCollection.CollectionScope.List, list.ID,
  17:             today.AddDays(1).ToUniversalTime());
  18:         var changeQuery = new SPChangeQuery(false, false);
  19:         changeQuery.Item = true;
  20:         changeQuery.Add = true;
  21:         changeQuery.Delete = false;
  22:         changeQuery.Update = false;
  23:         changeQuery.ChangeTokenStart = changeTokenStart;
  24:         changeQuery.ChangeTokenEnd = changeTokenEnd;
  25:         var changes = list.GetChanges(changeQuery);
  26:  
  27:         var listItemChanges = new List<ItemChange>();
  28:         foreach (SPChangeItem c in changes)
  29:         {
  30:             SPListItem item = null;
  31:             try
  32:             {
  33:                 item = list.GetItemById(c.Id);
  34:             }
  35:             catch (Exception x)
  36:             {
  37:                 Console.WriteLine("Error occured: {0}", x.Message);
  38:                 continue;
  39:             }
  40:  
  41:             if (item == null)
  42:             {
  43:                 continue;
  44:             }
  45:  
  46:             listItemChanges.Add(new ItemChange
  47:                 {
  48:                     Time =
  49: web.RegionalSettings.TimeZone.UTCToLocalTime(c.Time),
  50:                     ChangeType = c.ChangeType,
  51:                     FileName = item.File.Name,
  52:                     Url = SPUrlUtility.CombineUrl(web.Url, item.File.Url)
  53:                 });
  54:         }
  55:         return listItemChanges;
  56:     }
  57:     catch (Exception x)
  58:     {
  59:         Console.WriteLine("Exception occured: {0}\n{1}",
  60:             x.Message, x.StackTrace);
  61:         return new List<ItemChange>();
  62:     }
  63: }

Class ItemChange used in this example if POC helper class defined like this:

   1: public class ItemChange
   2: {
   3:     public DateTime Time { get; set; }
   4:     public SPChangeType ChangeType { get; set; }
   5:     public string FileName { get; set; }
   6:     public string Url { get; set; }
   7: }

At first we create instances of SPChangeToken class where specify change scope (List), id of specific list and start and end time for limiting search (lines 11-17). Then we create SPChangeQuery instance where specify that we want to get changes made for files in our list (line 19) and what kind of event types we want to track (lines 20-22). In our example we set it so only Add events should be returned, in order to return other event types specify true for Delete and Update properties. After that we pass start and end change tokens to appropriate properties of the query (lines 23-24), call SPList.GetChanges method and pass constructed SPChangeQuery object there. On lines 27-55 we iterate through returned results and create more convenient and simple collection of ItemChange objects which together with event time and change type also contain file name and url.

Note that there is also SPChangeQuery.FetchLimit property which by default is set to 1000. If your doclib may contain more changes for specific period, you need to add pagination logic to your code.