How to configure PowerPivot on SharePoint 2013

Hello Everybody,

Last week I studied about how to configure, get it working and test the PowerPivot on SharePoint 2013.

The best tutorial I found was at the link http://technet.microsoft.com/en-us/library/dn456880(v=office.15).aspx

I have downloaded and published the video directly here: https://www.youtube.com/watch?v=n9Qsxl3GZYk

On this post I added some configurations that not are included in this video:

1) Excel Services Configuration:

1.1 I have created an Site Collection called “BICenter” within my web application  http://portal.mabotega.local. The complete URL was http://portal.mabotega.local/sites/BICenter;

1.2 I added the BICenter Document Library (in my scenario: http://portal.mabotega.local/sites/BICenter/Documents) on the “Trusted File Locations” on the Excel Services Application configuration. For to do it, click on the “Manage Application Services” within “Application Management” section:

1.3 Click on “Excel Service Application”

1.4 Click on the “Trusted File location” link:

1.5 Add the Document Library address at the “Address’ field: http://portal.mabotega.local/sites/BICenter and Check the “Children Trusted”, and confirm in “Ok” button:

The result will be like this:

2) PowerPivot Permisions:

2.1 Run the PowerShell below changing for the appropriated values for your environment:

In my scenario I have used the URL: http://portal.mabotega.local

$w = Get-SPWebApplication -identity http://portal.mabotega.local

#this is the application pool account that runs the PowerPivot Service Application in my scenario

$w.GrantAccessToProcessIdentity(“MABOTEGA\RBTSHPSR10″)

2.2 Be sure if the command worked well:

Open the SQL Server Management Studio;

Expand Security –> Logins;

Double click on your PowerPivot application pool account (in my scenario MABOTEGA\RBTSHPSR10);

Select User Mapping;

In the “User mapped to this login” session, select the content database for your application. In my scenario http://portal.mabotega.local, content database DBSPS_Portal_Content;

In the “Database role membership for: DBSPS_Portal_Content” session, make sure that the “SPDataAccess” role is selected:

3) Secure Store Service Application

3.1 Select your “Secure Store Service Application” and set the appropriated Unattended user account to be used by Excel Service to get information on PowerPivot. In my scenario MABOTEGA\RBTSHPSR11. The Owner of this credential is the user account that will run the PowerPivot Application Pool, in my scenario MABOTEGA\RBTSHPSR10:

3.2 Open the SQL Server Management Studio;

Expand Security –> Logins;

Double click on your U accounattended account (in my scenario MABOTEGA\RBTSHPSR11);

Select User Mapping;

In the “User mapped to this login” session, select the content database that is get information. In my scenario I have followed the sample in the Microsoft video and I called the “Cities”. In the “Database role membership for: Cities” session, make sure that the “db_datareader” role is selected:

That’s it for now!

Simple Way to Get Office Web Apps 2013 Server Install and Configuration working

Installing Office Web Apps

Office Web Apps 2013 is a stand-alone server web application that provides capabilities to open and render a Microsoft Office Word, Excel, PowerPoint, or OneNote document as a web page.

Microsoft SharePoint 2013 can share the rendering service to display Office documents in those applications as a web page. Additionally, when accessed from within a SharePoint 2013 farm, Office Web Apps also enables rich editing features for those documents.

Note: You cannot install Office Web Apps on the same server as SharePoint 2013

Please follow the server preparation process in the following sections for the appropriate server, either Windows Server 2008 R2 or Windows Server 2012.

Windows Server 2008 R2 Preparation

Start by installing the following prerequisite software for Windows Server 2008 R2:

• Windows Server 2008 R2 Service Pack 1
• PowerShell V3.0 – Download Link
• .net 4.5 – Download Link
• Few Hotfixes – http://www.microsoft.com/download/en/details.aspx?id=27928 , http://hotfixv4.microsoft.com/Windows 7/Windows Server2008 R2 SP1/sp2/Fix354400/7600/free/427087_intl_x64_zip.exe , http://hotfixv4.microsoft.com/Windows 7/Windows Server2008 R2 SP1/sp2/Fix368051/7600/free/433385_intl_x64_zip.exe

Open a PowerShell command running as an Administrator and execute the following commands to install the required roles and services for Office Web Apps.

Import-Module ServerManager


## Run the following command as a single line
Add-WindowsFeature Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,Web-Security,Web-Windows-Auth,Web-Filtering,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Console,Ink-Handwriting,IH-Ink-Support

Please continue with the “Office Web Apps Installation” section below.

Windows Server 2012 Preparation

To begin, open a PowerShell command running as an Administrator and execute the following commands to install the required roles and services for Office Web Apps.

Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,InkandHandwritingServices

Download the Office Web Apps Installation

Download the Office Web Apps Server software from the Microsoft Download Center.

Office Web Apps Installation

Open and run the Office Web Apps setup.exe media to launch the setup wizard.

1. In the Office Web Apps Server 2013 Wizard, on the Read the Microsoft Software License Terms page, select I accept the terms of this agreement and then select Continue.
2. On the Choose a file location page, select the folder where you want the Office Web Apps Server files to be installed (for example, C:\Program Files\Microsoft Office Web Apps), and then select Install Now. Note that, if this folder does not exist, Setup will create it for you.
3. 
4. The Choose a file location screen on the Office Web Apps install wizard.
5. When Setup finishes installing Office Web Apps Server, choose Close.

After installing the Office Web Apps 2013 server software, you are ready to install any additional add-ins and updates. You can also install any language packs your farm requires. To install the language packs, run the setup media for each of the language packs you desire.If applicable, install the latest service pack Microsoft has released for Office Web Apps 2013 and then apply the latest service packs Microsoft has released for Office Web Apps 2013 language packs.

Finally, check for updates on Microsoft Update in the server’s control panel.

     6. Restart the server.

Configuring Office Web Apps

This section describes how to configure an Office Web Apps farm and join servers to it.

Important: Low memory conditions can cause Office document previews to fail in Office Web Apps. Verify that any servers that run Office Web Apps have sufficient memory.

On the first server for the Office Web Apps farm, execute the following PowerShell command to provision the farm:

Import-Module OfficeWebApps

New-OfficeWebAppsFarm -InternalUrl "http://owa.mabotega.local" –EditingEnabled –ClipartEnabled –TranslationEnabled -Allowhttp

Confirm the Office License with “Y”:

The following image provides an example of the expected output from the PowerShell command.

PowerShell results from configuring an Office Web Apps farm

Critical: Before you can use the Office Web Apps farm, you must add your domain to the list of allowed hosts.

Run the following PowerShell command to add your domain to the list of allowed hosts, substituting your domain for “mabotega.local”

New-OfficeWebAppsHost -Domain mabotega.local

Restart the server

You can test the Office Web Apps configuration by navigating to this URL and verifying it displays a Web app Open Platform Interface (WOPI)-discovery XML file: https://owa.mabotega.local/hosting/discovery

Note: For more information on deploying and configuring Office Web Apps, please see this TechNet article: http://technet.microsoft.com/jj219455

Adding a new Server on the Office Web Apps Farm (Optional)

Once you have provisioned an Office Web Apps farm and allowed your domain, you can join additional Office Web Apps servers to the farm.

To join additional servers, install the Office Web Apps software by following the steps in the previous section and then execute the following PowerShell command:

Import-Module OfficeWebApps

New-OfficeWebAppsMachine –MachineToJoin “owa.mabotega.local”

Configuring the Windows Firewall for Office Web Apps Traffic

On each Office Web Apps 2013 Server, you will need to set a firewall rule to allow Office Web Apps inter-farm traffic and HTTP/HTTPS traffic. Alternatively, you can disable the Windows Firewall if you choose and if you have another firewall solution.

