Get & Transform credentials prompt

[Eric Larsen]

I have the same issue as the gentleman from Ratheon.  I have built a file that successfully queries a client's Sql database, and now want to give that file to one of their accounting folks to refresh and use on a daily basis, without my having to do it for them.  When I run it on my machine over VPN, after the initial build, . . . it never asks me again to give it id and password.  When I give the same file to them, it is asking them for the information.  I am wanting this file to work automatically for them without it asking them for the information, it uses a system id to get the data.  If I edit the query, and go to data source settings, then edit permissions, it brings up the SQL Server Database Connection information screen, with the userid filled in and the password asterisked out, ie, it KNOWS the information, and in theory, it's stored with the query in the sheet (or not, . . I guess . .. . . cause when I pass it to them, it asks them).  How the heck does this really work, and where is this data stored such that it looks like it knows the info needed when I look at it, but doesn't know it when I send them the file?  Nothing in the online documentation seems to refer to how this works and/or how to do this so that I can send my wonderfully working query to the end user to carry forward with.  Arrrgh maties, I am struggling to find anything that will help solve this issue!

[#2]

It is curious why there is no answer for this. Perhaps it is a defect. However, the tool has been around for 7 years. Not sure why this has not been addressed. The data source settings dialog says my connection is ‘in this workbook’ but it is not, apparently.

[#3]

Daniel,

Yes, I agree, I don't understand why this doesn't work "as it looks".  "The way it looks" seems to indicate that the workbook itself contains all the information that it would need for a different person to just fire it up and go . . . . and yet . . . NOT!  So, . . . there has to be some double secret probation thing goin' on in the background somewhere, or it doesn't really work like it looks like it (should) works or . . . I dunno', but it's frustrating the bejeebers out of me!  This is the only thing left to do . . . I've built and tested all of the functionality that is needed, and have been running and sending the end results to them . .  and now I just want to give them the proven file and let 'em refresh it on their lonesome . . . doesn't seem like it should be this hard.  I love everything else in Get and Tranform, it is a lot simpler than a lot of things I've worked with and doesn't require a lot of coding knowledge (I'm an accountant) but they could be more instructive as to how this part of it works, and how to configure it properly.

I did also notice that there was complete silence on the subject that you initially raised.  Frustrating.

[#4]

I may only share my experience

- Excel file collects data from several sources includes on-premises MS SQL;

- for all data sources privacy level is set on Organizational;

- all users have R/O access to MS SQL through AD ID:s;

- first time when they open the Excel (or first time after the query update) they have the prompt to Run the query to MS SQL or not;

- selecting the Run they have no issues till next query update (other words they need to confirm the query again)

[#5]

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.

[#6]

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?

[#7]

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.

[#8]

Thanks for your research, this is very sad.  For my Project Server use, I can build the same exact connection by hand using the data tab and connection properties.  But it's time consuming.  Much easier to paste my SQL code into the Get and Transform editor.  I'll dig a little more.  Queries should be shareable.  

[#9]

Do you use SharePoint to store these connected files?  Are you able to refresh the data through excel services?