How to display Specific Content to Anonymous or Authenticated Users in SharePoint

A long time ago I wrote about the usefulness of the SPSecurityTrimmedControl in selectively displaying content based on a user’s permission level. It supports a myriad of different permission options, and my friend Marc Anderson has an excellent post in which he outlines all of the possible permission levels that can be used with this control by manipulating the PermissionsString attribute.

What is less well known about this control is that it can also be used in an application that supports anonymous access to selectively display content based on the user’s logged in state. This might be to display a custom log in button or link that should only be displayed when a user has not logged in. The way that this is accomplished is through the  AuthenticationRestrictons attribute. There are three possible values:

  • AllUsers
  • AnonymousUsersOnly
  • AuthenticatedUsersOnly

I have no idea why the AllUsers value exists. It’s not much of a restriction. The other two values are pretty well named, so I won’t bother explaining them.

A pretty typical usage scenario might be to build a page layout that will display a content field for all users, another field exclusively for anonymous users, and another for logged in users. The exclusive fields would need to be created and added to a content type prior to the creation of the layout. A simple example of this might appear as follows:

<PublishingWebControls:RichHtmlField
id=”PageContent”
FieldName=”PublishingPageContent”
DisableInputFieldLabel=”true”
runat=”server”/>

<SharePoint:SPSecurityTrimmedControl
runat=”server”
ID=”spAuthenticated”
AuthenticationRestrictions=””>

<PublishingWebControls:RichHtmlField
FieldName=”PageContentAuthenticated”
runat=”server”
/>

</SharePoint:SPSecurityTrimmedControl>

<SharePoint:SPSecurityTrimmedControl
runat=”server”
ID=”spUnAuthenticated”
AuthenticationRestrictions=”AnonymousUsersOnly”>

<PublishingWebControls:RichHtmlField
FieldName=”PageContentAnonymous”
runat=”server”
/>

</SharePoint:SPSecurityTrimmedControl>

In this example, the PublishingPageContent field is displayed on the page first, and depending on the logged in state, one of the remaining fields will be displayed. Of course, in order to use the control, the WebControls directive must first be added to the page:

<%@ Register Tagprefix=”PublishingWebControls” Namespace=”Microsoft.SharePoint.Publishing.WebControls” Assembly=”Microsoft.SharePoint.Publishing, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>

The above example is for SharePoint 2010, but the namespace also exists in 2013.

This is simple enough, but there is a practical problem with this approach. How do we edit the content for anonymous users on the page? Although the control is there, editors are going to be authenticated users, and therefore the content will be completely hidden from them.

To do this, we can treat the Anonymous filed like other page metadata, and include it (also) in an edit mode panel, without the spSecurityTrimmedControl.

<PublishingWebControls:EditModePanel
class=”ewiki-margin”
runat=”server”>

<PublishingWebControls:RichHtmlField
FieldName=”PageContentAnonymous”
runat=”server”
/>

</PublishingWebControls:EditModePanel>

The contents of the edit mode panel are only displayed when the page is in edit mode, so authors will be able to edit anonymous content and authenticated content in one step.

This control isn’t limited to publishing scenarios, but does require the Publishing namespace, and therefore requires at least SharePoint Standard license.

Advertisements

Setting Up Reporting Services and PowerPivot With SharePoint

Yesterday, I had the privilege of being the guest presenter of a hands on webinar organized by Cloudshare. The topic was how to install and configure both SQL Server Reporting Services SharePoint Mode and Power Pivot for SharePoint. The session was recorded, and is available below. The complete PowerPoint deck can also be downloaded here.

Troubleshooting and Removing a Failed Installation or Upgrade of PowerPivot for SharePoint

I recently had the opportunity to perform an upgrade of PowerPivot from SQL Server 2008 R2 to SQL Server 2012 for a customer with a relatively large SharePoint farm. Their farm consisted of 4 SharePoint front end servers, 2 SharePoint application servers, and a SQL Server 2008 R2 cluster. The PowerPivot for SharePoint 2008 R2 service was installed on the 2 application servers. The upgrade didn’t go quite as smoothly as planned and in the process it was necessary to manually remove all traces of the PowerPivot 2008 R2 service. This was a little tricky (to say the least), so I thought that I’d share the experience here in hopes that it helps someone.

The original plan was to remove the PowerPivot service from the second server, then perform a SQL server upgrade on the remaining server, upgrading the PowerPivot for SharePoint instance. The PowerPivot configuration tool would then be run on the single server, upgrading the SharePoint elements. Finally, PowerPivot for SharePoint 2012 would be reinstalled on the second application server.

This is an approach that has worked quite well in the past, but in this particular environment, the configuration tool could not run the upgrade process. Due to the fact that there had been no work done on scheduling data refreshes thus far, it was decided that a complete removal, and install from scratch was in order. This is where things got particularly tricky. The configuration tool was unable to run its uninstall process, so I elected to remove everything manually, which consisted of deactivating the PowerPivot features in the destination site collections and from Central Admin, then retracting the PowerPivot solutions from Farm Solutions. Finally, SQL Server installation was run, and the PowerPivot instance removed from the server completely.

Removing PowerPivot Breaks Central Administration

At this point, Central Administration became unavailable. A quick search through the ULS logs for the correlation ID reported that there were errors loading the Microsoft.AnalysisServices.SharePoint.Integration assembly. As a next step, I ran through the SharePoint Configuration Wizard (psconfigui) to make sure that everything was OK. As it turns out, it wasn’t OK, and the wizard repeatedly failed.

image

After some searching, I located a TechNet article that pointed out the problem. Apparently with 2008 R2, PowerPivot has a little trouble uninstalling itself, and leaves a few artefacts. Two of the artefacts are the registry keys listed in the article, and when the configuration wizard sees them, it tries to load the assembly, which of course no longer exists, and then it fails out. Removing the two keys fixes this particular problem, and I’m reproducing the steps for doing so below for convenience.

Open the registry editor. To do this, type regedit in the Run command.
Expand HKEY_LOCAL_MACHINE
Expand SOFTWARE
Expand Microsoft
Expand Shared Tools
Expand Web Server Extensions
Expand 14.0
Expand WSS
Expand ServiceProxies
Right-click Microsoft.AnalysisServices.Sharepoint.Integration.MidTierServiceProxy and select Delete.
Go back a step in the hierarchy. Under WSS, expand Services
Right-click Microsoft.AnalysisServices.Sharepoint.Integration.MidTierServicea and select Delete.

After getting rid of the registry entries, the configuration wizard completed successfully, and more importantly, Central Administration loaded up properly. In addition, to this, I also removed the same registry keys from the second application server in anticipation of a reinstall.

It was therefore time to reinstall PowerPivot. Installation from the SQL server media went fine (don’t forget to add all necessary accounts as administrators, particularly the account that is running the Analysis Services Windows service).

Parent Service Does Not Exist

However, re-running the PowerPivot configuration Tool resulted in the error “Cannot create the service instance because the parent Service does not exist”.  

image

This turned out to be a particularly vexing problem. The only guidance that I could find in the forums was incorrect (things like, “the Database engine must be installed with the PowerPivot instance”, which is patently false). I went to the source PowerShell scripts that register the service, and was able to run them, and register the service. The PowerPivot service then appeared under Services on Server, and I was even able to create a new PowerPivot service application thorough the Central Administration UI. However, attempting to access it resulted in an error (which I no longer have access to). Examining the ULS logs showed “access denied” attempting to connect to the Analysis Services instance.

In addition, subsequent attempts to run the PowerPivot configuration tool resulted in a perplexing error that stated that all servers in the farm must be running the same service account, and that they should all be changed to run as LOCAL SERVICE. This is particularly odd given that PowerPivot MUST be installed with a domain account, so this isn’t even possible.  As it turned out, this was a red herring, and occurred because I had incorrectly specified the credentials of the service account in my PowerShell scripts from above, and it had provisioned using the LOCAL SERVICE account. I was able to change the identity of the service through Central Administration in the Security section, by configuring the service accounts, and then the PowerPivot configuration tool was able to run to completion. However, the service application, and its proxy appeared as stopped in the Service Applications list.

Manual Uninstallation