You can set the Windows Firewall rules by navigating to the Control Panel, then click System and Security, then click Windows Firewall, and finally click Advanced settings. In the Inbound Rules area, ensure that the server allows connections on port 80 (HTTP) and port 443 (HTTPS). Add the port for the Office Web Apps inter-farm communication by following these steps:

1. In the Windows Firewall with Advanced Security window, click Inbound Rules.
2. In the Actions panel, click New rule…
3. In the New Inbound Rule Wizard window, select Ports as the Rule Type and click Next.
4. Select TCP and enter “809” for the Specific local ports. Click Next.
5. Windows Firewall Port Rule for Office Web Apps communication
6. Click Next. On the Profile screen, uncheck Public and click Next.

On the Name screen, enter “Office Web Apps Inter-Farm Communication” and click Finish.

Configuring a SharePoint 2013 Farm for Office Web Apps

Logon to the SharePoint application server that hosts Central Administration and open the SharePoint 2013 Management Shell (PowerShell), running it as an administrator. Next, enter the following PowerShell command:

New-SPWOPIBinding -ServerName owa.mabotega.local -Allowhttp

Check the protocol used by the SharePoint in its zone (by default it is https). Next, enter the following PowerShell command:

Get-SPWOPIZone

We need to change the protocol used by the SharePoint in its zone to HTTP (by default it is https). Next, enter the following PowerShell command:

Set-SPWOPIZone –zone “internal-http”

Get-SPWOPIZone

Finally, verify that Office Web Apps is working by navigating to a SharePoint 2013 document library and verify that you can open a document as a web page.

e,g.: Excel

e,g.: Word

Note: For more information on how to configure a SharePoint 2013 farm to use Office Web Apps and for troubleshooting information, please see this TechNet article: http://technet.microsoft.com/ff431687

Reference:

1) http://technet.microsoft.com/en-us/library/dn455088(v=office.15).aspx

2) http://technet.microsoft.com/en-us/library/ff431685(v=office.15).aspx

Disconnect SharePoint 2013 from Office Web Apps Server

If, for any reason, you want to disconnect SharePoint 2013 from Office Web Apps Server, use the following command example:

Remove-SPWOPIBinding –All:$true

 

For now that’s it!

Create Managed Metadata Service Application via PowerShell in SharePoint 2013

In this article, we will go through the steps to create Managed Metadata.

There are four steps which are:

1) Plan whether to use a dedicated application pool or a shared one based on various factors

To find out the application pool’s exists in your environment, try this in SharePoint PowerShell Management Console

Get-SPServiceApplicationPool

To get the list of managed accounts,

Get-SPManagedAccount

To create a new application pool with dedicated managed service account:

New-SPServiceApplicationPool -Name ManagedMetaDataAppPool -Account Domain\managedmetadata_svc

If you intend to use an existing app pool, change the name below.

2) Create the Managed Metadata Service Application:

New-SPMetadataServiceApplication -Name “MetadataServiceApp” -ApplicationPool “AppPool_MMS” -DatabaseName “MetadataDB”

3) Create the Managed Metadata Service Application Proxy

New-SPMetadataServiceApplicationProxy -Name “MetadataServiceProxy” -ServiceApplication “POFPROD_MetadataServiceApp” -DefaultProxyGroup

4) Configure the Service Application Association:

Add Managed Metadata in Service Application Association with the required Web Application manually.

We would use Power Shell given any day as I can keep the databases clean without any GUID’s.

References: http://technet.microsoft.com/en-us/library/ff607557(v=office.15).aspx

Upgrade the Managed Metadata Service Application

1) Start the MMS service instance

The following procedures start the service instance of MMS.

To start service application instances from Central Administration

