Tag Archives: Excel Services

Power BI Report Server Completes the Vision for On-Premises Reporting

Microsoft today made available the August 2017 preview of Power BI ReportServer 2017. This preview includes the long awaited support of embedded data models, as well as the ability to render Excel reports natively. This is a major step forward, because with this release, Microsoft has completed its vision for its on-premises reporting platform that it first articulated in October of 2015.

Excel content being rendered in Power BI Reporting Server

The big news at the time was that the platform was stated to be SQL Server Reporting Services (SSRS). Not SharePoint, not PerformancePoint, but SSRS. SSRS was a mature product that quite competently provided a platform for operational reports. What it needed was some modernization and the addition of some analytical and self-service reporting capabilities. Several of these capabilities were subsequently included with the release of SSRS 2016.

Gone would be the days of configuring complex SharePoint farms just to be able to work with analytical reports (ie Power View, Excel). New Features were being added to SSRS to make it a complete platform for both analytical and operational reports.

The Vision

The roadmap articulated four different report types, 3 of them analytical (by my definition) and one of them operational. These three types line up with reporting tools in the Microsoft BI stack:

Name Type Primary authoring tool ext
Paginated Operational SSRS Report Builder
SQL Server Data Tools
.RDL
Interactive Analytical Power BI Desktop .PBIX
Mobile Analytical Mobile Report Designer .RDLX
Analytical Analytical Excel .XLSX

Therefore, reading between the lines, in order to be a complete reporting platform, SSRS needed to be able to render all of these report types. Paginated reports were of course always native to SSRS, and the roadmap announced that Mobile reports would be included in SSRS 2016 through the integration of Datazen. The roadmap further committed to SSRS being able to render Power BI files in the future.

SSRS 2016

SSRS shipped with some significant modernization improvements, including a much awaited HTML5 rendering engine, and it included Mobile Reports. Mobile reports are delivered through the Power BI mobile application, and SSRS visuals can be pinned to Power BI dashboards.

Significant plumbing was done to move the platform forward in 2016, but it still only rendered 2 of the 4 report types.

In November 2016, it was further announced that the 2016 version of SSRS running in SharePoint Integrated mode would be the last. Moving forward, Reporting Services will only run in Native Mode. In the same announcement. In the same announcement, as I noted in another post, for the first time, the SSRS team committed to providing Excel report rendering capability as well.

Power BI Reporting Server

We first saw the on premises rendering of Power BI reports in the first community preview of SSRS V.Next in the fall of 2016. Those previews required that the reports be directly connected to SSAS tabular models, but they were ground-breaking just the same. A user could be totally disconnected from the web, and still render Power BI reports.

In May of 2017, Power BI Report Server (PBIRS) was announced. A less confusing name could have potentially been SSRS Premium, because that is in essence what it is. PBIRS is everything that SSRS is, plus the ability to render Power BI reports. SSRS will continue forward as a product without Power BI rendering capabilities. It is just a licensing distinction.

The release today of the August 2017 preview of PBIRS allows for embedded data models, and therefore a much wider breadth of capabilities. These models cannot be automatically refreshed yet, but they will upon release. This is, after all, just a preview. If you need automatic refresh of these data models in the meantime, there is an excellent third party solution to do this: PowerPivot Pro’s Power Update.

The inclusion of Excel report rendering capabilities means that PBIRS is a complete report rendering platform, more complete even that the Power BI cloud service.

Moving Forward

Now that the basic on-premises capability has been provided, SSRS/PBIRS needs to pay attention to paying back the debt that it incurred when SharePoint Integrated mode was deprecated. Chief among these features is the SSRS web part. The lack of a decent web part is a blocker for many organizations to move forward with this strategy. Some migration tools to move from Integrated to Native mode (like this one that migrates in the other direction) would be highly useful as well.

Now with on-premises covering all the bases, it’s easy to spot a glaring hole in the cloud Power BI offering. While it supports all three types of analytical reports, there is currently no way to render operational reports in the cloud. Until this capability is provided, it appears that on-premises will have the most complete solution.

What you need for Business Intelligence in SharePoint 2016

Over the past few weeks, I’ve put together a number of posts that outline the intricacies of setting up SharePoint 2016 with its BI workloads, in particular Excel, PowerPivot, and SQL Server Reporting Services. With the full release today of SharePoint 2016, I wanted to summarize these posts, and to provide some context.