Something was still amiss, and for the life of me I couldn’t figure out what it was. At this point, I engaged the PowerPivot support team. After eliminating a few other potential issues, we decided to manually uninstall PowerPivot and use a few clean-up tools afterward. I again deleted service applications, deactivated features, retracted and removed solutions, and uninstalled PowerPivot for SharePoint with the SQL Setup application. Once everything had been theoretically removed, I ran this PowerShell script (repeated below) provided by the Support team to force the removal of all remaining PowerPivot items from the farm.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction:SilentlyContinue

Function DeleteServiceAndInstances
{
    param($service)
    if($service)
    {
        foreach($instance in $service.Instances)
        {
            $instance.Delete()
        }
 
        $service.Delete()
    }
}

Function DeleteServiceApplications
{
    param($service)
    if($service)
    {
        foreach($instance in $service.Applications)
        {
            $instance.Delete()
        }
    }
}

Function DeleteServiceApplicationProxies
{
    param($proxy)
    if($proxy)
    {
        foreach($instance in $proxy.ApplicationProxies)
        {
            $instance.Delete()
        }
    }
}

Function DeletePowerPivotEngineServiceAndInstances
{
    $farm = Get-SPFarm
    $service = $farm.Services | where {$_.GetType().GUID -eq "324A283C-6525-43c8-806C-31D8C92D15B7"}
    DeleteServiceAndInstances $service
}

Function DeletePowerPivotMidTierServiceAndInstances
{
    $farm = Get-SPFarm
    $service = $farm.Services | where {$_.GetType().GUID -eq "35F084BE-5ED5-4735-ADAA-6DB08C03EF26"}
    foreach($job in $service.JobDefinitions)
    {
        $job.Delete()
    }

    DeleteServiceApplications $service    
    DeleteServiceAndInstances $service
}

Function DeletePowerPivotEngineServiceProxy
{
    $farm = Get-SPFarm
    $proxy = $farm.ServiceProxies | where {$_.TypeName -eq "Microsoft.AnalysisServices.SharePoint.Integration.EngineServiceProxy"}
    if ($proxy)
    {
        $proxy.Delete()
    }
}

Function DeletePowerPivotMidTierServiceProxy
{
    $farm = Get-SPFarm
    $proxy = $farm.ServiceProxies | where {$_.TypeName -eq "Microsoft.AnalysisServices.SharePoint.Integration.MidTierServiceProxy"}
    if ($proxy)
    {
        DeleteServiceApplicationProxies $proxy
        $proxy.Delete()
    }
}


DeletePowerPivotEngineServiceProxy
DeletePowerPivotMidTierServiceProxy
DeletePowerPivotEngineServiceAndInstances
DeletePowerPivotMidTierServiceAndInstances

This script ran successfully, and in theory should have removed all of the PowerPivot elements from the SharePoint farm. However, just to be sure, we ran the following SQL script (also provided by MS support) to look for any orphaned PowerPivot elements in the farm configuration database:

   SELECT Id, classid, parentid, name, status, version, properties
   FROM objects
   WHERE name like '%PowerPivot%'
   ORDER BY name

Lo and behold, there was still a record in the configuration database pointing to an instance that obviously no longer existed. Now, direct editing of the configuration databases is not something that anyone should do lightly, and it’s not generally supported. However, in a few cases, it’s the only option, and according to support, this was one of those cases. I therefore took a backup (ALWAYS take a backup first!) of the config database, and then ran the following SQL query to determine if the instance had any configuration dependencies.

SELECT * FROM Objects (NOLOCK) WHERE Properties LIKE ‘%GUID-found-in-earlier-query%'

The GUID is the value of the ID field found in the previous query for the orphaned item. In my case, there were no items returned, but if there were, the dependencies would need to be removed. If you find dependencies, you will need to contact support yourself, as there are additional complicating factors involved that will need to be evaluated by the product group.

Since I had no dependencies, I was safely able to delete the offending record.

   DELETE FROM objects WHERE name like '%PowerPivot%'

My Configuration Database was now clean.

The Return of the Parent Service Error