1. Start SharePoint 2013 Central Administration.

   • For Windows Server 2008 R2:

     • Click Start, click Microsoft SharePoint 2013 Products, and then click SharePoint 2013 Central Administration.

   • For Windows Server 2012:

     • On the Start screen, click SharePoint 2013 Central Administration.

       If SharePoint 2013 Central Administration is not on the Start screen:

     • Right-click Computer, click All apps, and then click SharePoint 2013 Central Administration.

   For more information about how to interact with Windows Server 2012, see Common Management Tasks and Navigation in Windows Server 2012.

2. In SharePoint 2013 Central Administration, on the Application Management page, in the Service Applications section, click Manage Services on Server.

3. Next to the Managed Metadata Web Service, click Start.

2) Upgrade the MMS

To upgrade the Managed Metadata service application, you create the new service application and upgrade the database, and then create a proxy and add it to the default proxy group. You must upgrade the Managed Metadata service application before you can upgrade the User Profile service application.

To upgrade the Managed Metadata service application by using Windows PowerShell

1. Verify that you have the following memberships:

   • securityadmin fixed server role on the SQL Server instance.

   • db_owner fixed database role on all databases that are to be updated.

   • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

   An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

   Note:

   If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

2. Start the SharePoint 2013 Management Shell.

   • For Windows Server 2008 R2:

     • On the Start menu, click All Programs, click Microsoft SharePoint 2013 Products, and then click SharePoint 2013 Management Shell.

   • For Windows Server 2012:

     • On the Start screen, click SharePoint 2013 Management Shell.

       If SharePoint 2013 Management Shell is not on the Start screen:

     • Right-click Computer, click All apps, and then click SharePoint 2013 Management Shell.

   For more information about how to interact with Windows Server 2012, see Common Management Tasks and Navigation in Windows Server 2012.

3. To store the application pool that you want to use as a variable for this service application, at the Windows PowerShell command prompt, type the following command:

   Windows PowerShell

   $applicationPool = Get-SPServiceApplicationPool -Identity 'SharePoint Web Services default'
   

   
   

   Where:
   

   
   
   • 
     

     SharePoint Web Services default is the name of the service application pool that will contain the new service applications.

   
   

   This cmdlet sets the service application pool as a variable that you can use again in the cmdlets that follow. If you have multiple application pools and have to use a different application pool for a particular service application, repeat this step in the procedure to create each service application to use the appropriate application pool.

   
   
4. 
   

   To upgrade the Managed Metadata service application, at the Windows PowerShell command prompt, type the following command:
   

   Windows PowerShell

   $mms = New-SPMetadataServiceApplication -Name 'Managed Metadata Service Application' -ApplicationPool $applicationPool -DatabaseName 'Managed Metadata Service_DB'
   

   
   

   Where:
   

   
   
   • 
     

     Managed Metadata Service Application is the name that you want to give the new Managed Metadata service application.

     
     
   • 
     

     $applicationpool is the variable that you set earlier to identify the service application pool to use.
     

     Tip:
     

     If you do not use the variable $applicationPool, then you must specify the name of an existing service application pool in the format ‘Application Pool Name‘. To view a list of service application pools, you can run the Get-SPServiceApplicationPool cmdlet.

     
     
   • 
     

     Managed Metadata Service_DB is name of the service application database that you want to upgrade.

   
   

   This command sets a variable, $mms, that you use when you create the proxy later.
   

   For more information, see New-SPMetadataServiceApplication.

   
   
5. 
   

   At the Windows PowerShell command prompt, type the following command to create a proxy for the Managed Metadata service application:
   

   Windows PowerShell

   New-SPMetadataServiceApplicationProxy -Name ProxyName -ServiceApplication $mms -DefaultProxyGroup
   

   
   

   Where:
   

   
   
   • 
     

     ProxyName is the proxy name that you want to use.

     
     
   • 
     

     $mms is the variable that you set earlier to identify the new Managed Metadata service application.
     

     Tip:
     

     If you do not use the variable $mms, then you must use an ID to identify the Managed Metadata service application proxy instead of a name. To find the ID, you can run the Get-SPServiceApplication cmdlet to return a list of all service application IDs.

     
     
   • 
     

     DefaultProxyGroup adds the Managed Metadata service application proxy to the default proxy group for the local farm.

   
   

   For more information, see New-SPMetadataServiceApplicationProxy.

