I have a query that pulls from a SQL Server DB in excel and I'm wondering if there's a way to input the username and password credentials in the connection string? Otherwise, I will have to remotely dial into other computers running this file and enter the username and password manually. If not able to do it through the connection string, is it possible within the PowerQuery editor or possible to pass the credentials through using VBA?
Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query1;Extended Properties=""
From what I've seen online I do not believe it is possible. The username and password to run these queries are stored at a device level and not within the workbook itself; meaning every time someone else tries running it on their device, they will have to enter the credentials manually.
As for Power Query, it seems to be disregarded as it is a security issue to pass credentials through VBA; as said here, every user that wants to run these queries will need to have their own access to the database or data source.
The answer to a similar question I hyperlinked above suggests an alternative solution that may be worth exploring.