none
Power Query Execute a query against multiple data sources using Sql authentication and data driven connections RRS feed

  • Question

  • I have an excel worksheet that contains a list of sql servers with login infromation:

    servera, sa, password

    serverb, sa password

    i want to use this as a source to execute a query against each data source I created a function using sql.Data to do this however when i call the function I get prompted to enter credentials, there doesn't seem to be a way to pass credentials into the function in a data driven manner. using windows authentcation is not an option in this scenraio though i suspect it would work if I could use it.

    Here is my function:

    let
        fDoIt = (ServerName) =>

    let
        Source1 = Sql.Database(ServerName, "master", [Query="Select *, @@ServerName from sys.dm_exec_requests"])
    in
        Source1

    is this a supported scenario? I'm using office 2016 so it is is "Get and Transform" but it should work the same in regular power query too.

    I'm also looking to find a place to enter feature requests for "Get and Transform" or power query either

    Tuesday, June 21, 2016 11:04 PM

Answers

All replies

  • This is not a supported scenario. We require credentials to be entered out-of-band from the query.
    Tuesday, June 21, 2016 11:16 PM
  • Thanks much for the quick response i was worried that would be the case.

    is there a way to get this entered as a feature request? this is blocking my scenario. It seems like a straightforward and useful ask.

    Tuesday, June 21, 2016 11:55 PM
  • I'm afraid our security model was specifically designed to prevent this.
    Wednesday, June 22, 2016 1:00 PM