Reference: http://technet.microsoft.com/en-us/library/jj839719(v=office.15).aspx

That’s it for now!

Import-SPMetadatawebservicePartitionData / You do not have permission to use the bulk load statement

During a Term store import, you can incurr in a “Import-SPMetadataWebServicePartitionData: You do not have permission to use the bulk load statement”.

So the workaround is to add service account to BULK ADMIN role when you want to import the term store and remove it after operation is complete.

SharePoint 2013 Build Numbers

Just another post like this kind…

Build Number	Build Name	Component	Information Link	Download Link
15.0.4128.1014	Beta	SharePoint Foundation 2013		Download
15.0.4128.1014	Beta	SharePoint Server 2013		Download
15.0.4128.1014	Beta	Project Server 2013		Download
​	​	​	​	​
​15.0.4420.1017	​RTM	​​SharePoint Foundation 2013	​	​Download
​​15.0.4420.1017	​RTM	​SharePoint Server 2013	​	​Download
​​15.0.4420.1017	​RTM	​Project Server 2013	​	​Download
​	​	​	​	​
​15.0.4433.1506	​December 2012 Hotfix	​SharePoint Server 2013	​KB2752058	​Download
​15.0.4433.1506	​December 2012 Hotfix	​SharePoint Server 2013 (coreserver)	​KB2752001	​Download
​	​	​	​	​
​15.0.4481.1005	​March 2013 Public Update	​SharePoint Foundation 2013	​KB2768000	​Download
​​15.0.4481.1005	​​March 2013 Public Update	​SharePoint Server 2013	​KB2767999	​Download
​​15.0.4481.1005	​​March 2013 Public Update	​Project Server 2013	​KB2768001	​Download
​	​	​	​	​
​15.0.4505.1002	​April 2013 CU	​​SharePoint Foundation 2013	​KB2751999	​Download
​15.0.4505.1005	​​April 2013 CU	​SharePoint Server 2013	​KB2726992	​Download
​15.0.4505.1005	​April 2013 CU	​Project Server 2013	​KB775426	​Download
​	​	​	​	​
​15.0.4517.1003	​June 2013 CU	​SharePoint Foundation 2013	​KB2817346	​Download
​15.0.4517.1005	​June 2013 CU	​​SharePoint Server 2013	​KB2817414	​Download
​	​June 2013 CU	​Project Server 2013	​KB2817415	​Download
​	​	​	​	​
​15.0.4535.1000	​August 2013 CU	​​SharePoint Foundation 2013	​KB2817517	​Download
​	​​August 2013 CU	​SharePoint Server 2013	​KB2817616	​Download
​	​​August 2013 CU	​Project Server 2013	​KB2817615	​Download
​	​	​	​	​
​15.0.4551.1001	​October 2013 CU	​SharePoint Foundation 2013	​KB2825674	​Download
​15.0.4551.1005	​October 2013 CU	​SharePoint Server 2013	​KB2825647	​Download
​	​October 2013 CU	​Project Server 2013	​	​
​	​	​	​	​
​15.0.4551.1508	​December 2013 CU	​SharePoint Foundation 2013	​KB2849961	​Download
​15.0.4551.1511	​	​SharePoint Server 2013	​KB2850024	​Download
​15.0.4551.1508	​	​Project Server 2013	​KB2837668	​Download
​	​	​	​	​
​15.0.4569.1000	​Service Pack 1	​SharePoint Foundation 2013	​KB2817439	​Download
​	​	​SharePoint Server 2013	​KB2817429	​Download
​	​	​Project Server 2013	​KB2817434	​Download