Forum Discussion
Get & Transform credentials prompt
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.
Hmm, well, . . . that's sorely depressing, but that answers the question of where the heck the thing is getting the info from if not in the workbook. If that's the final verdict . . . . then it's a "personal" product, YOU can develop something for yourself, but can't be used to develop something and pass it to the ultimate users. That's frustrating . . . .
Has anyone else out there determined whether there is some means of "passing it on"?? Seems like there should be . . . . but sounds like maybe not if the id and domain information is effectively hardcoded into this encrypted file?
In particular, I distribute workbooks connected to MS SQL database and few shared Excel file to couple dozens of people who has R/O access (through AD) to each of above source, and have no such issues at all. It works.
Do you use SharePoint to store these connected files? Are you able to refresh the data through excel services?
