PowerBI not refreshing online with SQL connection

[TameshDoobay]

Hi,

In PowerBI Desktop, my report works and when refreshed, it displays updated info. My datasource is SQL.

I've created a gateway and things seem to be connected fine there, I don't see any issues with it in the settings.

But when I publish and try to refresh on the online PowerBI, it doesn't update. When I check the lineage, I see this issue.

Something went wrong

There was an error when processing the data in the dataset.

Please try again later or contact support. If you contact support, please provide these details.Hide details

• Data source error{"error":{"code":"DM_GWPipeline_Gateway_ImpersonationError","pbi.error":{"code":"DM_GWPipeline_Gateway_ImpersonationError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: employees.
• Cluster URIWABI-WEST-US-D-PRIMARY-redirect.analysis.windows.net
• Activity ID85048699-741e-4fe6-a403-90d815913671
• Request ID7b4c6ffd-8d7d-183a-5718-3159dada69cc
• Time2024-06-06 15:37:45Z

  I'm not sure what's causing this issue?

[Kaviraj11 · Accepted solution]

Hi,

 Here are some steps you can take to troubleshoot this issue:

1. Check the Authentication Method: Make sure the account being used matches the Authentication Method. If you’re using Windows Authentication, ensure that the gateway is installed on a machine that’s joined to the same domain as the server running the SQL Server instance.

2. Test the Credentials: Use SQL Server Management Studio (SSMS) to connect to your SQL Server using the same account and credentials. If there are no issues in SSMS, the problem might be with the data gateway.

3. Check the Gateway Connection: If you’re using an on-premise data gateway, ensure that it’s installed correctly and that it can connect to your data source.

4. Inspect the Data Source Settings: In some cases, the issue might be resolved by adjusting the settings of the data source in Power BI.

Solved: DM_GWPipeline_Gateway_ImpersonationError on refres... - Microsoft Fabric Community

[Anonymous · Accepted solution]

Hi,@TameshDoobay Hello,@blopez11, @Kaviraj11

thanks for your concern about this issue.

Your answesr are excellent!
And I would like to share some additional solutions below

According to your description, you are having a problem configuring the gateway connection on the power BI service.

Since you said that you are connecting to SQL Server as your data source.

So I guess you are using import/Direct Query connection mode.

Let me walk you through the process of setting up the gateway connection (since you said you have the option DirectQuery with SSO, I assume you are using the DirectQuery connection mode to connect to the database SQL Server)

Here is how I created the report, published it to the service and set up the gateway connection.

You need to make sure that you have created the gateway and that it is online.

You need to log in to the “On-premises data gateway ” application (log in to your gateway using your account on your Power BI service).

Please check if the gateway is online on service

Create a gateway connection:

The Signgle sign-on feature is optional, and when it is turned on, users can use a single account for multiple authentications without having to repeatedly change authentication information (but this feature has nothing to do with your current gateway connection authentication errors and can be ignored)

Here is the official documentation for this feature.
Test single sign-on (SSO) configuration - Power BI | Microsoft Learn

The gateway connection was set up successfully (note here that you must ensure successful authentication when setting up authentication for the gateway connection)

The account that is authenticated by windows here is also the valid user that logs into SQL Server Management Studio (SSMS).

The valid user who logs into SQL Server Management Studio (SSMS), usually a domain user

You have to make sure that the account is successfully authenticated (in fact I think this is where the problem lies: authentication failure).

semantic model refreshes successfully

Here are some directions for troubleshooting:

1. Check that the authentication is performed correctly and successfully.
Use Dataset owner’s security context(UPN) for Refresh-based Reports (Public Preview) | Microso...

2. Through the local gateway application to check: export the gateway connection log, which saves a lot of gateway information, including the cause of the error report

Here is the official documentation on the subject, which we hope you will find helpful
Manage a SQL Server data source - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[blopez11]

How is the gateway connection setup?  Is it DirectQuery with SSO?

[Kaviraj11 · Accepted solution]

Hi,

 Here are some steps you can take to troubleshoot this issue:

1. Check the Authentication Method: Make sure the account being used matches the Authentication Method. If you’re using Windows Authentication, ensure that the gateway is installed on a machine that’s joined to the same domain as the server running the SQL Server instance.

2. Test the Credentials: Use SQL Server Management Studio (SSMS) to connect to your SQL Server using the same account and credentials. If there are no issues in SSMS, the problem might be with the data gateway.

3. Check the Gateway Connection: If you’re using an on-premise data gateway, ensure that it’s installed correctly and that it can connect to your data source.

4. Inspect the Data Source Settings: In some cases, the issue might be resolved by adjusting the settings of the data source in Power BI.

Solved: DM_GWPipeline_Gateway_ImpersonationError on refres... - Microsoft Fabric Community