The major change to the BI world is of course the fact that Excel Services is no longer included, its capabilities having been replaced by Office Online Server (OOS). The posts below discuss the implications of this change, as well as how to configure all of the BI features in the new platform.

Article Description
Rethinking Business Intelligence in SharePoint and SQL Server 2016 My take on the changes to on-premises BI in the Microsoft world, and what the implications are for the present and future
Adding Excel Services Capabilities to a SharePoint 2016 Farm How to Set up Office Online Server to support the services previously available in Excel Services
Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016 How to set up SharePoint 2016 and Office Online Server to support Excel workbooks with embedded PowerPivot data models
Using PowerPivot for SharePoint with SharePoint 2016 How to configure the PowerPivot for SharePoint 2016 service application
Configuring SSRS 2016 Integrated Mode with SharePoint 2016 How to configure SQL Server Reporting Services 2016 Integrated mode in SharePoint 2016
Integrating SharePoint 2016 with SSRS Native Mode How to configure SQL Server Reporting Services 2016 Native mode and integrate it with SharePoint 2016

Just a quick glance at the articles above will show a deep dependency on SQL Server 2016. For example, in prior versions of SharePoint, multiple versions of SSRS were supported on SharePoint. This is no longer the case with SharePoint 2016. To be clear, I am talking about the BI components (SSRS, PowerPivot for SharePoint) and not the core database server for SharePoint. SharePoint 2016 requires SQL Server 2016 versions of both PowerPivot for SharePoint and SSRS. This means that if you’re invested in Business Intelligence in SharePoint 2013, you’re going to need to wait for SQL Server 2016 before you upgrade in a production environment.

SQL Server 2016 is currently at the Release Candidate (RC0) stage, and its release won’t be that far off. You can get started today on your test migrations, knowing that the full release will likely be available by the time your testing is complete. The articles above were all written while using the CTP 3.3 version of SQL Server 2016.

Looking through the articles you’ll find a number of configurations, and requirements that line up with specific scenarios. Below is a quick guide to outline what is required to support what feature in the SharePoint 2016 BI space.

Feature Requirements
Excel workbooks connected to SSAS Data Sources Kerberos Constrained Delegation (KCD) between OOS and SSAS data source

OR

EffectiveUserName enabled on OOS Server(s)

OOS Server account(s) added to Admin list on SSAS server(s)

Connected Excel workbooks to Windows Authenticated SQL Server Data Sources KCD between OOS and SQL Server

Claims to Windows Token Service running on OOS Server with Network Service enabled

Connected Excel workbooks using stored credentials (Excel Services Authentication Options) Secure Store Service (SSS) credential created

OOS machine account added to SSS Members list

“AllowHttpSecureStoreConnections = true” set on OOS server if HTTP is used

PowerPivot enabled Excel workbooks SSAS PowerPivot Mode server available

SSAS PP Mode server added to BI server list on OOS Server via New-OfficeWebAppsExcelBIServer cmdlet

OOS Server account added to Administrators list of SSAS PowerPivot Mode Server

Automatic Refresh of PP enabled workbooks PowerPivot for SharePoint

Silverlight (client side)

PowerPivot Gallery PowerPivot for SharePoint

Silverlight (client side)

Excel files as a data source PowerPivot for SharePoint

PP4SP must have admin access on SSAS PP mode Server

KCD between OOS and SharePoint application

Claims to Windows Token Service running on OOS Server with Network Service enabled

External ODC file support
PowerPivot Management Dashboard
S2S Trust Configured between OOS and SharePoint
Power View reports SSRS Integrated mode

Silverlight (client side)

Power View in Excel
Power View with Excel as a data source
SSRS Services account must be added to the Admin group on the BI server

Silverlight (client side)

I’ll update this post if anything significant changes between now and the release of SQL Server 2016, but this should help those interested get up to speed today on Business Intelligence in SharePoint 2016.

Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016

SharePoint has supported PowerPivot enabled workbooks since they were first introduced with the initial PowerPivot for Excel Add-On. This support was initially provided via PowerPivot for SharePoint, which was comprised of two major components. The first component was a special, and mostly hidden instance of SQL Server Analysis Services (SSAS) running in what came to be known as SharePoint mode. The other component was a SharePoint shared service application that provided for the scheduled refresh of workbooks and an administrative dashboard.

