Taking your PowerPivot server off the network

Do you travel with your laptop? Are you running Windows Server 2008 (or R2) on it? Is it a 64-bit OS? Does it have enough memory to run a VM of the PowerPivot server components? (4GB will just do it, but 8GB is better – with 6GB reserved to the SharePoint image) Are you running SharePoint 2010 and PowerPivot on it?

If the answer to all of these questions is YES (and there is an amazing number of people who do use their laptops like this), then read on. If you don’t then, still read on if you are a geek. If you try to take your laptop off the network (i.e. in a plane at 20,000 ft), then you will run into an issue around the construction of a valid Windows token. You will get the following error:

Just before Excel Services calls PowerPivot (at the OLEDB provider level), it attempts to translate its claims token to a Windows token for establishing a “Windows environment” for PowerPivot. With CTP3, this translation requires access to the domain controller for where the account lies – and obviously that is not available as you are disconnected from the network. Ultimately the system should use cached credentials if they are available, but that does not happen in CTP3. To solve the problem, we can either: (1) install a local domain controller on the machine – this is surprising easy but a bit of an overkill for this situation. An alternate approach is to setup Excel Services (and some of the PowerPivot components) to use the unattended execution account (aka, the “NONE” authorization setting for the connection in Excel). In the rest of this posting, I’ll talk about how to do that.

(Sorry about the quality of the screenshots. This wasn’t originally developed for my blog)

Steps to follow:

0 — Get your machine up and running on your home domains

1 — Do regular NEW FARM installation on the machine. Give the same account for both the farm administrator and the SQL services, i.e. click on "Use a single account . . ."

2 — Ensure everything is running correctly.

3 — Go into the Excel Services service application and change the Unattended Account to be "PowerPivotUnattendedAccount" (created as part of a NEW FARM installation)

Run Central Administration and click on "Manage service applications"

Click on "ExcelServiceApp1" and then on "Global Settings"

At the bottom of the page, in the section titled "External Data", fill in PowerPivotUnattendedAccount" as the unattended service account

4 — You must set all PowerPivot workbooks to use "NONE" as their Excel Services authentication technique. THIS MEANS ALL OF YOUR DEMO WORKBOOKS.

Here is how to do it:

a — In Excel desktop, go to the Data ribbon and click on "Connections"

b — Select the ‘Sandbox’ data connection (likely this will be your only connection) and click on Properties…

c — Go to the Definition tab and at the bottom click on "Authentication Settings…"

d — Select "None" and then OK, then OK, and Close.

e — Save the workbook.

5. If you are going to demo the PowerPivot Mgmt Dashboard, then there are three workbooks that you must modify to use NONE. These workbooks are located at:

a — Go to Central Admin and click on "PowerPivot Management Dashboard" at the bottom of "General Application Settings".

b — On the left-hand side, click on "All content"; and then "ITOps Workbooks" document library; and then on the GUID which represents the PowerPivot service application (most likely just one subfolder)

c — Click on 1033 or the locale for the being rendered) and you will see 3 workbooks. Modify those as per the steps outlined above in #4.

You are all done now and the PowerPivot Mgmt Dashboard should work now.

REMEMBER THAT YOU MUST MODIFY ANY AND ALL WORKBOOKS THAT YOU ARE USING TO HAVE THE ECS AUTHENTICATION SET TO "NONE" TO GET YOUR WORKBOOK TO RENDER UNDER ECS.

You are all set to go . . .