Setting up a Three-Tier SharePoint Farm Including PowerPivot

This post will detail some of the challenges I encountered while setting up a three tier SharePoint 2010 farm that included PowerPivot.  Hopefully these notes will help somebody else.

Our three-tier farm will include:

    1. One server for the SQL Server 2008 R2 relational database that SharePoint will use.  (SQL)
    2. One application server that will run the central administration web site, the web application web site which will house our site collections, and a PowerPivot installation which includes an Analysis Services database running in Vertipaq mode.  –Best practice would be to have a separate server for the PowerPivot installation.  (APP)
    3. One web front end server that will render the html web pages.  (WFE)

These instructions will be based on Microsoft’s documentation, Multiple Servers for a Three-tier farm (SharePoint Server 2010.)  I will point out some of the things that confused me.  I will also share some links to blog posts that helped me overcome my confusion.  The instructions are a little confusing as there are certain hotfixes that are needed if you are running SQL Server 2008 that you do not need if you are running SQL Server 2008 R2.

Pay special attention to the Before you begin section in the documentation.  Especially the hardware and software requirements.


Gotcha #1

My first gotcha occurred with the permissions of the active directory account(s) we were using for the setup administrator and farm account.  When I ran the the SharePoint 2010 products and configuration wizard I received the following error at step 3:

Failed to create the configuration database.
An exception of type System.Collections.Generic.KeyNotFoundException was thrown. Additional exception information: The given key was not present in the dictionary.
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)


Here is an excerpt from the link that led me down the path to the solution:


“I installed 2010 once and got this problem fixed using this URL – which at one time gave me the answer, but no longer does.

This is what fixed my challenges.  I was creating a new environment with new IDs, SPInstallTest, SPFarmTest.  They had all the correct permissions on the SharePoint boxes and the SQL boxes (Server 2008 R2 and SQL 2008 R2); however, every time I tried to configure the farm I got the error mentioned above.  The problem is that the AD attributes for these new IDs do not have the correct security in AD that they need.  (this will bite you with the people picker too with multiple domains).  Go into the AD accounts you created for your farm (Admin and Farm, except I used Install), make sure Advanced Features are selected in AD.  Click on the Security tab of these SharePoint accounts, highlight “Authenticated Users” and give them “read” access.  This is also important for Search to work properly!”


I was able to find the solution in this thread SharePoint 2010 – Error while running Configuration wizard for SP 2010 server farm.

You have to read until the end where it is suggested to go to Active Directory and to edit the properties of the following accounts:

  • Setup administrator (account used to install SharePoint)
  • Farm account (account used in the SharePoint Products Configuration wizard)
  • Look at administrative and service accounts for a description of accounts you need to install SharePoint

In the security tab you need to give the authenticated users group read permission:


After that the install has worked as expected.


Gotcha #2

The first time I ran the SharePoint 2010 Product Preparation Tool on each server (APP and WFE) it failed.  The error message indicated that the .Net framework 3.5 service pack 1 failed.  After wasting about an hour, I realized that 3.5.1 was already installed before I even began.  Running the Product Preparation Tool again on each server showed success.  So in short, if the Product Preparation Tool fails the first time, just run it again.


Gotcha #3

Documentation tells you to install several hotfixes that wouldn’t install.  From the documentation:

After you install the prerequisites on each farm server, you will need to install Error message when you use the “IMSAdminBase::CopyKey” method as a part of the IIS 6.0 Compatibility components in IIS 7.0: “Exception from HRESULT: 0x80070003” on the application server first, and then on the two Web servers.

This update seems to only apply to Vista.


