Scheduled Refresh Data source credentials

[juststarting]

Schedule Refresh failed because one or more data source is missing credentails.  this is the error I get when trying to schedule dataset refresh in Service.  In Desktop, can refresh report to bring back up to date information, but when published to Service, get a yellow triangle when trying to Refresh along with the error message. 

We are not allowed to set up Personal Gateway, have to use the business one. 

The report is built on data via connection to our SQL Management Server tables, I have also imported some of my pre existing sql that was written from the same server via the Advanced option, I also get data from Excel held in on premis Sharepoint and from Excel held in my network folder.  In Service, the Data Source credential area is greyed out, so had to go back to Desktop. 

I cleared the permissions, saved report, closed it.  Re -opened report and then Edited the permissions for all datasets to Windows and privacy setting as Organisational.  Then Refreshed report, results can back as expected, no errors.  Published and saved report.  Opened the Service and tried Refresh again, same error.  Can anyone advise what else we need to do to resolve this, thank you

[aj1973 · Accepted solution]

Yes, if a Standard Gateway is already installed on prem then the Tenant admin needs to add it and add you as a user in the service. Once done the you will be able to add your source to the gateway and connect your semantic model

[Anonymous · Accepted solution]

Hi @juststarting ,

Because your data source is on-premises MSSQL Database, you need to install and configure the Standard Mode Gateway in your local machine, please confirm the gateway could access the Database successfully, then you need add a data source for gateway in Power BI Gateway Portal, then you could set the gateway and connection in the semantic model settings, then you could refresh this semantic model.

For more details, you could read related document: Manage a SQL Server data source - Power BI | Microsoft Learn 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[juststarting · Accepted solution]

Update to original post.  Our IT had already set up the on site organisation Gateway, they had also added me onto the Gateway but Refresh was still failing, so the Gateway was not the issue.  On reports, I had added in SQL script that I had previously written in SQL Management Studio as Power Bi could not give me the links I required.  I noticed that when I clicked onto the created tables, the View Native Query was greyed out, so "Folding" was not occurring.  Searched on Youtube and was able to find several solutions to this, ie to activate View Native Query.  Once this was done, re ran report in Desktop to ensure still working, then Saved and re published report.  In Service was then able to complete a successful Refresh and scheduled Refresh.  Thank you to everyone who tried to assist with this, much appreciated

[Anonymous · Accepted solution]

Hi @juststarting ,

Because your data source is on-premises MSSQL Database, you need to install and configure the Standard Mode Gateway in your local machine, please confirm the gateway could access the Database successfully, then you need add a data source for gateway in Power BI Gateway Portal, then you could set the gateway and connection in the semantic model settings, then you could refresh this semantic model.

For more details, you could read related document: Manage a SQL Server data source - Power BI | Microsoft Learn 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[juststarting]

Hi Community champion, thank you for your reply, but it does not make any sense to me.  I have already explained that my organisation does not allow the use of personal gateways, instead we use the one set up by the company IT TEam if required(standard mode).  If you are saying that our IT Team(Admin) need to do something in relation to the Gateway, can you expand on what this is, thank you again for your assistance

[aj1973 · Accepted solution]

Yes, if a Standard Gateway is already installed on prem then the Tenant admin needs to add it and add you as a user in the service. Once done the you will be able to add your source to the gateway and connect your semantic model

[aj1973]

Hi @juststarting 
This is a permission that needs to be allowed by the Power BI Admin of the Tenant. A personnal Gateway is not allowed to be used in this Tenant.