Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
Need Help
GilbertQ's avatar
GilbertQ
Super User
6 years ago
Hi there

You will have to turn off the Gateway option for it to work?
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 

  • GilbertQ's avatar
    GilbertQ
    Super User
    6 years ago
    Can you post the connection string to the SQL Server?

    As well as the actual error message?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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)