The Health Analyzer and PowerPivot for SharePoint

*Note – Updated 2012-09-07

The Health Analyzer that first appeared in SharePoint 2010 is a fantastic idea. Periodically, (you say when) timer jobs run to check predefined health rules, and when those fail, you are alerted in Central Administration with a yellow or red error bar depending on the severity. This can potentially bring problems to an administrator’s attention before their effects are felt. Unfortunately, some of the default rules, through a combination of being useless, or buggy, result in almost any farm being constantly alerted. This has the negative effect of reducing confidence in it, and causing administrators to ignore these alerts, which is a pity, because when properly configured, they can be quite useful.

The trick is to spend a few minutes to deactivate the ones that don’t work properly, or are unnecessary. My personal favourite is the alert “Database has large amounts of unused space” – which is actually a very good condition.

All that you really need to do for these buggy rules is to open them up, and disable them. It’s relatively straightforward to do so. While in Central Administration, just open up any of the alerts, and select the “View” link in the Rule Settings section.

From the resulting screen, just click Edit in the ribbon, and deselect the “Enabled” option.

Taking the time to do either do this, or to remedy the situation for all of the alerts that you’re receiving on your farm will turn your Health Analyzer from a nuisance to a valuable monitoring tool.

In order to test whether or not your changes have worked, you need to run all of the Health Analyzer jobs. You can do this manually, through Central Administration, or you can run the following PowerShell (by Gary Lapointe, lifted shamelessly from Matthew McDermott’s blog):

Get-SPTimerJob | where {$_.Title -like “Health Analysis Job*”} | Start-SPTimerJob

Installing PowerPivot for SharePoint introduces a group of new rules to the mix, and like their out of the box cousins, they are a mixed blessing. Some are important, and some are downright wrong. I had occasion recently to do a clean install of PowerPivot for SharePoint 2012, and decided to document the Health Analyzer results, and their required actions here.

Upon completion of the PowerPivot for SharePoint install, I was initially taken aback to find so many errors:

OK, one of these has nothing to do with PowerPivot, but we’ll tackle the rest. In reality, they’re not so bad taken one at a time. We’ll start with one that’s potentially very important:

1. Built-in accounts are used as application pool or service identities.

It’s good practice to always use domain accounts for any service in SharePoint, which is why this alert exists. Opening it up points to the offending culprit, the Claims to Windows token service (C2WTS). By default, it is typically set up to run with the local system account, and there’s a good reason for that.

The C2WTS service is used by PowerPivot (OK… Excel Services) to convert from claims based identities to Windows identities. Non claims based services (like Excel Services) need it to work, and it is required for PowerPivot to work properly.

It’s relatively straightforward to change the identity for this service, but whatever you do, change it using Central Administration, not through Windows Services. SharePoint “knows” about the identity and will eventually reset it if you use the Windows interface. To change the identity, simply navigate to Security-Configure Service Accounts and select the appropriate managed account – but be warned, if you do, you have more work to do – in my opinion, it’s best to leave it as Local System.

The problem is, the C2WTS must run with the identity of an account that is part of the Local Administrators group on the server, or as Local System. If you do decide to use a domain account, you will need to add it that group (see this discussion for more detail). However, if you do, you’ll simply trigger another rule warning you about process identities being in the local administrators group. You just can’t win.

By the way, if you do decide to switch the identity to a domain user, and you decide to switch it back, it’s not so simple. The service account UI only allows the selection of managed accounts, and Local System isn’t one of them. If you do need to switch back, just run the PowerShell scripts listed in this Technet article.

Either way you go, you’ll simply want to deactivate the offending rule.

2. PowerPivot: The Analysis Services instance runs in tabular mode, but the configuration setting that specifies this mode is turned off.

Sounds frightening. However, opening up the alert reveals the true problem.

The issue is actually that the rules analyzer can’t see that the tabular mode setting is set properly, so it assumes the worst, and alerts you. The reason that it can’t see it, is that the account used by health analyzer doesn’t have access to the file on the file system. That account is typically the farm account, and granting that account access to the file will prevent the alert from reappearing.

Or you can turn it off –  your choice. Given that there’s little chance of something sneaking into the PowerPivot instance and switching the configuration away from tabular, you’re probably pretty safe.

3. PowerPivot: MidTier process account should have ‘Full Read’ permission on all associated SPWebApplications

This one matters, especially if you need to do data refresh. Don’t turn it off. The detail on this alert doesn’t really add anything, so I’m not showing it here.

By “MidTier” it means the process account running PowerPivot. If you used the (very good) PowerPivot Configuration Tool that ships with SQL Server 2012, and selected the defaults, the chances are that the account is your farm account. If that works for you, and your farm account has access to all of your content, well you likely aren’t getting this alert, but in my case, I don’t typically give the farm account access to the site collections.

In my recent case, I needed to do two things – change the account used by PowerPivot to a different account, and then grant that account access to the farm content.

Firstly, if you’re unsure of what account PowerPivot is using, navigate to Security-Configure Service Accounts. Your list of pools/services will vary, but once you select the one used by your service application, you’ll see it in the list

Changing the Application Poll for PowerPivot

If you need to change the account, you’ll need to navigate to your PowerPivot Service Application. From Central Administration, select Application Management-Manage Service Applications, and then select (but don’t click through!) your PPSA.

With the PPSA selected, click on the Properties button. Then, in the resultant dialog, select, or create the application pool that you wish to use for PowerPivot.

Take note of the managed account that you will be using for the application pool, as that is the account that will need full read access in the next step.

Setting Permissions for the PowerPivot Application Pool

You could go through your site collection and add the service account to any relevant groups, etc, but the easiest way to accomplish what is necessary is to treat it the same way that Search does the default content access account, and add it to the User Policy for the relevant SharePoint web applications. To do this, from Central Admin, navigate to Application Management-Manage Web Applications. Next, select the web application in question (again, don’t click through), and click the “User Policy” button in the ribbon.

If your account is not already listed, click add users and follow the prompt. When presented with the option, select “Full Read” for the permission level.When complete, you should see an entry similar to the following

Repeat this process for all applications in the farm. Once this is complete, PowerPivot should have the permissions that it needs, and you should receive no further alerts of this type.

4. PowerPivot: Usage data is not getting updated at the expected frequency.

This alert may or may not be important to you, but I do recommend getting it fixed. PowerPivot maintains usage data in its service application database, and the service application can surface that data to administrators in Central Administration. To see this, simply navigate to the PowerPivot service application in Central Administration, and click through it to manage.

It actually uses PowerPivot workbooks to surface the information, so if PowerPivot isn’t set up correctly, you will see errors. In addition, these workbooks need to be refreshed from the original data source. This refresh is performed by a timer job, which updates the underlying workbook. This Health Analyzer job looks at the most recently updated date of the workbook, and fires the alert if it hasn’t been updated recently. This is good, because in a default installation, it won’t be.

There is a bug in the setup procedure that prevents the correct permissions from being applied to the service application database (see this discussion for more detail – the bug is still there in SQL 2012 RTM). Fortunately, the fix is relatively straightforward.

You’ll need to run SQL Server Management Studio and connect to the SQL server hosting your PowerPivot Service Application database. Open the database, and navigate to Security-Users. You should see your service account already there (if not – add it…), then right click on it and select properties. In the resulting dialog box, select Membership. If not already selected, you’ll need to check both the db_datareader and db_datawriter roles.

Once that’s done, the service application will be able to use the usage data for PowerPivot.

However, this will not update the relevant worksheet immediately. If you want to do this, you need to navigate to the “PowerPivot Management Dashboard Processing Timer Job” (available in Central Administration via Monitoring-Review job definitions), open it up and click the “Run Now” button. This should refresh the worksheet. The dashboard will then work properly, and the alert should stop firing (at least for now..).

5. PowerPivot: ADOMD.NET is not installed on a standalone WFE that is configured for central admin

You will see this alert if you’re using PowerPivot for SharePoint from SQL Server 2012. ADOMD.NET is a dependency for PowerPivot and the Health Analyzer is alerting you to the fact that it is not installed. The problem is that it IS installed. What’s happening? The Health Analyzer is looking for ADOMD.NET 11.0.0.0 (the one that ships with SQL Server 2008 R2). The ADOMD.NET that ships with SQL Server 2012 is 11.0.2100.60. This of course fails the check, and fires the alert. (For more info check out Trevor Seward’s article)

What’s the solution? Disable the rule. You run the risk of not noticing if the file gets removed, but I bet you’ll notice that when PowerPivot stops working.

Below points added 2012-09-07

6.  PowerPivot: Registry settings for Microsoft.AnalysisServices.ChannelTransport.dll are not valid after you install the SQL Server 2008 R2 version of the MSOLAP provider on this computer

This alert will show up most commonly in larger farms – farms that have separated the shared services roles from the front end server roles, and it will show up on the front end servers. The problem  occurs because although the PowerPivot features get deployed to all servers, the deployed assemblies do not get registered properly in the Global Assembly cache. The PowerPivot install registers them so this shouldn’t appear on the application server(s).

The solution is to register them manually on all of the affected front end servers.

To do so, open a command prompt in administration mode. Once open, navigate to:

C:WindowsassemblyGAC_MSILMicrosoft.AnalysisServices.ChannelTransport

Once here, there will be another subdirectory that will depend on the version of PowerPivot for SharePoint installed. The SQL 2012 version will start with 11, the 2008 version with 10, etc. Navigate into that folder. Once in that folder, run the following command:

C:WindowsMicrosoft.NETFramework64v2.0.50727regasm.exe Microsoft.AnalysisServices.ChannelTransport.dll

That should be all that you need to do to fix this one. It will also let PowerPivot work correctly, which is of course a good thing.

7. PowerPivot: Secondary Logon service (seclogon) is disabled

The secondary login service is a built in Windows service, and it is used by PowerPivot to generate the thumbnails that appear in the PowerPivot gallery. It is set to start up manually in Windows by default. According to the PowerPivot Health Rules reference, this should be sufficient for correct operation. Unfortunately, in some cases it isn’t, the service never starts, the Health Analyzer thinks that the service is disabled, and the thumbnails don’t get built.

Fortunately, the fix for this is simple. Login to each of the servers in the farm, start the service, and set its start up options to automatic. That’s it.

 

This represents the bulk of the Health Analyzer issues with PowerPivot that I’ve come across. If you have others, or I’m out to lunch on any of my analysis, please drop me a comment – I’m keen to hear.