Tag Archives: SSAS

The Difference Between Reporting and Analytics is 42

In his novel “The Hitchhiker’s Guide to the Galaxy”, Douglas Adams envisioned a giant supercomputer named “Deep Thought” that was built to solve the answer to the ultimate question of life, the universe and everything. For the 5 people out there that are unfamiliar with the story, I’ll relate the important bits here. Deep Thought was commissioned by a race of pan-dimensional beings and required seven and a half million years to complete its calculations. When it was finally complete, Deep Thought informed the ancestors of the original creators that the answer was 42. The receivers were understandably disappointed with this response, and when they questioned Deep Thought further, the computer postulated that perhaps the problem was that they never really knew what the question was.

Undeterred, the race then commissioned a second computer (which happened to be the Earth) that would calculate the ultimate question. After a couple of 10 million year attempts, the ultimate question was determined to be “What do you get when you multiply six by nine”. Of course, Adams never claimed that the universe made sense.

To my mind, this is an excellent demonstration of the difference between reporting and analytics. The accurate answer (report) provided a result, but not meaning. Further analytics were necessary to determine context.

Like many information technology terms (Big Data, machine learning, CRM) Business Intelligence (BI) is one of those umbrella terms that many people use regularly without fully understanding its meaning. BI is comprised of many tools that help to glean information and insights from raw data. Thus, an ETL package that moves data from one location to another is just as much a BI tool as is a fancy looking infographic. Combine this lack of clarity with the overloading of the term “reporting, and we wind up with some real confusion in this space.

Reporting is the process of using data to highlight things or trends that have already happened. This can be contrasted with monitoring, which does the same for things that are happening now, and predictive analytics, which tries to predict what will happen in the future based on the same data. The difference between reporting and monitoring is only one of data latency, and as such, monitoring is often referred to as real time reporting, which further muddies the water. However, for the purposes of this article, I want to focus on historical reporting.

Reports are typically one of two types, either operational or analytical. Tools that are good at producing one type are typically not so good at producing the other. What’s the difference? Operational reports are designed to provide information that we know we need, and analytical reports are designed to help us discover things that we didn’t know, or to help answer unanticipated questions. Operational reports are typically designed to be printed. They are typically well paginated, pixel perfect, and provide a single view of the data within any given report. Analytical reports are just the opposite. They are designed with visuals as a starting point, but allow for the ability to pivot on or drill down into the data as appropriate to answer ad-hoc questions. Printing is typically a weakness for analytical reports, whereas drilldown is a weakness for operational reports.

Both report types have their place but they both have very different design point. The data that backs an operational report should ideally be relatively flat, as that best reflects the report layout and helps with performance. Conversely, cubes and data models exist simply because a flat data structure does not adequately support analytical reporting. With analytical reporting, a user may at any point decide to view quantitative data (a measure) through the lens of a different facet (dimension). This difference is so great, that we need a different type of engine to support it. OLAP cubes and tabular models are both examples of this.

Another difference is the data that is necessary to support both report types. Operational reports tend to concern themselves with various levels of subtotals per the predefined facets. In a case like that, the data mart that backs the report only needs to store those subtotals. The granularity, or resolution of the data stored in the data mart does not need to exceed that of report that references it. Analytical reporting is different. Since users will be expected to drill down on data, from on dimension to another, or to filter the data according to increasingly granular facets, it is critical to store all of the data in the data mart backing the data model. We don’t know the level of resolution the analyst will need; therefore, all detail is required.

As a simple example of this, consider the case where we want to analyze some server log data over a period of time. We can pre-aggregate the data in the data model such that it stores the total of the log entries of various entries on a daily basis. There would need to be a total based on each dimension, but the overall data storage would be less than for the raw data. Such data would allow an analyst to spot trends over several days, but the decrease in resolution means that it will be impossible to spot any usage trends within a given day. If daily trends will never be necessary, then this doesn’t matter, but the nature of analytical reports means that the designer can never be sure.

The more that the source data for the report is pre-aggregated, the less that report becomes analytical in nature, and the more it approaches operational. This is regardless of the tool used; you can build either report type with any tool, it’s just that it may not be optimal.

