https://stackoverflow.com/questions/49802471/embed-sql-server-credentials-in-excel-2016-o365-to-refresh-data-on-demand
You need to use the legacy "Microsoft Query" (rather than Power Query) to create the data connection. MS have explicitly removed the ability to store username and password in the connection string (in ODC files - ignored) with Power Query data sources.
Data (tab) > Get Data > From Database > From SQL Server Database > I enter the Server, database and my 'exec sp' command and this returns data no problem for me. However, if I hand the file over to another user that doesn't have access to the database the connection fails because Excel is trying to use Windows credentials. So I tried editing the Connection String (Data (tab) > Queries & Connections > right-click query > Properties > Definition (tab) > Connection String) but the fields are grayed out so I can't add the User ID and Password portion of the connection string.
Data (tab) > Get Data > From Other Sources > From OLEDB > Build > Provider (tab) I choose 'SQL Server Native Client 10.0' > Connection (tab) I enter server name, 'Use a specific user name and password' radio checked and I enter the account username/password, select the database, 'Test Connection' returns valid > All (tab) > set 'Integrated Security' to "False" > OK > OK > Excel then prompts for credentials from Database/Windows/Default or Custom:
I wouldn't think I would enter anything here as I already entered credentials when building the query string but I entered anyways as it seems it's required to enter something. I hand the Excel file over to another user and sure enough access is denied because it's defaulting to their current Windows login to access the database.
I added the From Data Connection Wizard (Legacy) to my Ribbon as an alternative method to build out the connection string using the same settings as the previous attempt and I'm prompted to type the password every time I want to refresh:
