Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Credentials are required to connect to the SQL source when using parameter for query

Hello,

 

I have a report that is scheduled to refresh everyday and it has been working fine until yesterday.  I noticed that it failed to refresh and it looked like gateway not configured correctly (see image below).  The gateway is fine for other report.  I could not figure out what caused the problem so I changed the query then able to select the gateway but getting another error 'Credentials are required to connect to the SQL source' when trying to refresh.   My query has parameter.  If I remove the parameter then I'm able to refresh the dataset.  I don't understand.  I can refresh the report with parameter query before.  Not sure what happened or what I need to change in order for this to work again.  Please advise.

 

Thanks

Vy

 

vyle_1-1593219129119.png

 

vyle_0-1593218725967.png

 

10 REPLIES 10
ITSNev
Frequent Visitor

I'm having the same problem, was working fine last week not getting this error but only for some of the reports.

I think Microsoft may have PATCH something and broke it as usual. Hope they fix this soon as refreshing manually now.


 

Same here, but with Oracle data source and on-premises properly configured.
Other reports ( without parameters used in the query text) updating nice using the same gateway with the same credentials.

GilbertQ
Super User
Super User

Hi there

You will have to turn off the Gateway option for it to work?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi @GilbertQ ,

 

I turned off the Gateway but still getting this error 'Credentials are required to connect to the SQL source.' when trying to refresh.  Please advise what else can I try.

 

Thanks

Vy 

Can you post the connection string to the SQL Server?

As well as the actual error message?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi @GilbertQ ,

 

So I created two reports, the first one is calling stored procedures test.spTest and passing in 3 like below

 

let
Source = Sql.Database("server", "database", [Query="exec [test].[spTest] @SiteNo = 3"])
in
Source

 

The second report calling the same stored procedures but passing in the parameter SiteNo.  Both of reports are working fine in Power BI desktop.

 

let
Source = Sql.Database("server", "database", [Query="exec [test].[spTest] @SiteNo = " & Number.ToText(SiteNo)])
in
Source

 

When I publish to my dashboard, I can see the gateway and able to select the data source then refresh the first report but get the message 'not configured correctly' when turn on gateway on the second report.  This just happened recently as of June 25.  Could you please try create a simple report with one parameter and see if it works for you?  Also, I notice if the parameter I have as string then it's working but if parameter is number and I have to use Number.ToText then it's not working.  Same as using Date.ToText() then it's not working.

Anonymous
Not applicable

I have a similar problem.

 

Same reports works on desktop PBI but does not work when trying to update data from web using a gateway.

 

Examples:

I build the query with strings/variables concatenation.

Depending how I set the concatenated variable, it works on both the environments or only from desktop: 
variable = "" WORKS
variable = if 1=1 then "" else "" WORKS ONLY FROM DESKTOP
variable = "'" & "0" & "'" WORKS
variable = "'" & Number.ToText(0) & "'" WORKS ONLY FROM DESKTOP

(These examples are real tests made after I became desperate)

Anonymous
Not applicable

Hi @Anonymous ,

 

It seems to me when use Number.ToText(0) or Date.ToText() then only work in desktop and it's not working when publish.  This just happened recently.  Not sure if need to report this issue.

Can you show us what your data types are set to in the Parameters in your PBIX?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Hi @GilbertQ ,

 

This is my steps:

 

1. Created a stored procedures called spTestVy (see below)

 

vyle_0-1593734467182.png

 

2.  In my Power BI report, added Parameter called 'SiteNo' with type interger

 

vyle_1-1593734554623.png

 

3.  In my power query, I have the following.  Need to use Number.ToText() to convert.  If not, I'm getting error can't concatenate string with number

 

vyle_2-1593734630285.png

 

4.  In my report, only display this

 

vyle_3-1593734724717.png

 

5.  Published the report to my dashboard.  Go to settings -> under Gateway connection, I get the message not configured correctly.

 

vyle_4-1593734810171.png

 

6.  Now if I change my parameter type to string then the gateway can see datasource.

 

vyle_5-1593734942984.png

 

vyle_6-1593735031886.png

 

vyle_7-1593735180994.png

 

Something changed because it was working before when having the paramter as integer.  

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors