Home

Secure Store Service and Office 365?

%3CLINGO-SUB%20id%3D%22lingo-sub-242458%22%20slang%3D%22en-US%22%3ESecure%20Store%20Service%20and%20Office%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-242458%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20use%20stored%20credentials%20to%20report%20on%20Azure%20SQL%20data%20in%20Excel.%20There%20are%20a%20ton%20of%20articles%20out%20there%20but%20most%20seem%20to%20reference%20old%20versions%20of%20Excel%20and%20on-prem%20SharePoint.%20I%20know%20Excel%20Services%20is%20no%20longer%20a%20thing%2C%20so%20maybe%20this%20isn't%20possible%20with%20the%20latest%20online%20versions%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20SharePoint%20I've%20set%20up%20a%20Secure%20Store%20target%20application%20with%20my%20Azure%20SQL%20User%20ID%20and%20Password.%20In%20Excel%20I%20connect%20to%20the%20database%20via%20Power%20Pivot%20using%20the%20database%20credentials.%20But%20I'm%20not%20having%20luck%20when%20I%20update%20the%20connection%20properties%20to%20have%20it%20use%20my%20SSS%20Application%20ID%20instead%20of%20the%20credentials%20--%20a%20data%20refresh%20gets%20the%20Azure%20credential%20prompt%2C%20so%20it's%20not%20using%20my%20stored%20connection.%26nbsp%3BApologies%20if%20this%20isn't%20the%20right%20board%20for%20this%2C%20but%20any%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-242458%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364985%22%20slang%3D%22en-US%22%3ERe%3A%20Secure%20Store%20Service%20and%20Office%20365%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364985%22%20slang%3D%22en-US%22%3E%3CP%3EBjorn%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20set%20the%20credentials%20in%20the%20workbook%20as%20well%3F%20Go%20to%20Data%20Connections%20and%20select%20the%20connection%2C%26nbsp%3B%20then%20Properties%2C%20then%20Definition.%20At%20the%20bottom%20under%20Excel%20Services%20select%20Authentication%20Settings%20and%20select%20the%20middle%20one%20%22Use%20a%20stored%20account%22%20and%20put%20in%20the%20Application%20ID.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20us%20know%20how%20it%20goes.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bjorn Foster
New Contributor

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!

1 Reply

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies