Single Server Install with Dedicated SQL Server

DRAFT: THIS IS A DRAFT OF A WHITEPAPER CURRENTLY BEING WRITTEN. IT MAY BE UPDATED AND WE INVITE YOU TO POST COMMENTS WITH SUGGESTIONS, CORRECTIONS AND QUESTIONS TO THIS PAGE. WE WILL CONSOLIDATE THEM AND INTEGRATE THEM BACK INTO THE BODY OF THE POSTING. AT SOME FUTURE TIME THIS INFORMATION WILL BE PART OF AN OFFICIAL MICROSOFT WHITEPAPER – BUT UNTIL THEN, WE CAN ALL BENEFIT FROM YOUR INPUT. THANKS.

Use this document to configure Microsoft SQL Server PowerPivot Add-in for SharePoint Server 2010 on a single server SharePoint 2010 farm with a dedicated SQL Server. Please note that SharePoint 2010 requires a 64-bit platform and Windows Server 2008 SP2 or later.

You will be guided through the steps to create a farm that looks like the following:

Note: These instructions are a modified version of the “EXISTING FARM” installation instructions but simplified for this 2 server environment.

1. Resources and Links

You can download the Microsoft Office 2010 SharePoint Beta and Microsoft SQL Server 2008 R2 November CTP from:

• Office 2010 SharePoint Beta: http://www.microsoft.com/downloads/details.aspx?FamilyID=77c30c6c-47fc-416d-88e7-8122534b3f37&displaylang=en
• SQL Server 2008 R2 November CTP: http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

1. Install SQL Server on the machine you designate as the DB Server

This step installs the SQL Server relational engine that serves as the backend for your SharePoint farm configuration, administration and content databases.

a. From the SQL Server build location, launch setup.exe and install the SQL Server relational engine.

b. Select both Database Engine Services and Management Tools.

c. Ensure that Named Pipes and TCP/IP are enabled in the SQL Server Configuration Utility.
To do this, go to All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager. Click SQL Native Client 10.0 Configuration > Client Protocols and ensure that the Named Pipes and TCP\IP client protocols are enabled. If applicable, repeat this step for the 32-bit version of the SQL Native Client 10.0 Configuration (32bit). Also, click SQL Server Network Configuration > Protocols for MSSQLSERVER and make sure that Named Pipes and TCP/IP are enabled.

d. You must open the server’s firewall ports for SQL Server access. In this case, this is TCP port 1433. To do this, click Start > Run, type firewall.cpl. Click Allow a program through windows firewall. You will find an option to add a port there.

2. Install SharePoint 2010 on the PowerPivot Server

a. Navigate to the SharePoint build location, right-click PrerequisiteInstaller.exe and select Run as administrator.

b. After step a. finishes successfully, from the SharePoint build location, run setup.exe.

c. If you see the message, below, install KB971831 from http://go.microsoft.com/fwlink/?LinkID=160770 and then run setup.exe.

Important: If you are running Windows Server 2008 R2 (Windows 7 Server), setup will not prompt you to install a QFE. Install KB976462 from http://go.microsoft.com/fwlink/?LinkID=166231 and then run setup.exe

d. Accept the terms and conditions and click Continue.

e. Click Server Farm and then click Next.

f. On the Server Type tab, choose Complete.

g. On the File Location tab, leave the default settings and click Install Now.

3. Run the SharePoint configuration wizard on the PowerPivot Server

a. After the installation in Step 3 is complete, you will get a prompt asking if you want to run the Configuration Wizard. Verify that the check box is selected and click Close.

b. On the Welcome page, click Next.

c. Click Yes.

d. On the Connect to a server farm page, select Create a new server farm, then click Next.

e. On the Specify Configuration Database Settings page, specify the machine name of the database server and the Windows account (Needs to be a domain account) used to access the database. This must be the account used to install SQL Server.

f. On the Specify Farm Security Settings page, type a pass phrase that meets the minimum requirements.

(Important: Make note of your pass phrase, as you must have it to add machines to your farm).

g. On the Configure SharePoint Central Administration Web Application page, accept the default port or specify a port number of your choice. Leave the default security settings (NTLM) and click Next.

h. On the Completing the SharePoint Products and Technologies Configuration Wizard page, click Next to start the configuration process.

4. Run the Initial Farm Configuration Wizard on the PowerPivot Server

At this point, you will now configure the default web application, site collection, and Excel Services with your farm configuration.

a. On the Configure your SharePoint farm page, verify that the Walk me through the steps using the wizard. option is selected. Click Next.

b. Choose the service account that SharePoint will use to provision services. You can use an existing managed account or create a new one.

c. On the same page, you can choose the SharePoint services that you want to run on your server. Excel Services and Secure Store Service are required to successfully configure PowerPivot on your server. Make sure these two options are selected and click Next.

Note: If you have chosen many services, the processing may take a long time to complete. Also, it is a good practice to only install the services you need to reduce the load on your server.

d. It is recommended that you create site collection here. Enter a name for your default site collection and then click OK.
This completes the configuration of your SharePoint farm.

e. To complete this configuration, click Finish.

f. You must open the server’s firewall ports for SharePoint Web access – which are port 80 (this should already be opened under the program of World Wide Web Services (HTTP)) and the SharePoint Central Administration Web Application port specified in Step 4f. To do this, click Start > Run, type firewall.cpl. Click Allow a program through windows firewall. You will find an option to add a port there.

g. Finally, you will need to set a Key for the secure store service application that was created for you in 5.c . To do this, click Start > All Programs > Microsoft Office Server 14 and then click SharePoint 4.0 Central Administration. Navigate to Application Management > Manage Service Applications, select Secure Store Service and then click Manage at the top of the page. At the top of the page, click Edit and then click Generate New Key. You will be prompted for a passphrase. (This is used to encrypt the credentials stored in the Secure Store Database.) Please enter a passphrase of your choice here.

5. Run SQL Server ‘Existing farm’ setup on PowerPivot Server

This step installs SQL Server Analysis Services in SharePoint Integrated mode on your PowerPivot server.

a. From the location where you saved your download of SQL Server, launch setup.exe.

b. Click Installation > New SQL Server Stand-alone installation. Select the default option on each page of the wizard until you reach the Feature Role page. Select the Enter a product key option and leave the text box blank when prompted for a key.

c. On the Setup Role page, select the Analysis Services with SharePoint Integration option, and then select Existing Farm from the drop down menu.

d. Click Next.

e. Click Next again.
The Instance Configuration page should look similar to the following image. For now, please keep the instance ID as GEMINIBI.

f. Continue through setup and select defaults until you reach the Server Configuration page

g. Continue selecting defaults until you get to the Analysis Services Configuration dialog. Make sure to click on Add Current User to ensure that your service account has access to the Analysis Services instance.

h. Continue through the rest of the Setup wizard and select the default for each option, then click Install.

6. Deploy PowerPivotWebApp solution to web application(s)

a. On the PowerPivot Server, open the central administration website and go to System Settings -> Manage Farm Solutions -> PowerPivotWebApp.wsp -> Deploy solution.

b. Choose the web application that you want to deploy PowerPivotWebApp.wsp to and click on OK.

c. Repeat step 2, on other web applications that you want the PowerPivotWebApp.wsp to be deployed.

7. Create a PowerPivot service application.

a. On the WFE go to Central Admin -> Home -> Application Management -> Manage service applications-> New-> SQL Server Analysis Services Mid-Tier Service.

b. Choose existing application pool from the list or create a new application pool.

c. If it is unchecked, select the Add the Service Application to the farm’s default group check box at the bottom of the form, and click OK.

8. Activate the “Analysis Services Integration Site Collection Feature”