Initially, these two components came as a single product, PowerPivot for SharePoint, available on the SQL Server install media. The PowerPivot for SharePoint that shipped with SQL Server 2008 R2, and with 2012 prior to SP1 would install both components on to a SharePoint server. Beginning with SharePoint 2013 and SQL Server 2012 SP1 it became possible to separate the two components, allowing for greater scalability. An entry for each SSAS server running in SharePoint mode simply needed to be added to the Excel Services service application (the “BI Servers”), and they would automatically be used for any embedded PowerPivot models.

 With the deprecation of Excel Services in SharePoint 2016, this support moves to Office Online Server 2016 (OOS), and with it, some further decoupling. It is now possible to support the data model with having first installed the PowerPivot for SharePoint service application. Technically, this was possible in SharePoint 2013, but it was far from obvious as to how to make it happen. All that is necessary for PowerPivot workbook support is an Analysis Services server running in what is now called “PowerPivot Mode”.

PowerPivot Mode is that very same specialized version of Analysis Services that exists to support data models that was previously referred to as “SharePoint Mode” in earlier versions. The option to install it is now front and center along with “Tabular Mode” and “Multidimensional and Data Mining mode”. To install a PowerPivot mode Analysis Services server, simply install Analysis Services as normal, and choose “PowerPivot Mode” when you get to the options screen.


The other important thing to note in this dialog box is the inclusion of the computer account for the OOS server(s) as (an) administrator(s). This is necessary because the OOS machines will need to create and tear down data models from Excel workbooks on demand, and the OOS process on these machines run as the Network Service account. Note that this is NOT necessary because of EffectiveUserName as with a regular Tabular Mode SSAS server. EffectiveUserName is not used in PowerPivot mode. It the server gets installed without this account added as administrator, it can be added later via SSMS 2016 in the same manner described in my earlier article regarding Tabular mode SSAS.

Once the PowerPivot mode SSAS server is up and running, the OOS server needs to be configured to use it (If you don’t yet have an operational OOS environment, please see this earlier article to get up and running). As with all other OOS configuration options, this is done through PowerShell. The command to add a PowerPivot mode server is:

New-OfficeWebAppsExcelBIServer –ServerId NAUTILUSSQLBI\SHAREPOINT

In the example above, the name of the PowerPivot mode server is NAUTILUSSQLBI and the instance name is SHAREPOINT. I have found it helpful to use named instances for PowerPivot Mode servers.

Additional servers can be added to the farm in a load balanced configuration by simply rerunning the above command with any additional server ids. This is similar to the way it was done in Excel Services, by adding them to the BI Servers list.

Once this has been put into place, it should be possible to interact with any Excel workbooks that contain a data model. All slicers and pivots should work. This will not, however allow you to schedule data refreshes, or use any other PowerPivot for SharePoint features. For that, you’ll need to add PowerPivot for SharePoint to your SharePoint farm, and that is the topic of an upcoming article.

Connecting Excel to Data with SharePoint 2016 and Office Online Server 2016

If you’re in the SharePoint community, and you’ve been living under a rock since August 2015, you may not have heard that Excel Services is no longer present in SharePoint 2016. This is not a as big a deal as it initially sounds, because all (OK.. most) of the features available to Excel Services have been moved over to Office Online server, streamlining the architecture. It does however lead to many changes, and this is certainly true for external data access and authentication. This article hopes to answer the question of how these changes impact data access and the Excel Services Authentication Settings.

My previous article “Adding Excel Services Capabilities to a SharePoint 2016 Farm” dealt with getting the farm enabled with Office Online Server and Excel Services capabilities. This article drills down into using Excel workbooks connected to back end data sources. It does not cover PowerPivot enabled workbooks, or workbooks with an embedded data model – that is the topic of the next article in this series.

The Excel Services authentication settings are use when an Excel workbook is opened in a browser, and the workbook connects directly to data on another server. Note that it does NOT pertain to PowerPivot enabled workbooks (at least that portion of those workbooks that uses the data model) as they utilize a different mechanism entirely.

The settings are accessed from the data tab in Excel. First, click on “Connections” to view the available connections in the workbook. Next select the connection and click “Properties”. Select the “Definition” tab, and at the bottom of the dialog box, click on “Authentication Settings”. It’s a well hidden option.

image_thumb3