At this point, I reinstalled PowerPivot for SharePoint, and once again, ran the PowerPivot configuration tool. Unfortunately, I ran right into the “Parent Service Does Not Exist” error discussed above. This was frustrating, to say the least. After several choice words, and some sleep, I decided to do yet another uninstall/reinstall. This difference this time is that instead of retracting solutions manually, I would allow the configuration tool to do the uninstall for me. When I did, it generated an error when it attempted to uninstall a feature:

The feature with ID ‘1a33a234-b4a4-4fc6-96c2-8bdb56388bd5’ is still activated within this farm or stand alone installation. Deactivate this feature in the various locations where it is activated or use -force to force uninstallation of this feature.

This GUID is the PowerPivotSite feature, which was still activated at one of the site collections. I then used stsadm to force the uninstallation of the feature (I used stsadm simply because I’m a dinosaur, and know the switches off the top of my head. Obviously PowerShell would work just a well, if not better).

stsadm -o uninstallfeature -id ‘1a33a234-b4a4-4fc6-96c2-8bdb56388bd5’ -force

After forcibly removing the feature, I used the PowerPivot configuration tool to remove PowerPivot. Once removed, I ran it again to configure the PowerPivot instance, and this time, it completed successfully.

I did note one thing that I thought was odd, and haven’t been able to explain. On this last run of the configuration tool, the step that provisioned the PowerPivot service application took an inordinately long amount of time, about an hour. Not content to leave well enough alone, I deleted the service application, and re-ran the tool. This time it completed in under a minute. I only mention this in case someone tries this and gives up because they think the system has hung up.

Once I got the first server up and running, it was relatively simple to install PowerPivot for SharePoint 2012 on the second application server, and run the configuration tool, which did everything necessary to add the second application server back into the PowerPivot rotation.

After cleaning up the remaining configuration items common to PowerPivot for SharePoint, and cleaning up the Health Analyzer errors, PowerPivot now appears to be running smoothly on this farm.

Installing and/or Upgrading a Multi-Server SQL Server Reporting Services 2012 SharePoint Mode Farm

A few months ago I posted an article discussing how to upgrade integrated mode Reporting Services from 2008R2 to 2012. That article pretty well assumed a small SharePoint farm, with a single SharePoint server, a single SQL server, and with Reporting Services running on the SharePoint server. In this article, I’d like to address upgrading and/or installing on a medium or large farm, but to do so, I need to first discuss the nuances involved in scaling out the Reporting Services service application of a SharePoint 2010 farm.

As I discussed previously, and as the Service Application architecture of Reporting Services 2012 makes plain, Reporting Services bits from the SQL server installation media must be installed on a server that has the SharePoint bits installed, and is joined to the farm. In the simple farm scenario mentioned above, that’s fairly straightforward – it’s the SharePoint server (NOT the SQL server!). However, if your SharePoint farm consists of multiple servers, you need to decide where you want your Report processing to be done. With 2012, Reporting Services is a full fledged SharePoint application, which means that it is relatively straightforward to load balance this processing.

A recent project that I worked on had an architecture very similar to the diagram found on the MSDN article explaining how to scale out Reporting Services:

image

The only difference in my case was that there were 4 servers in the front end role (1). Of course the servers in the application role served up more than just Reporting Services, but the diagram is essentially accurate. In our case, the SQL Server cluster was running SQL Server 2008 R2, but that was inconsequential because only the Reporting Services instances on the two Application Servers were being upgraded.

If you are are upgrading, there are a number of other steps in addition required first. No matter what, the encryption key should be backed up first. It will be needed after the upgrade unless you want to recreate all of your data connections. Once that is backed up, you should of course back up your two Reporting Services databases. Next, if you have a load balanced (Reporting Services) environment, I recommend removing from rotation all of the load balanced RS servers, leaving only the server that will be used for the primary upgrade. To make things very simple, I also recommend completely uninstalling Reporting Services from all but the one server.

Once complete, the main server can be upgraded according to the instructions laid out in my previous article. However, you may find, as I have, that SSRS doesn’t always want to be upgraded cleanly. The good news is that this is relatively easy to recover from. The SSRS service application works like other service applications in that when it is being created, and you specify an existing database, that database will be upgraded automatically. Therefore, if you find yourself with an uncooperative SSRS installation, simple uninstall it, and install the 2012 components from scratch, making sure to use the name of your existing RS database when the service application is being created. Once complete, restore your encryption key, and you should be good to go.

