Excel Data Connection Refreshes in SharePoint 2016 & Office Online 2016

  • [アーティクル]

Here’s a quick run-through on how to refresh data connections in Excel files stored in SharePoint, via Office Online, 2016 edition.

Things have moved around in SharePoint 2016; no longer does Excel services exist in SharePoint-land, so refreshing Excel data-sources on the web has changed a bit. This is a quick guide on how to get the same setup in the year of our Lord, 2016 AD.

What’s a Data Connection in Excel?

Something that some customers like to do is connect to SQL Server or other data sources within Excel files. This way you can have your Excel file magically update its data from a central repository, and it’s all very nice.

The challenge arises when we move that same Excel file into SharePoint and except that same data refresh functionality to work the same. In Excel, it’s pretty straight forward; excel.exe is running under your identity so when it connects to SQL Server for example, it already has all the credentials it needs to do a refresh. Here’s the example we’re going to use:

clip_image002

Behold, data from SQL Server via the power of data connections. Refreshes from Excel work without much drama.

The problem with moving this into SharePoint however is that SharePoint isn’t running as “you” but needs to pretend to “be you” if the same data refresh is going to work.

Here’s the architecture in SharePoint & Office Online 2016 for a typical data refresh:

image

The difference here is our claims-to-Windows token service is configured in Office Online, although there’s still the same service in SharePoint too – ignore it for now.

Network Setup & Configuration

Here’s the relevant details for my own setup then.

SQL Server is running under an account “awesomecloud\svc_sql”. It has these registered SPNs:

clip_image006

On the Office Online server, Claims to Windows Token is running as local system:

clip_image008

Running as local system saves a bunch of local security policies that a domain account would need to add (“impersonate a user”, “act as part of the OS”, and more). Just run as local system & save some headaches.

As we are running C2WTS as local system, the Office Online *computer* object is given delegation access to the SQL Server SPNs:

clip_image010

Unconstrained delegation no less. We need this so we can then impersonate $loggedInUser to SQL Server for our refresh.

Next up, the C2WTS needs to allow invocation by Office Online server. Do this by editing the file “C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config”.

Uncomment the line:

<add value="NT AUTHORITY\Network Service" />

The file should look something like this:

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

Save & restart the service. Now refreshes from Excel Online should work!

An Excel Data-Connection Refresh in Action

Assuming everything is working, this should now be possible. Here we open an Excel file, same one as above in fact, in SharePoint/Office Online. Refreshing the connections should not only not give us any errors, it should also now reflect any new data in the table.

clip_image012

…and the result…

clip_image014

This is the end-goal here; data refreshes just happening. If you checked on the SQL Server, as far as it was concerned, the refresh query would’ve executed under my logged-in Windows account equivalent – no claims here I’m afraid.

Stuff to Remember

It’s quite likely though you’ll hit some road-blocks on the way to getting it working. Here’s a few tips & clarifications.

Firstly, the C2WTS call uses Kerberos but authentication to SharePoint with Kerberos isn’t necessary. I did this connecting via NTLM to SharePoint just to prove a point. Double-hop isn’t happening or needed.

clip_image016

Why? Because SharePoint is all claims-based for authentication; Kerberos or NTML or whatever will just end up as a claim to SharePoint; one that’s then passed to Office Online.

That claim is then turned back into a Windows token, coincidentally using Kerberos to do so, but there’s no classic Kerberos double-hop happening here.

Next, Kerberos is actually used however to impersonate the logged in user to the service being connected to – SQL in this instance. C2WTS will request a ticket to do so, and if the right SPNs & delegation isn’t setup, it’ll fail. To make matter worse (and I’ll be honest, I’m not 100% clear why being that Kerberos isn’t my speciality), that response is cached to some extent even if you then fix the initial problem. So in other words, if you’re certain something should work – restart the Office Online server and it might just work next time you try.

When it does work, you’ll see the result if you look at SQL Server profiler:

image

My user is connecting to SQL Server, despite me not being on the Office Online server itself – just logged in to SharePoint which uses Office Online server to do it’s refresh work. Clever!

 

Finally, Office Online server has ULS logs, just like SharePoint. Run “Get-OfficeWebAppsFarm” to find out where they are & use them for if & when this doesn’t work.

 

That’s it for now!

 

Cheers,

// Sam Betts

Comments

  • Anonymous
    April 25, 2017
    Where does SSAS (or OfficeWebAppsExcelBIServer) come into play? When does OOS make the call to the database and when does SSAS do it? If they are on separate machines, do they both need C2WTS and KCD configured?Additionally:"authentication to SharePoint with Kerberos isn’t necessary" - not unless you want Workbooks as Data Source"Running as local system saves a bunch of local security policies" - but also creates a headache for KCD configuration when you have many OOS servers as you have to maintain it for many accounts. I tried with a domain account (local admin, Act as part of operating system, Impersonate a user, Log on as batch job) but no luck - I was getting a "Token cannot be zero" exception until I switched back to local system.Thanks for the post.
    • Anonymous
      April 26, 2017
      Hi Piotr,This post was for purely SQL Server data-sources only; other sources have other setup requirements, but all need to have the above done (or similar at least). I too had issues getting a domain-account working for C2WTS but given all the other pre-reqs needed for each local policy & permissions, I figured configuring computer accounts was at least the same amount of hassle so I left it as local system.I might do a follow-up for PowerPivot if time permits!Cheers,Sam