Lydia Bronze

Step-by-Step Guide to Installing SharePoint with SQL 2012 PowerPivot, PowerView, and Reporting Services

May 22nd, 2012 | Author: | Filed under: Dev / Test, SharePoint | Tags: , , , , , , , ,


We get a lot of requests for templates that include BI solutions. Recently, we published a new template containing SharePoint 2010 Enterprise SP1, SQL Server 2012 Business Intelligence Edition with PowerPivot, PowerView, SSRS (Native Mode and SharePoint Mode), SSAS (Multidimensional and Data Mining Mode, PowerPivot for SharePoint, Tabular Mode) and PowerPivot features for Excel 2010.

* Editor’s warning: This post is long, but worth it!

The installation of this server is not a traditional SharePoint Server installation. It can be a bit complicated and quite confusing. I have decided to make your life a bit easier by writing this post with a step-by-step guide to installing SharePoint Server 2010 With SQL 2012 PowerPivot, PowerView and Reporting Services. I would like to note that this server is good for Development/Test environments. In a production environment it is typically not best practice to install all of those items on a stand-alone server.

If you want to test it, I created a Pre-Configured Environment here with the below virtual machine

Ok, Let’s begin!

1. Our first step will be installing and configuring a Domain Controller on our Windows Server 2008 R2 Server.

2. Before we start the installation process we need to create user accounts for the SQL, PowerPivot and SharePoint Services. Navigate to the Active Directory Users and Computers. Click on Start -> Administrative tools -> Active Directory users and computers.

3. Navigate to your Domain -> Users -> New -> User. Create the following user accounts:

    • SQLSvc (SQL Server database engine account)
    • SPAdmin (SharePoint Admin Account)
    • SPFarm (SharePoint Farm account)
    • SSAS (SQL Server Analysis Services Account)
    • SSRS (SQL Server Reporting Services Account)

 

 

 

 

 

 

 

It’s best practice to create an account for each SQL and SharePoint service. The Service accounts should be added to the “Administrators” group as local administrators on the server and as Domain Administrators.

4. Our next step will be installing SharePoint 2010 Enterprise Edition. Before starting the installation of SharePoint, I recommend reading the following article about hardware and software requirements.

5. Install SharePoint Server 2010 Software prerequisites.

Sometimes, the installation of prerequisites fails; I recommend trying again. If that still does not work, or you don’t have Internet access from your server, you can download them. Again, see the article for hardware and software requirements.

Once the installation of prerequisites completes successfully, this is what you will see:

Now, you can click on Finish and continue to SharePoint Server installation. Click on “Install SharePoint Server” and choose your preferred type of installation (Standalone or Server farm). I prefer the Server farm because it gives me more flexibility to be able to add additional SharePoint Servers to the farm in the future.

6. Next, the installation is in progress.

Important: When the installation is complete, don’t run the configuration wizard.

If you don’t have SP1 for SharePoint installed you can install it at this point.

7. Our next step is Installation of SQL 2012 BI Edition. Click on the install file to open the SQL Server Installation Center.

8. Click on the Installation link on the left side of the menu and then click on the option to install a new stand-alone SQL Server.

9. At this point, the installation will check some Setup Support Rules. Please take care of any warnings or issues before continuing with the setup process.

10. The setup might warn us that we are installing SQL Server on a domain controller. Since we are installing everything on a stand-alone server, we cannot avoid installing SQL Server on a domain controller. You can click on Next and continue the installation.

11. On the Setup Role screen, select the SQL Server Feature Installation option.

We will get back to this screen a couple times in order to install additional instances of SSAS in Tabular mode, or PowerPivot for SharePoint. Click on Next and continue with the installation.

12. Because we are installing a stand-alone server, we should select all features available in the Feature Selection screen.

13. Select a name for the Database Engine instance and the Instance ID, and click on Next.

14. The Server Configuration screen is where we define which accounts will be used in order to run each of the services. We already created all necessary users in section 3.

15. In the Database engine configuration, select the Windows authentication mode and add user permissions.