After you complete the Microsoft SharePoint Products Preparation Tool, you must install KB 949516 ( and KB 971831 ( You might also need to restart the server after installing this hotfix.

These also wouldn’t install.


My recommendation is to try and install these anyway as your experience may vary.  This installers for these hotfixes are apparently smart enough to know when they don’t apply.




Gotcha #4


Should you choose Windows Classic authentication or Windows Claims authentication for your first web application?  I initially chose Windows Claims authentication because it included Windows Classic plus two other authentication methods.  I figured this would give me the most flexibility.  We could start w/ NTLM and if we ever needed to switch to Kerberos we could.  Big mistake when it came to the PowerPivot installation.  I spent many hours trying to figure out what was wrong when I noticed this quote from a MSDN article:

When creating new web applications, be sure to select the Classic mode authentication option in the Create New Web Application page.  

For existing web applications, use the following instructions to verify the applications are configured to use Windows authentication. When creating new web applications, be sure to select the Classic mode authentication option in the Create New Web Application page.

1. In Central Administration, in Application Management, click Manage web applications.

2. Select the web application.

3. Click Authentication Providers.

4. Verify that you have one provider for each zone, and the Default zone is set to Windows.

Ref: Plan PowerPivot Authentication and Authorization


Another thing I messed up was kind of silly looking back on it now.

In the instructions for creating your web application using Classic Mode, item #10, the public URL section I replaced the APP server with the WFE server.  Strangely enough, this worked fine for everything in PerformancePoint.  PowerPivot not so much.


Here is the excerpt from #10:


In the Public URL section, type the URL for the domain name for all sites that users will access in this Web application. This URL will be used as the base URL in links shown on pages within the Web application. The default URL is the current server name and port, and is automatically updated to reflect the current SSL, host header, and port number settings on the page. If you are deploying SharePoint Server 2010 behind a load balancer or proxy server, then this URL may need to be different than the SSL, host header, and port settings on this page.

The Zone value is automatically set to Default for a new Web application.


You can change the zone when you extend a Web application. For more information, see Extend a Web application (SharePoint Server 2010).


You want to leave the default application server in the public URL section.  Later on, once you have PowerPivot installed and working, extend your web application in the first link directly above.



Create a Web application that uses Windows-classic authentication (SharePoint Server 2010)



Gotcha #5

Don’t enable anonymous access when creating the web application!  Excel services won’t work and the documentation doesn’t tell you that!  From the doc:


If you want users to be able to access any site content anonymously, you must enable anonymous access for the entire Web application zone before you enable anonymous access at the SharePoint site level; later, site owners can configure how anonymous access is used within their sites. If you do not enable anonymous access at the Web application level, you cannot enable anonymous access later, at the site level. For more information, see Choose security groups (SharePoint Server 2010).

After reading that, I thought I should enable it in case we ever needed it.  I was sorely mistaken.  Don’t do it.


I was trying to create a new excel services data connection. Item name drop down wouldn’t populate and test connection wouldn’t enable. Came across this:


On this same point… you don’t need to create an Excel Services data connection within Dashboard Designer to test Excel Services.  Create an Excel Services report instead.



Gotcha #6


The PowerPivot installation documentation stinks!  (PowerPivot for sharepoint existing farm installation)  It obviously hasn’t been kept up to date.

For example, #12 Create the PowerPivotService appilcation states:


b. Click Application Management, Manage Service Applications, New, and SQL Server Analysis Services Mid-Tier Service.

Well, that’s great; but, it doesn’t exist…. I used SQL server PowerPivot service application instead.


That’s just one of many examples.  A better source for how to install PowerPivot for an existing farm can be found here:  How to: Install PowerPivot for SharePoint in a Three-tier SharePoint.




Other tips…


When you are setting up Excel Services go ahead and uncheck the warn on refresh.  While you’re at it, increase the maximum workbook size to something more reasonable like 2000MB and the max size for charts to 100MB.  You’ll also want to configure the maximum file size for a SharePoint web application.  See this link:

Configure Maximum File Upload Size (PowerPivot for SharePoint)



Related Links

SharePoint 2010: Missing Server Side Dependencies

Configure SharePoint Server 2010 in a Three-Tier Farm

Creating a PowerPivot Gallery

SQL Server 2008 R2 and SharePoint 2010 Products: Better Together (white paper) (SharePoint Server 2010)

SharePoint 2010 Feature Gallery

PerformancePoint Demo – Click Demonstration.  To get the right click functionality you have to click the compatibility icon in Internet Exploder.

PowerPivot Demo

Please let me know in the comments if you have anything to add or if I have stated anything that is incorrect.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s