Before continuing, it should be mentioned that these settings affect Windows credentials only. If the data source uses SQL Authentication, or something proprietary, the credentials will be stored in the Secure Store Service, or embedded in the connection string, and they will be used as proxy accounts – fine grained permissions on the destination server will not be possible.

The issue with Windows credentials is the standard “double hop” problem encountered in Windows authentication. This problem existed in prior versions of Excel Services, with SharePoint being the middle tier server. Office Online Server (OOS) must now reside on a separate server, and this add an extra “hop”, exacerbating the problem. In a nutshell, the problem is how to allow the server (in this case, OOS) to provide an identity to the source server. These settings allow for a number of solutions to this problem.

The two major data sources that support Windows authentication are SQL Server, and SQL Server Analysis Services (SSAS), and they are unique enough to be considered separately. Any other data source that supports Windows authentication can be treated in the same manner as SQL Server.

1. Use the authenticated user’s account

This is the option that you’ll choose if you want the identity of the person opening the workbook to be passed back to the source server when the data is refreshed, or in the case off SSAS, interacted with. It’s also the default option. If you never knew about the Excel Services Option Authentication Options button, this would be the one that you’d use.

Before you go rushing to declare that this is the best option, keep in mind that it means that the user permissions will need to be maintained on the data source for all users accessing it. Sometimes proxy credentials are a better choice. In order to get either option working however, additional configuration will be required.

SQL Server Analysis Services Data Sources

If your data source is SSAS you have two options. You can set up Kerberos Constrained Delegation (KCD) between the OOS Server and the SSAS Server, or you can use EffectiveUserName. Given that Kerberos will be discussed in the SQL Server section, and that my recommendation is to use EffectiveUserName whenever possible, we will cover EffectiveUserName here.

To begin with, EffectiveUserName is turned off by default in OOS. In Excel Services this could be turned on through the user interface in the Excel Services Service Application, but with OOS, this is done through PowerShell. As with the other OOS parameters, it’s a single line on the OOS Server:

Set-OfficeWebAppsFarm -ExcelUseEffectiveUserName:$true

With EffectiveUserName,  a connection is made between the OOS Server and the SSAS server using the machine account of the OOS Server itself. The OOS server has the identity, but not the password or token of the requesting user, and that is provided to the SSAS Server to use when executing queries. This impersonation process requires a high level of trust, and the machine account of the OOS server must be an administrator in the destination SSAS instance.

Adding this account requires the use of SQL Server Management Studio 2016 (SSMS), in prior versions it was not possible to add machine accounts to SSAS. To add it, connect SSMS to the destination SSAS instance, then right click on the server node and select “Properties”. Once the dialog options, select the “Security” node, and click the “Add” button.

image_thumb18

Next (and order is important here), select the “Locations” button, and choose “Entire Directory” (or a container where your server accounts live). Click “Ok” and and then click the “Object Types” button. Computer accounts are excluded by default, and this is where you select them.

image_thumb16

Once selected, you can enter the machine account in the form of DOMAIN\MachineName. After clicking OK, it should appear in the administrators list with a “$” appended to it. In our case, the machine name is NAUTILUS2016OOS and the domain is NAUTILUS. THis needs to be done for each OOS server in your farm.

image_thumb17

At this point, any user interacting with the workbook in a browser will interact with the SSAS server using their own identity, which of course means that the SSAS server needs to know who they are. The end users will need to be granted access to the data sources in SSAS, in the same way they would if they were connecting to it in an Excel client.

SQL Server Data Sources

SQL Server has an equivalent feature to EffectiveUserName, the SetUser command. This feature has been supported for years in SQL Server Reporting Services, but unfortunately was never supported by Excel Services. Unfortunately, it’s still not supported by OOS. SharePoint and OOS both support claims based identities, but SQL server does not and this leaves us with only Kerberos Constrained Delegation to provide the user’s identity to the source system.

In order for this to work, Kerberos must be configured for the OOS Server(s) to trust the account running SQL Server on the destination server to delegate credentials. However, KCD delegates Windows credentials, and as mentioned above, both SharePoint 2016 and OOS use claims based authentication. It is necessary to first retrieve the user’s Windows token in order to delegate it, and this needs to happen on the OOS server, as it is now the final “hop” before connecting to the source system.

In the past, SharePoint provided this capability through the Claims to Windows Token Service (C2WTS). The was perfect for Excel Services, as it ran on top of SharePoint and could leverage this service. With OOS that is no longer an option because it runs on a separate machine, but C2WTS can be made available here as a part of the Windows Identity Foundation (WIF).

