Another Reason to Stop Developing Sandboxed Solutions

As you probably know by now, sandboxed solutions are no longer a preferred approach to development in SharePoint 2013. Wherever possible, you should use develop your custom functionality within a SharePoint app. If SharePoint apps don't offer the capabilities you require, you probably need a farm solution anyway, which leaves sandboxed solutions in a distant third place for SharePoint development.

However, there may still be reasons that compel you to create a sandboxed solution for SharePoint 2013. (In my case, it's because I'm writing a course that needs to cover sandboxed solution development). If so, there is a limitation you need to be aware of:

You can't run sandboxed code on a single server installation of SharePoint 2013 on Windows Server 2012 + domain controller.

You can install and activate the solution without any problems, but any sandboxed code will throw the following error:

An unknown exception occurred while executing a sandboxed code solution request in the worker process.

A quick trawl of the forums - e.g. here - suggests that this problem is specific to Windows Server 2012 + DC configurations. Now, we all know that running SharePoint on a domain controller is unsupported in production environments. At the same time, many developers - including myself - find a single-server development environment vastly preferable to a multi-server development environment. If you run a development server and a domain controller on separate VMs, it can be a real battle to keep the VMs in sync when you use snapshots and reverts.

The single server development environment works just fine for SharePoint apps and farm solutions. However, if you want to develop sandboxed solutions, you'll either need to install your single server development environment on Windows Server 2008 R2, or you'll need to run your DC on a separate VM.

Product Catalog Sites and the Product Hierarchy Term Set

We recently ran into a perplexing issue with SharePoint 2013. We'd create a Managed Metadata service application, and we'd be able to create and use term sets without any issues. However, when we created a Product Catalog site, the site collection term store group and the Product Hierarchy term set did not get created. This is a fairly major roadblock, as the entire structure of the site (e.g. the Item Category site column, the Products list) is built around this term set.

In other words, you click this:

You expect to see this:

But you actually see this:

 

The Solution

 

On the properties for your Managed Metadata service proxy, you need to select the option This service application is the default storage location for column specific term sets. To do this, in the list of service applications, select the Managed Metadata service application proxy (be sure to select the proxy, not the service application itself). Then, on the ribbon, click Properties: 



Next, on the Edit Managed Metadata Service Connection dialog, select This service application is the default storage location for column specific term sets, and then click OK:

 



Next time you create a Product Catalog site collection, you should find that the site collection term set group and the Product Hierarchy term set are present. Unfortunately, this step isn't retroactive - you need to configure the Managed Metadata service application proxy before you create the Product Catalog site.

The This service application is the default storage location for column specific term sets option is selected automatically if you use the Farm Configuration Wizard to provision your Managed Metadata service application, but not if you create the Managed Metadata service application manually. It's not necessarily obvious that Product Hierarchy is a column-specific term set, but when you think about it the term set is specific to the Item Category site column within each Product Catalog site.

The Search Dictionaries term store group does not exist

Today's SharePoint 2013 configuration quagmire is this: you provision the Search service application, you click on Search Dictionaries - for example, to manage company name extraction or query spelling correction - and you see a largely empty term store.

In other words, you click this:

You expect to see this:

But instead you see this:

As is often the case with these issues, you'll probably that everything works fine if you use the Farm Configuration Wizard to provision your services. However, if you do things properly and create your service applications by hand, you come across this kind of issue.

The Solution

The Search Dictionaries term set group is not created when you provision the Search service application, it's created when you provision the Managed Metadata service application. When you create a Managed Metadata service application, it should create the Search Dictionaries and the People term set groups in addition to the System group. However, this will only happen if you have provisioned the State service application before you provision the Managed Metadata service application.

You can find details on how to configure the State service here (SharePoint 2010 article, but the steps are the same). For convenience, for a basic deployment, you can provision a State service application by running the following PowerShell cmdlets:

$state = New-SPStateServiceApplication -Name "Contoso State Service"
New-SPStateServiceDatabase -Name "ContosoStateDB" -ServiceApplication $state
New-SPStateServiceApplicationProxy -Name "Contoso State Service" -ServiceApplication $state -DefaultProxyGroup

So, in summary, configure your service applications in the following order:

1. Provision a State service application.
2. Provision a Managed Metadata service application.
3. Configure usage and health data collection.
4. Ensure the usage and health data collection proxy is started.
5. Provision the Search service.

Everything should then work fine.