The issue here is one of semantics. Semantics however are important in knowing what you are getting if reports are being provided to you. Calling something “Analytics” does not make it so. If you spin up a content pack in Power BI, and find that the underlying data model provides just enough dimensions and measure to construct the provided report, and that you can’t deconstruct the data in any meaningful way, what you have is a report, not analytics, no matter what the platform. As with anything, there is a trade-off between complexity and power. Given the nuances of this topic, it’s important to look under the hood to know what you are getting.

The answer “42” is perfectly acceptable if you already knew that the question was “what is 6×9?”. But if you want to know why, that takes a little more digging. You’d also know that there might be a data problem…

Power BI Analyze in Excel – The beginning of a beautiful thing?

One of the announcements made at the Microsoft Data Summit in this past March 2016 was the availability of Analyze in Excel. This feature allows an Excel workbook to connect to a data model that is stored in the Power BI service, and to use it to analyze the data contained within. With this approach Excel is not importing data, or at least it is not importing any more data than the query results. It is exactly like connecting data to SQL Server Analysis Services data sources, something that Excel users have been doing for years. Well, to be completely accurate, it’s not LIKE connecting to SSAS, it IS connecting to SSAS. The only difference is that in this case SSAS is in the cloud. This feature significantly enhances the utility of the Power BI Service, and is important for several reasons that may not be all that obvious. I’d like to walk through a few of them, but let’s start with the obvious.

Excel is a very powerful analytical tool

As nice, and as attractive as Power BI visuals are, Excel still rules the roost when it comes to doing advanced analytics. Excel has been doing this for years and is very mature. It supports features such as pivot tables, pivot charts, and drill through to data, where Power BI reports still do not. The lack of these features can be a blocker for Power BI on its own, but if the data models in Power BI can be analyzed with Excel, suddenly a move to Power BI is not an either/or decision – you can have it both ways. You can deploy models and reports into Power BI and take advantage of all the goodness there, but you can also connect with Excel when the deep analysis is needed. With Analyze in Excel, you can have it both ways.

A wider audience for your data models

Very often, the person that builds the data model is the same person that does the analysis. This is the nature of self-service analytics. In the past when the only multidimensional analysis tools were OLAP cubes and connected Excel workbooks, cube design was a specialized skill. The cubes were published and users would use them as is. The advent of the data model (Power Pivot) and self service analytics lowered the skills bar so that analysts could acquire data, model it and analyze it, making the entire process much quicker and responsive. However, this still puts the model out of reach for those with no data modeling skills or interest.

Analyze in Excel provides the best of both worlds. Analysts can build models and reports in Power BI, and users that need more in depth analysis can connect to them with Excel without having to reinvent the wheel. This in effect provides the same capability that PowerPivot for SharePoint does on premises. One model can now reach a much wider audience of users. This has many of the benefits of an on-premises SSAS deployments without all of the organizational overhead of getting them up and running.

Uses the Analysis Services OLEDB Driver

The connection that is made from Excel to the Power BI services uses the latest version of the classic Analysis Services OLEDB driver. This is the driver that has always been used to allow Excel (and other tools) to communicate with Analysis Services, and this new version has been updated in order to work with the cloud based SSAS service. In fact, in order to use the feature, you must first download and install the updated driver. Therefore, in theory, any tool that uses this driver should be able to communicate with Power BI models as if Power BI was one great big SQL Server Analysis Services server (because it is).

It really is Analysis Services in the cloud

The Power BI service itself is backed by tabular mode SSAS. Until now, it was necessary to go through the service to access it. Analyze in Excel is the first instance that I know of that a client application communicating directly with that SSAS instance. While this connection is really using the Power BI API, it does beg the question – can a fully Platform as a Service version of Analysis Services be very far away?

Claims based authentication and Power BI API

None of the products in the SQL Server suite currently supports claims authentication. This is true even for the yet unreleased SQL Server 2016. Even SQL Azure, a cloud based version of SQL Server, requires SQL authentication only (although Azure Active Directory authenticated databases are currently in preview). However, looking at the connection string contained in the ODC file used by the Analyze in Excel feature reveals some interesting things. Here’s one connection string:

<odc:ConnectionString>Provider=MSOLAP.7;Integrated Security=ClaimsToken;Identity Provider=AAD;Data Source=https://analysis.windows.net/powerbi/api;;Initial Catalog=xxxxxxx; ……..

The value MSOLAP.7 for the provider indicates that this is the next version of the SSAS OLEDB Driver. No surprises there, but this does hint at future compatibility (see SharePoint below). The value for Integrated Security, and Identity Provider (ClaimsToken and AAD) indicate that it is leveraging Azure Active Directory Claims authentication. We therefore have a version of SSAS that can use Claims based authentication. This isn’t available to on-premises installations, but given that the capability has been built, I imagine that it is not all that far away.

Finally, the Data source indicates that the Power BI API is being used to marshal all communication with the back end API service. I think that it is reasonably to conclude that any API for a PaaS based version of SSAS would be based on, or strongly resemble the Power BI API. They may even be one and the same.

Excel Online in SharePoint

As anyone that has set up PowerPivot for SharePoint can tell you, SharePoint supports the configuration of new OLEDB drivers. This support carries forward into the Office Online Server in the world of SharePoint 2016. Given that both SharePoint and OOS utilize claims based authentication, it should theoretically possible to create a workbook that uses the Analyze in Excel feature, store it in SharePoint, and have it work for multiple users from within a browser. I imagine that more plumbing is needed at this point, but it would be an interesting way of integrating Power BI in the cloud with SharePoint both on premises and Online.

Reusing the Excel Files, and Limitations

In the same vein as discussed with SharePoint, Power BI itself allows Excel files to be interacted with in the service in exactly the same manner that Excel Online does. Theoretically, one should be able to use Analyze in Excel to build a workbook, then connect it to Power BI and have it work for interaction. While it is possible to connect it, all interactions fail at the moment. It appears that the Power BI service (or the backing Office Online service) does not yet support the new OLEDB driver.

Another current limitation of this feature is that data sources using Direct Query (this includes SSAS sources) or sources created by the Power BI API cannot be used with Analyze in Excel. At least not yet.

Analyze in Excel is another useful tool in the Power BI arsenal, but as outlined above, I think that it’s a harbinger of even greater things to come.

Using PowerPivot for SharePoint with SharePoint 2016

While the capabilities previously provided with Excel Services have been moved to Office Online Server (OOS) in the 2016 version of SharePoint, PowerPivot for SharePoint (PP4SP) has not. PP4SP remains a SharePoint service application in the 2016 edition of the product. This service application is responsible for providing the automatic data refresh capability for PowerPivot for SharePoint enabled workbooks. As an aside, it can also refresh connected workbooks, as I discuss here. Given that the rendering engine now exists on a separate server, there are a few additional steps to perform, and this article aims to walk through them.

Basic Installation

Prior to setting up PowerPivot for SharePoint, you’ll need a SharePoint farm that has been enabled for PowerPivot workbooks, as I have previously outlined here. The Add-In is available from Microsoft here.

Installing is a simple matter of downloading the add-in and running setup. You’ll be presented with a straightforward dialog box with 4 options.

This should be installed on every SharePoint server in the farm, whether or not it will run the Service application. Technically the first option is not required for front end web servers, but it is small, and I like to keep my options open. After clicking next, the bits will be installed.

Like SharePoint, once the bits are installed, they must be configured. This is done through the PowerPivot for SharePoint configuration tool, which the earlier setup installed. It should be available from the application list on the server. It works much the same as it did with earlier versions of PP4SP and SharePoint. Run it and you’ll be prompted for the installation type. Select “Configure or repair..” and click OK. Next, you’ll be presented with the configuration detail dialog. The dialog contains a number of configuration nodes, which drive a series of PowerShell scripts that are used to perform the configuration (tip – click on the Script tab to see the scripts in question). The exclamation point icon indicates that parameters need to be supplied.

The first node is mandatory – Configure or Repair.

Here, you enter the credentials of the user that can perform the configuration – I normally use the user that was used to configure SharePoint in the first place – spSetup in my demo environments. This is the only step that is critical. However, I find it to be good practice to change the name of the Service application and database.

The default values begin with “Default Power Pivot…” and the database contains a GUID as part of its name. When searching alphabetically for PowerPivot, I tend to look under P not D, so I remove the word default from both, the GUID from the database, and further change the database name to conform with naming conventions. Finally, it’s a good idea to check the Site Collection that will be activated.

The configuration tool will activate the PowerPivot solution in one site collection by default. It can be activated later in others, but it’s worth starting off on the right foot.

Click the “validate” button, and if all of the indicators are green, go ahead and complete the configuration.  Once configured, no further Central Admin work should be necessary, at least not at this point.

PowerPivot Gallery

