Pass Credentials to a function to query datasources

[Anonymous]

Hi everyone, 

I'm facing a problem with a Power Query custom function I've written in Power Query to execute the same query on different databases that are configured using a separate Excel File. 

The function I use is the following : 

Basically it iterates through an Excel File to get Server and DB and execute a Query.

//First we declare our function GetData
let 
 GetData=(index as number)=>
//Then we will load again the config Excel File inside the function.
//This part may be optimized to not reload each time the data source
let
Datasource= 
    let
    Source = Excel.Workbook(File.Contents(ExcelFile), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
        in
    #"Promoted Headers",
//Get the Data Source of the row n°Index
        SERVER=Datasource{index}[SERVER],
        DB=Datasource{index}[DB],
//Run our Query to the designated data source
        Source = Sql.Database(SERVER, DB,
        [Query=FunctionQuery])
in
    Source
in
    GetData

When publishing this report with the aggregated queries on Power BI Report Server, the Scheduled Refresh fails  with the following error message : 

Data refresh failed. Please try again later or contact your administrator.

[0] -1055784934: Credentials are required to connect to the SQL source. (Source at XXXXXX,XXXXX.). The exception was raised by the IDataReader interface

Is there any way to pass credentials in Power BI Report Server that are used to identify to the Excel (only Source in Power BI) to the subsequent data sources? 

Thank you for your help ! 

Romain

[edhans]

for SQL Server, not that I am aware of. The credientials are not stored in M code or passed by M code. It is in a separate blob in the PBIX file, most likely salted, hashed, and double-encrypted.