Can't refresh Semantic Model Connectors: SQL + Sharepoint

[PSV2934]

Hi all,

Preface
I have just updated one of my dashboards to use a sql connector instead of a sharepoint connector. This report was refreshing perfectly fine before. In power query I clicked Get Data > SQL > [Entered credentials with a SQL query to match my sharepoint excel. (The SQL Authentication Method is "Windows"), (The Sharepoint Authentication Method is "Organizational Account").

Then once power query loaded the source I would take the source of my new query, copy it, go to my old table and paste the source into my old table's source step. Once this was done I would delete the new query bc the source step was in my old query, then I would clean up the steps of my old query. 

After doing this on the PBI desktop app I checked the report and everything looks good and all reports are loading correctly. There are 2 excels that dont exist in our data warehouse and I've kept them in the sharepoint without touching them and they are loading into the model on the desktop app perfectly fine as before.

Issue
On the service I am unable to refresh the semantic model and when I go to view my data source credentials the option is grayed out as shown below:

In the desktop I can refresh my model without an issue, how can I fix this for the service so I can setup refresh schedule.

Is this because I am using windows credentials as my SQL authentication method and don't have a personal or organizational gateway setup?
Thanks for any help,
PSV

[v-huijiey-msft · Accepted solution]

Hi @PSV2934 ,

@lbendlin said it well.

When using the Power BI service for automatic refresh, please ensure that the Power BI gateway is installed and correctly configured.

The gateway acts as a bridge between on-premises data and cloud services. It is essential for refreshing data sets that are not directly connected to cloud services.

I tested it with a gateway and found that this was feasible. You can follow the steps below.:

Select the gear icon in the upper right corner.

Click new in the upper left corner to configure data source information.

After the configuration is complete, go to the workspace and select semantic model.

Under the "Gateway and cloud connections" tab, select the gateway you just configured.

For more information, please see:

Manage your data source - import and scheduled refresh - Power BI | Microsoft Learn

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[PSV2934]

Thx for being so verbose in your response! Makes it easy to follow.

[lbendlin]

Your SQL Server connection requires a gateway.  Open the gateways section and configure it accordingly.

[PSV2934]

Thx for the clarity. Assumed it was this but wasn't sure if a gateway would allow me to connect either because Windows Auth is using the principal user token.