Forum Discussion
Power Query data source settings in current workbook not available
When I create a power Query with a sql server data connection in the current workbook and then give the workbook to someone else, they are prompted for the credentials. Why is this happening and how do I prevent it?
In case you do not know, I have learned something unfortunate about Power Query.
When you create a data connection to a database, the tool stores the credentials (see Data Sources Settings dialog) in a file called User.zip
This file is stored in the following locations on the user’s machine
(2013) C:\Users\1037591\AppData\Local\Microsoft\Power Query
(2016) %LocalAppData%\Microsoft\Office\16.0\PowerQuery for Excel 2016
The credentials are stored in this file using an encryption key defined by the author and the domain he is on.
This means it will be impossible to author a power query connection to a database and deploy the connection without also telling the other users the credentials to connect.
In case you do not know, I have learned something unfortunate about Power Query.
When you create a data connection to a database, the tool stores the credentials (see Data Sources Settings dialog) in a file called User.zip
This file is stored in the following locations on the user’s machine
(2013) C:\Users\1037591\AppData\Local\Microsoft\Power Query
(2016) %LocalAppData%\Microsoft\Office\16.0\PowerQuery for Excel 2016
The credentials are stored in this file using an encryption key defined by the author and the domain he is on.
This means it will be impossible to author a power query connection to a database and deploy the connection without also telling the other users the credentials to connect.
So how do you create a spread sheet with embedded connections that you can share without having to have them do setup on their PC.
In earlier versions of Excel you could created a spreadsheet with a link to something like a MySql database (using read only credentials).. Then making a change to the query in the connection file would "unlink" the spread sheet from the ODBC file on your PC and embed the connection information in the spread sheet. Then you could give that spread sheet to anyone in the organization and they woyld be linked to the latest data without having to know/provide the userid/password.