Whether or not you are upgrading or installing fresh, there are a number of differences when installing to a multi server farm compared to a single server installation.

Firstly, when installing SSRS 2012, you will be presented with a screen where you may choose the SQL features to be installed.

image

There are two components to Reporting Services in SharePoint mode, Reporting Services – SharePoint, and the Reporting Services Add-in for SharePoint Products. A lot of the guidance simply says to select both options and continue. However, in a multi farm environment it is important to understand the difference between the two.

Feature #1 is the core of Reporting Services, what in the past would have been the instance, but is now the service application itself. Feature #2 is the add-in, which has been around since the first integrated mode SSRS. It is used by SharePoint to connect to SSRS. In the past, that was a connection to the SSRS web services, but is now how the SharePoint front end servers connect to the service application. Therefore, in a multi server farm, feature 1 should be installed on every application server that will process SSRS reports, and feature #2 must be installed on every server participating in the front end role. Since application servers often perform a dual role, at least for administrators, I recommend installing both features on application servers.

I’m not going to walk through the steps required to create the service application and light up the features in this article, as there is quite a bit of good guidance on that available. I also wrote one up for installing on SharePoint 2013 which is pretty much identical to 2010 for SSRS.

Once the initial installation is complete, it should be repeated on every server that will participate in the Application server role. Obviously the service application only needs to be created once. Once all of the bits are installed on all of the relevant servers, simply navigate to Services on Server under System Settings in Central Admin, and start the “SQL Server Reporting Services” service on every application server. Once that’s done, you’ll have a load balanced, multi-server Reporting Services service.

It is worth calling out a common error encountered in the multi-server farm scenario. You may find that after your upgrade or installation has completed, attempts to access a report from the front end servers result in a connection error, “The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version”.

image

It’s the last bit of the error that’s relevant. This problem arises when the add-in hasn’t been installed to the front end servers in the farm. Now, you may remember that the Reporting Services Add-In is one of the prerequisites that the prerequisite installer installs on your SharePoint boxes. In the case of an upgrade, you may also remember that you never had to do this before to get SSRS working. So why is it that we need to do this now? It’s because the add-in included with the prerequisite installer is for SSRS 2008 R2, and we’ve just added SSRS 2012. The add ins are not forward compatible, and therefore, it need to be on every front end server in the farm.

So to recap, in order to scale out reporting services, Install the service on one application server, and get it working in the farm. Then, install the service on the remaining application servers, start the service on each server, and install the RS add-in for 2012 on all of the front end servers.

Using Per-User Identity with Reporting Services and SharePoint

Almost anyone that has dealt with business intelligence in the Microsoft space is aware of the double hop problem. Simply put, when a user wants a server to execute a process on their behalf, that process will have a different identity, if any at all from the originating user, and therefore user focused operations such as per-user security trimming can’t be performed. The traditional solution to this problem in the Windows world has been to use Kerberos authentication, which allows server to pass on secure user tokens to other servers on behalf of originating users.

Unfortunately, Kerberos requires additional infrastructure, and is not adopted in all organizations. Smaller to mid sized organizations tend to stay away from adopting it, primarily due to its perceived complexity, which leaves them with the double hop problem. In the long term, claims based authentication promises to solve this problem, but it is still in its infancy, and is not adopted at all yet in the SQL Server suite of products, which is where double hop pain is felt most keenly.

The new BISM tabular model with Power View works around this problem by taking advantage of the EFFECTIVEUSERNAME property available in Analysis Services (I’ll be writing more about that later), as does Excel Services and PerformancePoint Services in SharePoint 2013. With EFFECTIVEUSERNAME, the server makes its connection with the data source using its service credentials, but then every query identifies the originating user, and those queries are executed in the context of that user. It’s a clever workaround, and takes care of much of the BI stack, but what about Reporting Services and relational data?

As it turns out, Reporting Services has been able to do this sort of thing for a long time. It’s just been rather cleverly concealed or misunderstood. SQL Server itself has a function, SetUser() that can be used by someone with sufficient privileges to impersonate another user. Originally implemented for testing purposes, Reporting Services takes advantage of it in much the same way that EFFECTIVEUSERNAME works in Analysis Services. The place to turn on this function is within a data source editor.

Using SharePoint Integrated mode, there are 3 main tools that can be used to define a data source. A data source can be created from any library that is configured to use the Report Data Source content type. In addition, SQL Server Report Builder can be used to edit a data source, or to create an embedded data source in a Report. Finally, Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (depending on your version of SQL) can be used to create shared data sources. And the best news is, the interfaces are all different!

image                                        Setting Connection credentials in SQL Server Data Tools

image                                Setting Connection credentials in SharePoint 2013

image                                      Setting  Connection credentials in Report Builder

The option that we’re interested in, in all three cases is “Use this user name and password” (stored credentials). In the case of Data Tools or BIDS, the impersonation option isn’t available. Normally what would happen is that the connection would be deployed to the server, and then edited afterwards. We can therefore ignore this interface. However, examine the description of the impersonation option in both SharePoint and Report Builder. Although worded completely differently, they mean exactly the same thing. And in my opinion, Report Builder has it right. In fact it’s a stretch to say that the SharePoint screen isn’t dead wrong.

The SharePoint form can only be considered correct if the word “this” applies to the currently logged in user. To my mind, when I read that option, I am led to believe that it will be set to the account that is being used to make the connection (the stored credentials), when it is in fact the opposite. The setting in Report Builder is far clearer and more accurate.

When the option is checked, the SetUser() SQL function will be used in advance of any queries performed by the report, and the value will be set to the currently logged in user, NOT to the credentials specified under “Use this name and password”. The credentials specified here are used to make the connection to the SQL server, and because of the security restrictions of SetUser(), MUST have at least the dbOwner role on the queried database, or the SYSADMIN fixed role on the server. My recommendation is the latter if possible. This requirement also makes it very important to restrict the editing of the Data Source.

I imagine that most of the confusion around this feature comes from the fact that in most cases, when you are using impersonation, you are saying “I want to execute this function as this account”. However, in this case, with stored credentials, it’s always the stored credential executing the function, and what we’re asking it to do is to impersonate the currently logged in user. It’s sort of backwards to the way we normally think about impersonation. It’s not immediately intuitive as to why a logged in user would need to impersonate themselves, but that’s basically what’s happening.

Using this option therefore allows you to utilize per user permissions through Reporting Services on SharePoint without having to use Kerberos. This is a good thing, but you should be aware that in many cases, using a proxy account (by not selecting the impersonate option) may be a better option. There are several drawbacks to using per-user authentication. Impersonation should only be used when it is absolutely required.

If you open up the report processing screen for a report that uses a data source defined for impersonation, and select the “used cached data” option, you will receive the warning “This report can not be cached because one or more of the data sources credentials are not stored”.

image

The reality is that although the credentials are in fact stored, the report could be different for different users, and caching depends on a consistent result. Therefore, to prevent confusion, it’s not allowed. If the report is particularly large, this removes an important performance optimization technique.

From the same screen, it’s also made very clear that snapshotting is not an option when impersonation is being used, from the error “This report can not run from a snapshot because one or more of the data sources credentials are not stored”.

image

The reasons are the same as for caching, and the implication is that snapshots cannot be created, preventing us from storing a version history for the reports.

Finally, subscriptions are also rendered useless through the use of impersonation. An attempt to create a subscription to a report using an impersonated data connection results in the following error:

image

Per-user identities also require management on the SQL server side. Because we are impersonating the currently logged in user, that user must be managed from within SQL itself, whereas a proxy account only requires that account to have access to the data in question.

To summarize, Kerberos is not required to do per user queries in Reporting Services for relational data. The SetUser() function in SQL Server combined with the Execution Context option in data connections allow this to be performed quite effectively. However, just because it can be done, doesn’t mean that it should be. In many cases a simple proxy account can do the job, and should likely be allowed to, unless the requirements absolutely demand otherwise.