Forum Discussion
Problem with refresh and scheduled refresh in PowerBi service.
Hi whenever i refresh my semantic model, it throws this error. Anyone knows how to fix it?
I have set up gateway to access the data source which is stored in SQL server.
Data source error: [ValidateMarkupTags]{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[ccon]Microsoft SQL: The SELECT permission was denied on the object 'new_contractproduct', database 'SGH_MSCRM', schema 'dbo'.[/ccon]"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ErrorCode","detail":{"type":1,"value":"10478"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"14"}},{"code":"Microsoft.Data.Mashup.ValueError.ConnectionId","detail":{"type":1,"value":"f2d50e7a-f425-4ee3-8559-0a3ad5840a92"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"[ccon][ccon]10.2.4.164</ip>[/ccon]<ip><ip>10.2.0.0</ip></ip>\crmcgl;SGH_MSCRM[/ccon]"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"[ccon]The SELECT permission was denied on the object 'new_contractproduct', database 'SGH_MSCRM', schema 'dbo'.[/ccon]"}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"229"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.State","detail":{"type":1,"value":"5"}}],"exceptionCulprit":1}}} Table: UCB_SGH.
it looks you account does not have correct access
Microsoft SQL: The SELECT permission was denied on the object 'new_contractproduct', database 'SGH_MSCRM', schema 'dbo'.[/ccon]"}},Validated you are using same credentials that is working fine in desktop
6 Replies
it looks you account does not have correct access
Microsoft SQL: The SELECT permission was denied on the object 'new_contractproduct', database 'SGH_MSCRM', schema 'dbo'.[/ccon]"}},Validated you are using same credentials that is working fine in desktop
Hi Anonymous
Just checking in to see if the previous response provided by arvindsingh802 helped to resolve your issue ?
If not, feel free to share your questions and we’ll be glad to assist.Hi Anonymous
We’d like to confirm whether your query has been addressed. If you still need help, please let us know and we’ll provide further guidance.Hi Anonymous
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hi,
Thank you arvindsingh802 for your help. The database admin has granted me access and I am able to connect to the server already. However, there is a new problem whereby I have "timeout error" when querying from the database.
So, I am building a dashboard which consists of multiple tables (shown in the screenshots). When i "include in report refresh" for the master tables (UCB_Processing, Master_UCL and Master_WJ) and published to PowerBi Service, the refresh failed and gave me this error (in the 2nd screenshot). So, I tried another method of excluding these 3 master tables from refresh, then the refresh is successful BUT it is not fetching the latest data.For some context, UCB_Processing has 13,231 rows, Master_UCL has 8,105 rows and Master_WJ has 2,506 rows. Could it be that the dataset is too huge so the query timeout?All of my datasets are queried from MSSQL Server with SG and INA are on cloud, and the rest of the countries are queried from on-premise MSSQL Server. Data gateway has also been set up to connect to those on-premise databases.To rectify the problem, I had asked my colleague to increase the timeout limit for the gateway to fetch the data and he has set it to 4 hours. For my SQL command, I have changed the CommandTimeOut in the M code to 4 hours maximum too (as shown in the screenshot).I have tried incremental refresh too, but it said M query not foldable error.Could you guide me and point out where the possible error is? As I have been debugging this for weeks.Hi Anonymous
Thank you for providing the details and screenshots. Based on the error message and your description, the refresh failure appears to be related to a gateway or mashup engine timeout during data retrieval from the SQL Server sources. Even though the timeout duration has been increased both in the gateway configuration and within the M code, the issue is likely linked to query performance or folding limitations for the larger master tables.
Here are some points and recommendations to help address the issue:
1.Reduce data volume, simplify joins, and apply filters early.
2.Update the Execution Timeout value in the Mashup.Config file.
3.Ensure sufficient CPU/RAM, restart service, and update to the latest version.
4.Test the SQL connection in SSMS/Excel and check for network delays.
5.Run refreshes during off-peak hours to avoid heavy load.
Hope this helps !!
Thank You.
