Excel Services has been deprecated in SharePoint 2016, but the important features that it provided have been moved to Office Online Server 2016. This article describes the necessary steps.
It’s been fairly well documented that Excel Services will not be a part of SharePoint 2016, and that the bulk of its capabilities are being replaced by Office Online Server (OOS), which is the new name for the Office Web Apps server. The Excel Services features are not all available by default, and certainly not through the standard setup procedures of old. This post will walk through the process of restoring the bulk of the Excel Services capabilities to a SharePoint 2016 farm. SharePoint 2016 is currently in beta, and is scheduled to ship in Q2 2016, but the impact of these changes can be seen and tested now.
This is the first in a series of “how-to” articles that will outline how to get all of the SharePoint-based BI components up and running. The white paper, Deploying SQL Server 2016 PowerPivot and PowerView in SharePoint 2016, published by Microsoft in December 2015, goes into great detail on these topics, and my articles are meant to be a sort of “quick start” guide. In addition, these articles are based on pre-release versions of SharePoint 2016, OOS, and SQL Server 2016, so some of these steps may change, or will become unnecessary by final release time.
Configure the Office Online Server Farm
Starting with SharePoint 2013, it became necessary to run the Office Web Apps server independently from SharePoint. In fact, multiple Office Web Apps servers could be joined together to form a farm. This is also the case with OOS. Given that OOS is the only way for SharePoint to render Excel content in a browser, our first step is to set up a (single server) OOS farm.
The procedure for setting up OOS is fairly straightforward, and is outlined in the following TechNet article, so I won’t repeat it here:
This article correctly lays out all of the prerequisites, and how to install them, but it’s not as complete as the Office Web Apps version of the same article for SharePoint 2013. In particular, it doesn’t discuss how to create a farm that uses http instead of https, which is important, and simpler for testing purposes. Luckily, you can use the same procedure for OOS 2016 as was used with Office Web Apps 2013, using the –AllowHTTP switch in the PowerShell creation commandlet.
Once completed, you can navigate to ServerAddress/hosting/discovery to determine if the procedure worked. In my case, the address was http://oos2016test/hosting/discovery.
Configure the SharePoint Farm
Once the OOS farm is in place, the SharePoint farm needs to be configured in order to use it. The procedure is well outlined in the aforementioned white paper, and is also identical to the procedure used for SharePoint 2013, so it is not worth repeating those steps here. Once connected, and an IISReset has been performed on the SharePoint front end server(s), all Office files should render successfully in a browser. The ability to work with Office files in a browser should now be the same as it was in SharePoint 2013 through Office Web Apps server. At this point, it will be necessary to enable or configure features previously available through Excel Services, and some additional setup is required.
The Excel web part in SharePoint is the most visible Excel Services feature, and is one of the most common ways that people interact with Excel Services in SharePoint. While Excel Services is gone from SharePoint 2016, the web part remains, and it will work with OOS, but using it does involve some configuration. It uses the SOAP-based Excel Services web services, and the new OOS server also exposes these web services. You’ll need to add the web services capability if you want to use the web part, or if you have any solutions that depend on Excel Services web services.
Again, you’ll need to use PowerShell to do the configuration, only this time, on a SharePoint server. In the following PowerShell script, you should replace OOSServer with the Fully Qualified Domain Name of the OOS farm or server.
$Farm = Get-SPFarm $Farm.Properties.Add("WopiLegacySoapSupport", "http://OOSServer/x/_vti_bin/ExcelServiceInternal.asmx"); $Farm.Update()
within earlier setups of Excel Services, I advised my customers to immediately go to the Excel Services configuration in Central Admin and change some of the defaults because they weren’t terribly practical. Microsoft carried some of these defaults over to OOS, but there is no longer a user interface to configure them – so again, you need to do all of this with PowerShell. You can find all of the configurable settings by opening up a PowerShell prompt on the OOS server, and entering “Get-OfficeWebAppsFarm”. Figure 1 shows all of the Excel configuration parameters begin with “Excel”.
Figure 1: Excel Online configuration options
I think a few of these parameters are impractical, so I recommend changing them. The PowerShell to do this follows the same pattern in every case:
Set-OfficeWebAppsFarm –ParameterName ParameterValue
Here is a table with some of my recommended changes to OOS:
Increase the Maximum Workbook Size
|By default, the maximum size of a workbook that can be opened in a browser is 10 MB. It’s pretty common to find larger workbooks, so I recommend increasing this limit. This increase will impact performance so keep that in mind, but by default, I like to set it to 250 MB.
Turn Off Warning on Data Refresh
|As with Excel Services, by default OOS will warn the user through a dialog box whenever it accesses external data. This gets to be particularly excessive because OOS considers PowerPivot content to be an external data source, and there is no way for users to say “stop warning me”. I recommend turning this option off.
If You Aren’t Using Kerberos, Turn On EffectiveUserName
|When the EffectiveUserName option is used, the server is able to pass the user’s identity to an Analysis Services server, allowing all queries to run under the permission of that user. It’s a way to delegate permissions without using Kerberos. If you’re not using Kerberos, you’ll likely want to turn this on.
Getting EffectiveUserName working properly requires another step. The EffectiveUserName feature requires the connecting service
(the one doing the impersonation) to have server admin-level access. In prior versions of SharePoint, this would be the service account that the Excel Services application used. With the move to OOS, there is no Excel Services, and the Excel Online service uses the Network Service account. The solution to this is to add the OOS server(s) to SSAS as administrators. To do this:
- Open SQL Server Management Studio 2016 (SSMS).
- Connect to the SSAS server.
- Right-click on the server node and select properties.
- In the resulting dialog box, select Security, and then press the add button (see figure 2).
- Make sure that “Entire Directory” is selected in “From this location” – if it is not, select the Locations button and select it (see figure 2).
Figure 2: Selecting SSAS Administrators from Active Directory
Next, we need to add the name of the OOS server(s) to which we want to grant admin access. However, if we just enter the name in the object name box, it will fail, because the computer objects are not scanned by default. We must first select the “Object Types” button (figure 3) and select Computers and then click OK.
Figure 3: Adding Computer Accounts to the SSAS Administrator List
Now we can add our OOS server names to the administrators list, and the EffectiveUserName feature will work. Note that you must use SSMS 2016 to do this – prior versions do not allow computer accounts to be used.
Enabling Data Connections
The EffectiveUserName feature described above allows OOS to impersonate users to an SSAS server, but this isn’t available for other data sources. In past versions of SharePoint, Windows authentication could be provided through unattended access accounts, Secure Storage Service accounts, or Kerberos. The first two allow for proxy accounts, while Kerberos provides full identity delegation. With the move from Excel Services to OOS, the proxy account options are both gone, and we are left with Kerberos alone for Windows authentication. Therefore, in order to connect to SQL server data sources from a web-enabled workbook, it is necessary to either enable Kerberos Constrained Delegation (KCD), or switch connections to use SQL Server authentication. KCD is also required in order to use a SharePoint workbook as a data source in OOS (see below).
If you plan to use KCD, there is an additional step required. SharePoint uses Claims authentication, but Kerberos implies Windows authentication. In prior versions of SharePoint, the Claims to Windows
Token Service (C2WTS) on the Excel Services server(s) would be used to translate the Claims based identity into a Windows token. Now that the Excel engine lives in OOS, it’s necessary to configure the C2WTS on the OOS server as this is not done by default. The C2WTS is a part of the Windows Identity Foundation, and if it has not been installed, it can be added as a feature from the Add Roles and Features wizard on the server (figure 4). Also, make sure you install Microsoft Identity Extensions if you require ADFS support.
Figure 4: Installing Windows Identity Foundation in order to add C2WTS to the OOS server.
Once installed, you must configure C2WTS
to allow the Network Service account to use it, and you must configure C2WTS to start automatically. To do so, edit the C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config file, and remove the comment tags (<!– and –>) from the NT AUTHORITY\Network Service line. Once you have done so and saved the file, run the following from a PowerShell prompt:
Set-Service -Name C2WTS -startuptype "automatic"
Start-Service -Name C2WTS
Configuring KCD itself is outside the scope of this article, but once enabled, it should be possible to delegate user identities from the OOS server back to SQL Server.
If you decide to not use Excel embedded connections and instead use data connections (ODC files) from a data connection library, you will need to configure a server to server trust relationship between the SharePoint WFE server(s) and the OOS server(s). You must also establish this relationship if you want to use the PowerPivot for SharePoint IT Management Dashboard.
You can find the procedure for establishing S2S trust in this TechNet article. Note that you also need to set up a User Profile service in the SharePoint farm before you perform this procedure.
The move to Office Online Server brings a consolidation of technology and a few new features, but it’s significantly more involved to set up. The good news is that you don’t have to enable anything that you don’t need. If you are an established SharePoint shop that currently leverages Excel Services to any degree, you will want to carefully plan and test any new environment before making the leap. Here there be dragons.
If your customers use PowerPivot enabled Excel workbooks, setting up OOS will allow them to open these workbooks in a browser, but not interact with them. For that, you need to introduce an Analysis Services PowerPivot mode server into the environment. I will discuss that topic in my next article.
Hello thank you for the explanation I’m upgrading from sharepoint 2013 and some of my site use the ExcelWebRender, I have an OOS server that is actually working well but the preview of excel file is not working on search page and ExcelWebRenderer webpart are not working as well do you have any info about that ?
John, thanks for article
When i try to run New-SPWOPIBinding -ServerName -AllowHTTP
I get error message:
PS C:Userssp_farm> New-SPWOPIBinding -OOS -AllowHTTP
At line:1 char:24
+ New-SPWOPIBinding -OOS -AllowHTTP
The ‘<' operator is reserved for future use.
+ CategoryInfo : ParserError: (:) , ParentContainsErrorRecordEx
+ FullyQualifiedErrorId : RedirectionNotSupported
as i understand, i should replace , but for what?
domain – leo, sps2016 – ps2016, oos – oos
Hey Oleksiy – if I’m reading this right, the command should be :
New-SPWOPIBinding -ServerName OOS -AllowHTTP
Great article and series, as always! 🙂
I believe we followed every step of the configuration correctly, but are however still unable to successfully refresh workbooks with embedded data connections that are configured to use authenticated user’s account. In other words – we’re having some issues in configuring Kerberos constrained delegation on Office Online Server 2016.
The interesting part is the warning that is logged in the event log while trying to refresh:
Credential delegation failed because Excel Services Application was unable to obtain a Windows Identity.
I’ve changed it but the correct username is logged in the original warning, which is odd since it says that it’s unable to obtain it.
Any help would be highly appreciated! 🙂
I had the same issue but instead of using the FQDN I just used the server name w/o the domain or the .com. It worked after I tried every variation known to man.
New-SPWOPIBinding -ServerName -AllowHTTP
Where can I acquire the SpreadSheets and databases that this document references? We are trying to build a POC and would like to use this test material.
We have some Excel reports based on an SSAS tabular model data. We managed to get as far as Excel reports being displayed on SharePoint 2016. The only problem is when we try to refresh. We get an error saying: An error occurred while accessing application id ExcelDataAccess from Secure Store Service.
A few pointers:
1. Excels are configured to use the ‘ExcelDataAccess’ secure store.
2. SQL profile does not show any signs of a connection being attempted on SSAS.
3. I have created a Secure Store application add with the name ExcelDataAccess. The owners are all users, and the credentials used is for an account that is both admin on SSAS and also has a FULL control role in the target database. This is also the account used within Excel desktop and it works without any issues.
Any ideas? Thanks in advance!
Just a quick follow-up regarding my previous post, in case someone else runs into this issue.
Microsoft has officially stated that Excel Online Cross Forest KCD Data Refresh is Not Supported:
Hey, on occasion I see a 500 website error when I browse your webpage. I thought you may wish to know, regards
Hey, Nice Article. Excel in Browser shows Blank Page in SharePoint 2016 after adding Excel Services Capabilities to a SharePoint 2016. but it opens fine in Excel Application. tried multiple browsers. Word and PowerPoint files are opening fine. Any suggestions.
Hi, any idea why I would be getting an error “A schedule cannot be enabled for a workbook with no external data sources” when trying to schedule a data refresh? I have SQL 2016 and SharePoint 2016 on the same server and of course an OOS server. The reports show in excel in the browser, just can’t refresh them.
Hi, Thanks for the explanation. I did exactly what you wrote and get-officewebappsfarm also show a ExcelWorkbookSizeMax of 150mb, but when I open an xlsx with about 120 mb it still fails with an error as follows:
“Sorry, we can’t open your workbook in Excel Online because it exceeds the 30 MB file size limit.
You’ll need to open this in Excel.”
I did a restart of both, Sharepoint and Office online Server. Sharepoint is 2016.