cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ForcaTaico Member
Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ForcaTaico Member
Member

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

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

 

 

2 REPLIES 2
ForcaTaico Member
Member

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

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

 

 

Highlighted
ForcaTaico Member
Member

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

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