a. Open your default web site (http://wfename) and click Site Actions -> Site Settings -> Site collection Administration -> Site collection features.

b. Click Activate.

c. Repeat from Step 4 to activate the feature on all site collections that you want the feature to be activated.

11. Configure the PowerPivot Unattended account (Optional)

If you will be allowing users to use the PowerPivot service account to schedule a data refresh, you will need to set an ‘unattended’ account. To do this:

To create a target Secure Store Application ID :

a. Click Start > All Programs > Microsoft Office Server 14 and then click SharePoint 4.0 Central Administration. Navigate to Application Management > Manage Service Applications, select Secure Store Service and then click Manage at the top of the page.

b. On the Ribbon, click New.

c. On the Target Application Settings page, enter a name of your choice in the Target Application ID and the Display name fields, specify an email address and click Next.

d. On the Credentials Field page, accept the defaults, and click Next.

e. On the Target Application Administrators page, enter a user who has access to this target application. You can enter the account you are using to create the target application ID.

f. To return to the Manage page for the Secure Store Service Application, click OK. You should see your target application id on this page.

g. To set the credentials for this application id, select the your ID check box, and click Set on the Ribbon.

h. On the Set Credentials page, in the Credential Owner text box, type the PowerPivot Service account that you specified as an AS Administrator when you ran Analysis Services setup.

i. In the Windows User Name and password text boxes, enter the credentials of a target application administrator. This should be one of the users you specified in step e. or the farm administrator.

Also, note that the two accounts that you need to specify can be the same if the PowerPivot service account is also a target application administrator for your application id.

To set the PowerPivot Unattended account:

a. Navigate to Application Management > Manage Service Applications, select Secure Store Service and then click Manage at the top of the page

b. After the page loads, click Configure Service Application Settings from the list of actions on the right, and on the page that pops up scroll down to the Data Refresh section.

c. Enter the target application ID you created in earlier in the Unattended Account text box and click OK.

9. Configure the Microsoft ‘Geneva’ Claims to Windows Token Service

This needs to be configured on all the servers that are running Excel Calculation Services. In our current configuration this would be the WFE, PowerPivotServer1, PowerPivotServer2 and PowerPivotServer3.

a. If it is running, stop the Microsoft “Geneva” Claims to Windows Token Service.

b. In a text editor, open the configuration file \ProgramFiles\Microsoft Geneva Framework\ReferenceAssemblies\c2wtshost.exe.config XML file.

c. Replace the entire <allowedCallers> XML structure with the following:

<allowedCallers>

<add value="WSS_WPG" />

<!– <clear /> –>

<!– <add value="NT AUTHORITY\Network Service" /> –>

<!– <add value="NT AUTHORITY\Local Service" /> –>

<!– <add value="NT AUTHORITY\System" /> –>

<!– <add value="NT AUTHORITY\Authenticated Users" /> –>

</allowedCallers>

d. Save and close the file

e. Start the “Microsoft “Geneva” Claims to Windows Token Service”. (If necessary change the startup type for this service to ‘Automatic’)

f. Reset IIS by typing iisreset at an administrator command prompt.

10. Enable usage data collection and choose events that trigger data collection

a. On the Home page in Central Administration, click Monitoring.

b. In the Monitoring section, click Configure usage and health data collection.

c. Select the Enable usage data collection check box and select the events that you want to monitor.

d. Click OK.

12. Configure file size limits (Optional)

Out of the box, SharePoint allows you to upload files up to 50MB in size and Excel Services allows you to view files as large as 10 MB within a browser. If users will be publishing and interacting with larger files, you can change these settings.

To increase Web Application limits:

a. Go to SharePoint Central Administration > Application Management > Manage Web Applications.

b. Select SharePoint-80 and then select General Settings > General Settings from the ribbon on the top.

c. Change the Maximum Upload Size setting to the MB limit you want to set. (The maximum allowed by SharePoint is 2047MB.)

To increase Excel Services limits:

a. Navigate to SharePoint Central Administration > Application Management > Manage Service Applications.

b. Select your Excel Service Application.

c. On the Ribbon, click Manage.

d. Click Trusted File Locations and on the following page, select your trusted location. (Typically, this is listed as http:// in the Address column.)

e. In the Workbook Properties area, set the Maximum Workbook Size property to 2000 and the Maximum Chart or Image Size to 100 MB.

13. Confirm PowerPivot Setup

a. After you install the Excel 2010 and the PowerPivot Add-in for Excel 2010, use the PowerPivot Add-in to create a workbook.

b. Publish the workbook to the Shared Documents folder on the default Web site (http://wfename).

c. On the WFE open the default site in a browser, navigate to Shared Documents, right-click the workbook you just published and select Copy shortcut.

d. Open SQL Server Management Studio, click Connect > Analysis Services and paste the link you just copied into the Server Name field.

e. Click Connect.
A connection should appear in the in the Object Explorer and you should be able to browse the cube in your Excel workbook. This confirms that you have PowerPivot set up correctly.

Now that you have successfully installed and configured PowerPivot, you might want to configure Reporting Services on your SharePoint farm to enable additional functionality. Use the Reporting Services – Installation and Configuration document to to install and configure Reporting Services in SharePoint Integrated mode. You can then use Report Builder to create reports off PowerPivot workbooks that you have been published to a PowerPivot Gallery.

I. Troubleshooting

Normally, when performing the above steps the firewall ports for Excel Services and PowerPivot Services will be opened so that the farm will work without a problem. But if there is a problem, you may need to manually open the following firewall ports:

1) Open Excel Services port 32843 on the PowerPivot Server.

2) If you are still running into problems, you may need to open up the ports for the Analysis Services Engine service. To do this:

a. On the PowerPivot Server, open up firewall ports 2382 and 2383.

b. Again, on the PowerPivot Server, modify the Windows Firewall settings to “Add program…” – and add $drive$\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\bin\msmdsrv.exe so that it can communicate through the firewall.