16. Our next step is configuration of Analysis Services. Since we are going to install three instances of SSAS (Multidimensional, Tabular and PowerPivot) on this server, we will first install the Multidimensional and Data Mining Mode.

Choose the Multidimensional and Data Mining Mode option and click on next.

17. In the following screen, choose the Install only option and click on Next.

18. Click on Next until the installation process starts.

Wait for the process to finish.

19. Up to this point in our configuration, we have already installed and configured one instance of SSAS in Multidimensional and Data Mining Mode. Our next step will be installing and configuring the other two types of SSAS services available for installation in SQL 2012. The first is SSAS in Tabular Mode, and the second is PowerPivot for SharePoint Integrated Mode. 

20. We will start by installing SSAS in Tabular Mode. Click on the install file to open the SQL Server Installation Center. Continue through the Setup Support Rules until you are asked to choose the installation type. Choose to perform a new installation of SQL Server 2012.

21. In the Setup Role screen select the SQL Server feature option.

22. In the feature Selection screen, choose the Analysis Services feature and click on Next.

23. Name your instance; I suggest naming it with a legible name, such as “TABULAR”, and click on Next.

24. Define SQL Server Analysis Services service account and click on Next.

25. In the Analysis Services configuration screen choose the Tabular Mode option, add an administrative user account, and click on Next.

Navigate through the installation wizard until the installation starts. After the installation ends successfully, we can continue onto our next step.

26. Upon completion of the installation of SSAS in Tabular Mode, we are now going to install SSAS for PowerPivot for SharePoint.

I will skip over steps in section 8 – 10 because they are the same as in the Tabular Mode installation.

On the Setup Role screen select the SQL Server PowerPivot for SharePoint Installation option.

Notice that the option to add an SQL Server database relational engine to the installation is checked. Since we already installed a new instance of SQL Server Relational Engine earlier, you should uncheck this option and click on Next.

27. Continue to the feature selection screen. Notice that the features have been selected for you and click next.

28. During the Setup Rules check process we get the following warning:

“This warning is caused by unselecting the option to install the database engine on the previous Setup Role screen.”

If you don’t have any other issues with the rule check, continue and click on Next.

29. The instance name should be PowerPivot. In order to keep things clear, I suggest leaving the instance ID as “PowerPivot” and then click on Next.

30. Define SQL Server Analysis Services service account and click on Next. Continue with the installation process, and wait until the installation completes successfully.

31. Our next step will be the configuration of the PowerPivot and SharePoint servers. This will be done through the PowerPivot configuration wizard. It’s important to say that in order to successfully perform all of the following steps you must have SharePoint 2010 SP1 installed on your server.

32. Click on Start -> All Programs -> SQL Server 2012 -> Configuration Tools -> PowerPivot configuration Tool.

33. In the following screen click on the “Configure or repair PowerPivot for SharePoint” option and wait for the running process to finish.

34. The following screen shows all of the tasks that need to be done by the configuration tool.

You should pay attention to a couple of things. Go over all tasks listed in the wizard and make sure that the credentials of the service accounts and other properties are configured correctly.

Put your farm account credentials and a passphrase for your SharePoint instance. Then, continue with the additional tasks below.

When you finish, click on “Validate” and after the following screen appears, click on “Run”. If the validation did not complete successfully, correct the gaps and validate again.

This is how the wizard looks initially:

And this is how the wizard should look after the validation. Once the validation completes successfully, click on Run. Exit the wizard after it finishes running.

35. Navigate to the following folder:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

Run the following command:

PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

36. Navigate to SharePoint Central Administration.

37. Navigate to Application Management -> Manage Services on the Server.

38. In the following screen stop the ‘Claims to Windows Token Service’ and start it again. Then perform IIS reset.

39. Navigate to Application Management -> Manage Server Applications -> Default PowerPivot Service Application.

40. If you see the following screen without any errors it means you have done everything correctly!

41. In order to create PowerPivot workbooks, you need the PowerPivot Add-in for Excel. You can download the add-in here: http://www.microsoft.com/en-us/download/details.aspx?id=29074.

Congratulations! If you’re reading this, it means you have a SharePoint server with full BI stack up and running.

Related posts:

62How to install SharePoint Server 2013 preview with SQL Server 2012 SP1 CTP3 SSRS, SSAS, and PowerPivot and PowerView CRMPortalStep-By-Step Guide to Installing Microsoft Dynamics CRM 2011 Server 05 - Setup Support RulesHow to Setup SSRS in SharePoint Integrated Mode
About the author:
Lydia Bronze ( @LydiBee ) , Cloud Content Architect at CloudShare, is an experienced technologist with a diverse and deep background in infrastructure technologies, information security and technical training. Lydia's proven ability to architect, implement and provide solutions for advanced infrastructures using Microsoft and Open Source technologies makes her an invaluable asset to the team. She brings technical experience from a variety of industries, including the Defense and Finance sectors. In addition, Lydia has extensive SharePoint experience and has implemented corporate environments as well as custom SharePoint solutions for organizations of all sizes. Lydia is continuously seeking new challenges that will allow her to utilize her technical abilities.
  • Mike

    Can you use bigger images?

    • Lydia Bronze

      Thank you for your comment. Due to technical constraints the full resolution was lost. We will address this in future posts.

      • Kevin_walker_75

        ????? “The Service accounts should be added to the “Administrators” group as local administrators on the server and as Domain Administrators.” ????? create multiple user accounts and add to DOMAIN ADMINS ????

        • Lydia Bronze

          Hello Kevin,
          This is in case you are installing an environment on a single server. There is no ‘Local Administrators’ group on a domain controller, so the service accounts should be added to ‘Domain Administrators’ group.

          If you are installing a multi server environment adding your service accounts to local ‘Administrators’ group is sufficient.

      • http://www.facebook.com/john.denver.73 John Denver

        On step 14 the image cannot be seen. Which user account goes to which service?

  • Gurdeep Sira

    Hi Lydia, great article! I installed SQL Server 2012 Enterprise Edition with my SP2010 SP1 farm. Can I still do everything listed in this article or do I need to change versions? Although I use cloudshare, I ask due to a farm I run and built from scratch.

  • Guest

    Went to install PowerPivot and it would not install with “SharePoint installation requirement for PowerPivot for Sharepoint” Failed.  Can I not have the 2012 SQL server be on a different machine than the share point server? Or would I have to install sharepoint twice? 

    • Lydia Bronze

      Hello,
      If you would like to install a Multi-Server Installation of PowerPivot for SharePoint you don’t need to install SharePoint twice. Install your SharePoint and database server and then install PowerPivot for SharePoint on the SharePoint server.

      For more information you can follow the steps here: http://technet.microsoft.com/en-us/library/ee210708.aspx

      Please pay attantion to section 10 in the Technet article, if you already have a database server and you don’t need to create a new instance of the Database engine to your installation please uncheck this option. 
      If you have more questions feel free to ask.

  • Oldbay71

    Great post! Thanks. One thing though, step 34 is a missing a very critical point. The default web app for PP should be something like http://PowerPivot:54322
    The port can be whatever you like just so it’s not already in use and you can remember it.

    • Lydia Bronze

      Thank you, this is a very important remark.

    • Ajay Khanna

      port number is not necessarily required, if you got the host name in dns no need to add port number, or can even add host name in hosts file and use default port 80

  • guest

    Not sure why the service accounts should have domain admin priviliges ,care to elabborate ?

    • Lydia Bronze

      Hello,
      Thank you for your comment, this is a very important question. 
      This is relevant if you intent to install a single server farm like I did.
      After a Windows Server 2008 has been promoted to be a Domain Controller the ‘Local Administrators’ group on this server will become the ‘Domain Administrators’ group. In order to grant sufficient permissions to your service accounts you should verify that they are part of the ‘Domain Administrators’ group.

      If you would like to install a multi server farm your service accounts should belong to local administrators group on the servers. 

  • Fahim Kanji

    Hi Lydia,

    I’m trying to install Sharepoint 2013 with SQL Server 2012 SP1 to test the SSRS integration functionality.  I’ve not been able to successfully get a report to look up data (on the same server) and present it, due to the C2WTS service not giving me windows token.

    I’ve seen some articles about sharepoint 2010 integration with SQL 2012 SP1 (http://www.sharepointjoel.com/Lists/Posts/Post.aspx?List=0cd1a63d-183c-4fc2-8320-ba5369008acb&ID=551) but haven’t found the suggestions to help me get past the problem.

    I was wondering if you’ve had success setting this up in a single-server environment (without AD)?  If you have, I’d love to hear your feedback.

    Cheers,

    Fahim.
    feemurk@gmail.com

    • Fahim Kanji

      Hi Lydia, 

      Wondering if you have any feedback on my situation.  Any help would be greatly appreciated.  Thank you!

      Fahim.

      • Lydia Bronze

        Dear Fahim,
        Thank you for your comment, I am sorry it took me a long time to check this issue.
        I did not get to install a single server environmet of Sharepoint Server 2013 without Active Directory yet but I can try and help you out.

        I need some additional info in order to help you. Is your c2WTS service started? Does it run as the local system account? Did you manually configure the c2WTS with a list of allowed callers? Did you have a look at the ULS logs?
        You can always try out our pre-configured environment https://use.cloudshare.com/Pro/ShareEnv/A9VDTAE7L2L9

  • Alerteye

    I am confused with requirement for Claim vs Classic mode auth for PP.

    TechNet documentation http://technet.microsoft.com/en-us/library/cc262350.aspx#section2 Plan authentication methods (SharePoint 2010) that was just (Aug 5th) “entirely updated and revised” states

    But Microsoft writes “For new implementations of SharePoint Server 2010, use claims-based authentication. With this option, all supported authentication types are available for Web applications. There is no practical reason to select classic-mode authentication for new deployments, even if your environment includes only Windows accounts. Windows authentication is implemented the same way regardless of the mode that is selected. There are no additional steps to implement Windows authentication when you use the claims-based authentication mode.”

    But PowerPivot requires classic mode — http://technet.microsoft.com/en-us/library/ee210621.aspx says “PowerPivot for SharePoint is supported for SharePoint web applications that are configured to use Windows authentication.” And then of course there’s Dave Wickert’s post http://powerpivottwins.com/2010/04/22/why-powerpivot-requires-%e2%80%98classic-mode%e2%80%99-web-applications/ (and http://powerpivotgeek.com/2010/04/22/why-powerpivot-requires-classic-mode-web-applications/#more-836 for full article)

    I haven’t found docs explaining how-to PowerPivot with claims mode web app.

  • Ricky

    Hello Lydia,
    Could you help me with this case please?

    I’m tried to Setup SharePoint 2010 SP1 CU 2598354 with Sql Server 2012 Enterprise Edition but when I was run the Configuration Wizard I have a problem.
    My scenary is the following:
    I have to 4 servers:
    Server 01 – Server App
    Server 02 y Server 03 – WFE
    Server 04 – DB Server
    All servers is with Windows Server 2008 R2 SP1 x64, I installed SQL Server 2012 Enterprise in Server04 without problems and I put the permisions that need in the DB.
    In the server01 I installed SharePoint Server 2010 with SP1 and them I installed the CU 2598354 When I finished the install I run the configuration wizard but when the wizard is running in the step 3 stopped the process and I see the log and say the following:
    Now joining to farm at server DBSQLSHAREPOINT database SharePoint_Config
    08/16/2012 18:17:41 9 ERR Task configdb has failed with an unknown exception
    08/16/2012 18:17:41 9 ERR Exception: System.Security.AccessControl.PrivilegeNotHeldException: The process does not possess the ‘SeSecurityPrivilege’ privilege which is required for this operation.
    at System.Security.AccessControl.Win32.GetSecurityInfo(ResourceType resourceType, String name, SafeHandle handle, AccessControlSections accessControlSections, RawSecurityDescriptor& resultSd)
    at System.Security.AccessControl.NativeObjectSecurity.CreateInternal(ResourceType resourceType, Boolean isContainer, String name, SafeHandle handle, AccessControlSections includeSections, Boolean createByName, ExceptionFromErrorCode exceptionFromErrorCode, Object exceptionContext)
    at System.Security.AccessControl.DirectorySecurity..ctor(String name, AccessControlSections includeSections)
    at Microsoft.SharePoint.Administration.SPProvisioningAssistant.ResetAcl(FileSystemInfo fi, Boolean append)
    at Microsoft.SharePoint.Administration.SPServer.StaticProvision(SPConfigurationDatabase configurationDatabase)
    at Microsoft.SharePoint.Administration.SPFarm.Join()
    at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.CreateOrConnectConfigDb()
    at Microsoft.SharePoint.PostSetupConfiguration.ConfigurationDatabaseTask.Run()
    at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()

    This error is in the log
    So I review the policies in AD and the users don’t have policies.
    Wich is your opinion about this case? What I have to do?

    Thanks a lot.

    Regards,

  • Pingback: Stap voor stap gids installatie SharePoint met SQL 2012 | MichielvanOppen.nl

  • Fergie348

    Thanks for the write-up, it helped fill in some of the gaps! I did run into one persistent problem while configuring this within a VPC in AWS – During the PowerPivot for SharePoint install I was unable to use any domain authenticated users for the SSAS service account. I tried a lot of different configurations and my domain authentication works fine, but the installer complains that the credentials provided are invalid. Any ideas? I’d love to get PowerPivot for SharePoint installed and working correctly, but this is a full stop for me.

  • Mart

    Will this work using Server 2012?

  • Tir

    Great Post!

  • chris

    quick question… in ur steps u mention to run the command:
    PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures what does that command do ?

  • http://www.facebook.com/john.denver.73 John Denver

    Hi again Lydia,
    Sorry to keep commenting on an old thread its just that I have found your guide to be the most comprehensive method to for installing a demo of SharePoint with BI features. I am however unclear about something.
    My question is about PowerView and Reporting Services. You didn’t show how to configure SSRS once inside SharePoint. Do you have any other links to show how to do this with the current set up? I uploaded a PowerPivot wookbook into the Pivot Gallery but then cannot create a PowerView report.

  • http://twitter.com/eDeuceWVU Evan Pettrey

    Can you please go into more detail with what needs to be done to get things working with the PowerPivot Configuration Tool at Step 34? I’ve followed all of the previous steps exactly but I’m having some trouble getting this step to validate.

    Thanks in advance!

    • http://twitter.com/eDeuceWVU Evan Pettrey

      Note: I figured out the solution on my own and will leave it here for others in case they ever need it.

      Although in the (low resolution) image it says to use “localhost” as the Database Server, you actually have to use the full name (i.e. “ComputerNameSQLDatabaseName”).

      So the settings you need to use are:

      Default Account Username – DomainSPFarm (created earlier)

      Default Account Password – Set when you made the user!
      Database Server – ComputerNameSQLDatabaseName
      Passphrase – Create this now (be sure to record it as you’ll need to use it when adding other features to the farm in the future!!!)
      Confirm Passphrase – Retype the passphrase set in the above step
      SharePoint Central Administration Port – Unused random port picked here, but you can change it to any other port you choose as long as it is not in use.

      Hopefully this will save some others headaches in the future.

      • Raj

        Thanks

  • Pingback: Step-by-Step Guide to Installing SharePoint with SQL 2012 PowerPivot, PowerView, and Reporting Services | My Love with SQL Server | Scoop.it

  • Pingback: Summarizing the year 2012 | Beetalks

  • MikeA

    Lydia,

    Excellent post! I found noticed a couple weeks after installing PowerPivot 2012 that I still needed to run PSconfig because the farm member showed “Upgrade required” on the server status screen. I couldn’t find this requirement documented anywhere in the MS docs. Although I only ran it with the following parameters.
    PSConfig.exe -cmd upgrade -inplace b2b -wait -force
    I didn’t include the additional parameters you show above. Do you think I’m ok? It did clear the “Upgrade Required” flag on the server.
    Thanks again for a great post!

  • Pingback: Advancements In Technology Simplify Court Reporting. | Wisdom Health Prosperity

  • Pingback: How to Write Successful Technical Blog Content | Beetalks

  • Pingback: BI Resources and Links – SharePoint is Awesome!