Secure Store Service and Office 365?

[Bjorn Foster]

I'm trying to use stored credentials to report on Azure SQL data in Excel. There are a ton of articles out there but most seem to reference old versions of Excel and on-prem SharePoint. I know Excel Services is no longer a thing, so maybe this isn't possible with the latest online versions? 

In SharePoint I've set up a Secure Store target application with my Azure SQL User ID and Password. In Excel I connect to the database via Power Pivot using the database credentials. But I'm not having luck when I update the connection properties to have it use my SSS Application ID instead of the credentials -- a data refresh gets the Azure credential prompt, so it's not using my stored connection. Apologies if this isn't the right board for this, but any help would be greatly appreciated!

[#2]

Bjorn,

Did you set the credentials in the workbook as well? Go to Data Connections and select the connection,  then Properties, then Definition. At the bottom under Excel Services select Authentication Settings and select the middle one "Use a stored account" and put in the Application ID.

Let us know how it goes.