Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Pass Credentials to a function to query datasources

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

 

 

 

 

 

1 Reply

  • edhans's avatar
    edhans
    Community Champion

    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.