Note: steps 3 and 4 aren't necessary to create the Search Dictionaries group, but the Search service will report errors if the usage and health data collection service app isn't set up. When you configure usage and health data collection manually, rather than using the Farm Configuration Wizard, you'll typically find that the service application proxy is in the Stopped state. For a good explanation on how to start it, see http://tristanwatkins.com/fixing-the-usage-and-health-data-collection-sa/.

The target principal name is incorrect. Cannot generate SSPI context.

Today's problem occured after I restarted a Hyper-V based SharePoint 2013 farm (Windows Server 2012, one SharePoint 2013 machine, one SQL Server 2012 machine, one DC). I fired up Central Administration and was hit with the following error:

Unknown SQL Exception 0 occurred. Additional error information from SQL Server is included below.

The target principal name is incorrect. Cannot generate SSPI context.

After checking the obvious things - testing connectivity to the DB server, checking the SQL service was running, verifying permissions, etc - I initially figured this was an issue with my Hyper-V snapshots being out of sync, so I ran the SharePoint Products Configuration Wizard. This hit me with the following error:

Failed to detect if this server is joined to a server farm. Possible reasons for this failure could be that you no longer have appropriate permissions to the server farm, the database server hosting the server farm is unresponsive, the configuration database is inaccessible or this server has been removed from the server farm.

I attempted to rejoin the server farm to no avail, then I realised I was barking up the wrong tree. The initial error message suggests a Kerberos issue, while my farm is set up to use NTLM. After a lot of searching, this ancient forum thread pointed me in the right direction. In Active Directory, I opened the computer record for the DB server. In the attribute list, the servicePrincipalName attribute showed the following entries:

Initially I tried deleting just the MSSQLSvc entries, as suggested by the forum thread, but to no avail. So I deleted the whole lot. With no SPNs, authentication falls back to NTLM as it should and the farm comes back to life.

Update: I'm fairly certain that this issue arose when I added Analysis Services to the SQL Server instance on the database server.

We don't know what happened, but something went wrong. Could you please try that again?

I recently ran into an issue I hadn't seen before when configuring Excel Services on SharePoint 2013 RTM. I'd performed all the configuration steps described on TechNet. However, when I tried to browse to a workbook, I got the following error:

I checked the event logs and the trace logs, and the root of the problem was an Excel Services Application error with event ID 5226: Unable to create or access workbook cache.

This might seem like a straightforward permission issue. However, what also tends to happen in this situation is that the IIS application pool will stop, and this error gets buried under many more generic errors. (I've seen event IDs 5231, 5239 and 5240, for which the official advice is to restart the server. Obviously in this case that isn't much help.)

The fix is straightforward - change the permissions on the %WINDIR%\Temp folder. As a managed account, the Excel Services application pool is a member of the local WSS_WPG security group, which has read and execute permissions on the Temp folder. Add the modify permission, recycle the application pool, and everything should work properly.

You could of course grant permissions on the Temp folder to the individual application pool account - in this case, I opted to grant permissions to the WSS_WPG group in case other managed accounts need to create temporary cache files.

Enforcing Site Policy Selection in SharePoint 2013

One of the new features in SharePoint 2013 is the ability to create and publish site policies. Essentially, a site policy defines when a site should be closed and when it should be deleted, together with any reminders, workflows, and so on that you want to associate with the process. You create site policies at the site collection level, and you can then publish them through a Managed Metadata Service application to make them available on other site collections.
One of the big selling points of site policies is that you can use them in conjunction with self-service site creation. The basic idea is that you allow people to create their own sites, but mitigate the associated risk of site proliferation by forcing them to select an appropriate site closure and deletion policy as part of the site creation process. This is all well-documented elsewhere, so I don't plan to go into it here. Instead, I want to focus on a couple of specific issues that stumped us for a couple of hours.

Issue 1: The link on the Self-Service Site Creation Management dialog is a red herring

When you configure self-service site creation, you'll see a dialog like this:



 

Notice the link at the top of the page:

Users can create their own Site Collections from: http://.../_layouts/15/scsignup.aspx

This is a red herring. If you use this link, you'll get a site collection creation dialog of sorts, but there won't be any option to select a site policy - even though you've set Site Classification Settings to A required choice. Instead, you need to use the following site-relative URL:

/_layouts/15/selfservicecreate.aspx

If you use this URL, you'll be presented with a page that forces you to select from a list of your published site policies:

 

 

Issue 2: You must explicitly specify the site creation link

 

If you've read up on configuring self-service site creation, you'll know that the general idea is that users should create site collections from the Sites page on their My Site. If you've enabled self-service site creation on the web application that hosts your My Sites, you'll see a new site link at the top of the Sites page:

 

When you click this link, SharePoint launches the selfservicecreate.aspx page as a dialog. However, unless you have explictly specified the link to the page in the self-service site creation settings, Sharepoint will display a default version of the page - in other words, it will ignore the site classification settings you've configured. When you configure self-service site creation for the My Sites web application, under Start a Site, you must select Display the custom form at and then specify the link to the selfservicecreate.aspx page:

 

 

Now, when you click the new site link, you should see the correct version of the dialog that forces you to select an appropriate policy template for the new site collection:

 

 

Hope that saves a headache or two.

Problems Viewing Health Reports in SharePoint 2013

I've been faced with an interesting problem over the last few days when working with the SharePoint 2013 RTM build. I'm using SharePoint 2013 RTM on Windows Server 2012 and SQL Server 2012 RTM on Windows Server 2012. I configure usage and health data collection in Central Admin using default settings. I click View health reports. I specify some criteria under Slowest Pages, and click Go. I then get presented with the following error message:

Sorry, something went wrong
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

This took quite a bit of troubleshooting. When you click Go on the Health Reports page, SharePoint calls a stored procedure named proc_GetSlowestPages in the WSS_Logging database. After spending some time messing around with SQL Server Profiler, we established beyond doubt that the call to the stored procedure is using the default READ COMMITTED transaction isolation level. The problem lies in a conflict between the proc_GetSlowestPages stored procedure and the database view that it selects data from.

The proc_GetSlowestPages stored procedure looks like this:

SET NOCOUNT ON
SELECT TOP(@MaxRows)
   ServerUrl +
   CASE  ISNULL(SiteUrl,'')+ ISNULL(WebUrl,'')
      WHEN '/' THEN '' ELSE ISNULL(SiteUrl,'')+ ISNULL(WebUrl,'')
   END
   +ISNULL(DocumentPath,'')
   +ISNULL(QueryString,'') AS Url,
   CONVERT(float,AVG(Duration))/1000 AS AverageDuration,
   CONVERT(float,MAX(Duration))/1000 AS MaximumDuration,
   CONVERT(float,MIN(Duration))/1000 AS MinimumDuration,
   AVG(QueryCount) AS AverageQueryCount,
   MAX(QueryCount) AS MaximumQueryCount,
   MIN(QueryCount) AS MinimumQueryCount,
   COUNT(*) AS TotalPageHits
FROM dbo.RequestUsage
WITH (READPAST)
WHERE PartitionId in (SELECT PartitionId from dbo.fn_PartitionIdRangeMonthly(@StartTime, @EndTime))
AND  LogTime BETWEEN @StartTime AND @EndTime
AND (@WebApplicationId IS NULL OR  WebApplicationId = @WebApplicationId)
AND (@MachineName IS NULL or MachineName = @MachineName)
GROUP BY ServerUrl,SiteUrl,WebUrl,DocumentPath,QueryString
ORDER BY AVG(duration) DESC

Notice that the SELECT statement queries dbo.RequestUsage, which is a database view. It uses the READPAST hint, which essentially tells the query engine to skip any locked rows.

The RequestUsage view looks like this:

SELECT * FROM [dbo].[RequestUsage_Partition0] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition1] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition2] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition3] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition4] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition5] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition6] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition7] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition8] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition9] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition10] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition11] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition12] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition13] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition14] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition15] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition16] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition17] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition18] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition19] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition20] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition21] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition22] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition23] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition24] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition25] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition26] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition27] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition28] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition29] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition30] with (NOLOCK) UNION ALL 
SELECT * FROM [dbo].[RequestUsage_Partition31]

Notice that the view uses a whole bunch of NOLOCK hints. These essentially tell the query engine to ignore any locks. This is the source of the problem: you cannot use NOLOCK and READPAST in the same query as they basically contradict each other. Although the transaction isolation level is READ COMMITTED, the use of the NOLOCK hints means it behaves like a READ UNCOMMITTED isolation level.

As far as I can see, this is a bug in SharePoint 2013 RTM, which creates the usage database (named WSS_Logging by default) when you first configure usage and health data collection. I'd guess Microsoft will address it with a patch in the near future. I managed to work around it by altering the proc_GetSlowestPages stored procedure and commenting out the WITH (READPAST) line.

Thanks to Geoff Allix and Graeme Malcolm for the SQL Server tips, it's been an education :-)