Data Connection-> Query -> Set it up with one user id

[Tushar]

Context - Currently I pull data out from SQL Server and send excel to multiple users. I am pulling data using Data-> From Other Sources -> From Microsoft Query.

Issue - When I share the excel with other user and request them to pull data, they get an error message saying access denied.

Expectation - I am creating connection using service id that has access to server. When other user click to pull data, excel should use the service id to pull data and not the user id of user using the excel.

Please let me know how to do it.

 

[Chihiro]

You'll need to uncheck "Trusted Connection". However, this will require user to enter password.

To have credential automatically entered. You'd use VBA to build connection string to SQL server. For security I'd recommend protecting your VBA (though it's not too hard to crack).

See link for an example.
https://msdn.microsoft.com/en-us/library/ms807027.aspx