Can Excel workbooks with Power Query refresh 'on open' on PBIRS?

[ForcaTaico]

Hi all, 

Is it possible to refresh a Excel workbook with a Power Query 'on open' in PBIRS? 

I have tried and I get a warning:

"

External data Refresh failed

The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh [my query name]

"

If Kerberos or other authentication is used - Is it then possible?

Regards Taico

[ForcaTaico · Accepted solution]

I have kinda solved the problem myself - no I cannot use a 'Power Query' and 'refresh on open' in PBIRS with OOS.

The "new" connection wizard to SQL in Excel 2016 - which was introduced with some patch - uses Power Query and will not 'refresh on open' on PBIRS.

Workaround is to use the "old/non-PQ" connection wizard to SQL. I have reactived the "old" wizard (Settings > Data) and connected to SQL-server and now PBIRS/OOS is able to refresh 'on open'.

[ForcaTaico]

It looks like it should be posible to use 'windows authentication' against a SQL database and refresh 'on open'

https://docs.microsoft.com/en-us/officeonlineserver/data-authentication-for-excel-online-in-office-o...

I guees we have a problem with our Kerberos settings somewhere... I will talk to our IT dep. and report back here if we get it solved...