WIF is not a prerequisite for OOS, so it must be added first. In order to do so,  launch the Add Roles and Features wizard on each OOS server, proceed to the features section, and select “Windows Identity Foundation”. Allow the wizard to complete.

image_thumb20

(Note – if you will be supporting ADFS, you will also need to install the Microsoft Identity Extensions.)

Next, the C2WTS needs to be configured to allow access for the Network Service account. To do so, edit the file “C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config” and remove the comments in the <allowedCallers> section for Network Service. When complete, it should appear as below:

<allowedCallers>
<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>

Once edited, run the following Powershell to start the service and set it to automatic start:

Set-Service -Name C2WTS -startuptype “automatic”
Start-Service -Name C2WTS

At this point, we are ready to start delegating credentials.

The following Kerberos settings need to be performed on a directory server, or a server that can run the SetSPN command and the Active Directory Users And Computers tool.

The first step is to add the SQL Server service and its associated account as a Service Principal Name (SPN). We need to do this for both the machine name of the server and its Fully Qualified Domain Name(s). This can be done from either PowerShell or a command prompt.

SetSPN –S MSSQLSvc/NAUTILUS2016SQL.nautilus.local :1433 NAUTILUS\SQLService
SetSPN –S MSSQLSvc/NAUTILUS2016SQL :1433 NAUTILUS\SQLService

In this example, NAUTILUS2016SQL is the name of the SQL server, nautilus.local is its domain, and NAUTILUS\SQLService is the account that SQL service is running as.

Once the SPNs have been added,  open the Active Directory Users and Computers tool. Navigate to the Computers node, select the OOS Server(s), right click and select properties. Click the delegation tab, Select “Trust this computer for delegation to specified servers only” (this is the constrained part…), and “Use any authentication protocol”. Next, click the “Add” button, and then “Users and computers”. Find the SQL service account (in our case, NAUTILUS\SQLService), and save it. You should see its corresponding service appear in the dialog box.

image_thumb1

Click OK, save everything and that’s it. KCD should be set up to allow delegation from OOS to SQL server, and any direct connected Excel workbooks should work.

This process will need to be repeated for any data source that supports Windows Authentication.

2. Use a Stored Account

A stored account is a proxy account, which is to say that all users using the workbook in a browser will connect to the data using the same credential – the one that is stored. This option uses the Secure Store Service in SharePoint, which is therefore a requirement. Usage is relatively straightforward. To set up a new Stored Account, navigate to the Manage Service Applications section in Central Admin on the SharePoint farm, and select the Secure Store Service.

Once in the service, Select the “New” button in the ribbon to create a new “Target Application” (Stored Account). Enter an ID, Display Name, and contact email (it’s required for some reason). Then, ensure that the Target Application Type is “Group”.

image_thumb[1]

Once complete, click the “Next” button.

The next screen prompts for the type of credentials, and the default is for Windows credentials, which is what we’re using. Click the “Next” button.

The administrators are those users that can modify the target application (stored account) and set the credentials for it. Add administrators as appropriate. The members are those users that can use the stored account, so this should be set to anyone that can access the workbook.

Now, in addition to any regular users, there is one or more accountsthat MUST be in the members list –  the computer accounts for all of the OOS Servers. This makes sense as the Office Online service itself runs as the Network Account, and it needs to consume the credentials from the SSS – it needs permissions to do so. The account should be entered in the form DOMAIN\MachineName$ – so in our example it’s NAUTILUS\Nautilus2016OOS$.

image

Finally, click OK to save it. The last step is to set the credentials. Select the target application, and click on the “Set Credentials” button in the ribbon. Enter the account that will be used as the proxy here. This will be the stored account that gets used to access the source data, so it obviously should have access to that data.

image_thumb[5]

If your network is http based, there is one further configuration task to perform on the OOS server. By default, OOS requires https to access the secure store service. The option for this is in a PowerShell parameter, and you can change it with the following PowerShell script:

Set-OfficeWebAppsFarm -AllowHttpSecureStoreConnections:$true

Now that this is complete, go to the Excel Services Authentication dialog in an Excel workbook, select “Use a Stored Account” and enter the name of the Secure Store Service Target application that was created (in the example above, this is SSSExcelTest). Save the workbook to SharePoint, open it in a browser and refresh data. All should work.

This procedure is the same for both Analysis Services and for SQL Server. This approach does not require Kerberos, EffectiveUserName, the C2WTS, or S2S trust for it to work. It is likely the easiest option to get up and running quickly.

As with any proxy account, these accounts should only have enough access to  the source data to serve the worksheet(s) they are used for. These should always be least privilege accounts.

3. None

This option was always poorly named. None does not mean no credentials, or anonymous, it means default, or the Unattended Access Account. Both Excel Services and PerformancePoint had the concept of an Unattended Access Account. This account was really just a default entry in the Secure Store Service. It’s useful when the entire organization has access to a certain body of data. Giving the Unattended Access Account access to that data means that nobody needs to configure anything – it should “just work”.

One of the casualties of the move from Excel Services to OOS was the Unattended Access Account. There is no configuration area or PowerShell where it can be set. It would seem that the “None” option cannot be used at all. However, it can, in a very limited way.

Selecting this option will cause the OOS machine account(s) to be used when attempting to access the source data. Therefore, if we grant access in the source data to the machine account, we are treating it like an Unattended Access Account. If this is acceptable from a security standpoint, then it can be used in this manner.

We have already seen how to do this for SSAS, but there is no option in SSMS to add a machine account as a security user in SQL Server, even with SSMS 2016. It is however possible. The way to do this is to use SSMS to open a new query, and run the following T-SQL command:

create login [NAUTILUS\NAUTILUS2016OOS$] from windows

where NAUTILUS\NAUTILUS2016OOS$ is the machine account. The square brackets are required. This will add the machine account as a user in the security section in SQL Server Management Studio.  Once that is done, the account can be added to individual databases in the same manner as any SQL account by navigating to the database Security\Users folder and adding a new user.  From here, select “SQL User with login” as the User type, click the ellipsis beside Login name, enter the machine account and select “Check Names”. Once added click “OK”, and “OK”, and then that account can be used just like any other account.

SSAS cannot use the “None” parameter in this way due to the way that the EffectiveUserName option works. If EffectiveUserName is enabled in OOS, it will simply use the credentials of the logged in user and be indistinguishable from the “Use the authenticated user’s account” option. If it is not enabled, it will try to connect to SSAS as anonymous and fail. The alternative to failing would be to open up access to all data on the server – failure is the best option here.  The bottom line is that the “None” should never be used with SSAS.

Summary

Below is a Summary of the various authentication options that can be used with Excel and OOS, and the requirements to make them work.

Use authenticated user’s account with EffectiveUserName (SSAS Only)

  • ExcelUseEffectiveUserName = true on OOS Server
  • OOS Computer account added to SSAS Server Administrator list

Use authenticated user’s account with Kerberos (SSAS or SQL Server)

  • CTWTS running on OOS Server
  • C2WTS configured to allow Network Service on OOS
  • KCD delegation from OOS Server to Source Server/account

Use a stored account (SSAS or SQL Server)

  • SSS Set up on SharePoint Farm
  • SSS Target account(s) created
  • OOS Computer account added to Members for Target account(s)
  • Target account configured for Least Privilege access to data

None

  • OOS Server account configured with Least privilege access to data (SQL Server Only)
  • ExcelUseEffectiveUserName = true on OOS Server (SSAS)

Acknowledgements

I wanted to acknowledge the help and guidance I received while putting this together and testing. Both Kay Unkroth and Jason Haak at Microsoft were quick to answer some of the thornier configuration questions, and fellow MVP Trevor Seward was instrumental in getting me over the Kerberos hump. Thanks guys.

Adding Excel Services Capabilities to a SharePoint 2016 Farm

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:

https://technet.microsoft.com/en-ca/library/mt170644%28v=exchg.160%29.aspx?f=255&MSPPError=-2147217396

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.

Set-OfficeWebAppsFarm -ExcelWorkbookSizeMax 250

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.

Set-OfficeWebAppsFarm -ExcelWarnOnDataRefresh:$false

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.

Set-OfficeWebAppsFarm -ExcelUseEffectiveUserName:$true

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:

  1. Open SQL Server Management Studio 2016 (SSMS).
  2. Connect to the SSAS server.
  3. Right-click on the server node and select properties.
  4. In the resulting dialog box, select Security, and then press the add button (see figure 2).
  5. 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.

Wrapping Up

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.