A PowerPivot gallery is not required. All of the PowerPivot for SharePoint features can be used in a regular document library, but the gallery centralizes things and makes these features more discoverable. It should be noted that just as with SharePoint 2013, the PowerPivot gallery is a customized document library that uses Silverlight to display its contents. This dependency on Silverlight means that in order to use it, workstations must have Silverlight installed, and neither Google Chrome, nor Microsoft Edge browsers will support it.

To create a new PowerPivot Gallery, navigate to the site contents of the target SharePoint site, and select “Add an app”. Select the PowerPivot gallery and give it a name. If you don’t see PowerPivot Gallery as an option, you may need to enable the PowerPivot Feature for Site collection in the Site Collection features list. Once added, upload a PowerPivot enabled workbook. This workbook should contain a data model where the data was imported directly into PowerPivot (not via Power Query). Once uploaded, after a few moments, the thumbnails from the workbook objects should show up in the gallery. It should be possible to interact with the workbook, as PP4SP is not required for that, but the two (or 3 depending on whether or not SSRS has been installed) icons on the right of the workbook provide access to PP4SP capability.

In order to set up scheduled refresh, click on the calendar icon (The Excel icon is for using Excel as a data source – see below). This opens up the data refresh history for this workbook. To configure it, click on the “Configure Schedule link”. On the configuration screen, select the enable check box, enter the desired schedule, and the credentials needed to connect to the source data. For testing purposes it is more deterministic to explicitly enter credentials here, but refresh supports a “refresh account” (configured via the Secure Store Service), or any Secure Store Service credentials. Also, selecting “Also refresh as soon as possible” will immediately force a refresh cycle, which will begin within 5 minutes of saving, and is useful for testing.

Once complete, open the refresh history for the workbook, and you should see either a stopwatch icon, indication a refresh is in progress, a green check mark, indicating successful completion, or a red x, indicating failure.

One thing should be noted – data models created by using Power Query in Excel will always fail – this is true as of March 2016. Power Query refresh has been stated as a feature for PowerPivot for SharePoint 2016, but as of this writing, it has not yet been included.

Workbook as a Data Source – Kerberos Enablement

The URL of a workbook that contains a data model can be used in a connection string in another workbook, and PowerPivot for SharePoint can intelligently route that connection to the backing SSAS PP Mode server. To the consuming workbook, it looks just like a regular SSAS server.

In prior versions of SharePoint and PP4SP, using a workbook as a data source “just worked”, because the service and the workbook were all on the same server. With OOS, the server is on a different server. OOS needs to connect to the source workbook (the one with the data model) with the credentials of the consuming user, which means that for this to work, Kerberos Constrained Delegation (KCD) must be configured between OOS and SharePoint.

To be sure, you only need to configure KCD if you wish to use Excel files as a data source. If not, this step can be safely skipped.

You need to allow the computer account for the OOS server to delegate credentials to the account running the HTTP service for the SharePoint application that contains the workbooks to be used as data sources. In the example below, the OOS Server is NAUTILUS2016OOS, the service account is NAUTILUS\spApps, and the application is http://home.nautilus.local. This PowerShell can be run on any Domain Controller server.

$allowedPrincipals = @()
$allowedPrincipals += Get-ADComputer -Identity NAUTILUS2016OOS

# Set the delegation property on the application pool identity.
Set-ADUser spApps -PrincipalsAllowedToDelegateToAccount $allowedPrincipals

# Set the Service Principal Names for the application pool identity.
SetSPN -S HTTP/home NAUTILUS\spApps
SetSPN -S HTTP/home.nautilus.local NAUTILUS\spApps

Once successfully configured, it should be possible to use Excel files that contain data models as a data source for other Excel files. To create a new one, click the Excel icon beside the data refresh history icon in the PowerPivot gallery.

Wrapping Up

One other feature requires further configuration to work, and that is the PowerPivot Administration dashboard. Security constraints now prevent the use of Central Administration as a container which means that the dashboard must be set up in a regular site collection. This  requires Server to Server (S2S) trust to be configured. Given that this is not a user facing feature, it’s out of scope for this article, but details on how to do it can be found in the Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016 document.

Setting up PowerPivot for SharePoint will still not give you the ability to render Power View reports in a browser whether they are created standalone, or in an Excel workbook. For that, it is necessary to set up SQL Server Reporting Services (SSRS) in SharePoint mode, as Power View rendering is part of SSRS. That will be the topic of an upcoming article.

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.