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
PSV2934
Advocate I
Advocate I

Can't refresh Semantic Model Connectors: SQL + Sharepoint

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:

PSV2934_0-1710170789519.png

 


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

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

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.

vhuijieymsft_0-1710223055037.png

 

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

vhuijieymsft_1-1710223055039.png

vhuijieymsft_0-1710223132277.png

 

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

vhuijieymsft_1-1710223156086.png
vhuijieymsft_2-1710223156086.png

 

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!

View solution in original post

4 REPLIES 4
v-huijiey-msft
Community Support
Community Support

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.

vhuijieymsft_0-1710223055037.png

 

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

vhuijieymsft_1-1710223055039.png

vhuijieymsft_0-1710223132277.png

 

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

vhuijieymsft_1-1710223156086.png
vhuijieymsft_2-1710223156086.png

 

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!

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

lbendlin
Super User
Super User

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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