Cannot add SQL SSMS to On-Prem Gateway

[awg201]

I have a report that uses a PBI semantic model and SQL query. All connections are direct query, hence the requirement to use on premeses gateway.

When I try adding the SQl connection to the gateway, I get the following error:

Unable to update connection credentials.
The on-premises data gateway's service account failed to impersonate the user.

Details:

CCQ_BIGW: Received error payload from gateway service with ID 131414: Error logging on username 'MYDOMAIN\username_here'..

Please have this information handy if you choose to create a support ticket.

Session Id: 6616bc07-026b-4068-9712-0ba4758b62e5
RequestId: 9a37fcff-f30e-4382-adf2-bf89729e4018
Cluster URI: https://api.powerbi.com
Status code: 400
Time: Fri Jun 27 2025 11:08:09 GMT+1000 (Australian Eastern Standard Time)

I usually connect to our SSMS via Windows Authentication SSO. Is there something I need to do to enable the GW to connect?

Thanks!

[v-agajavelly]

Hi @awg201  ,

Thanks for the clarification you're absolutely right to suspect a mismatch between your gateway configuration and SQL authentication setup.

From your update, the core issue seems to be that your SQL Server connection in Power BI is configured to use Windows Authentication, but the on-premises gateway is running under a different service account (your IT admin account) which does not have permission to access SQL Server.

1. Identify the Windows account running the on-premises data gateway service.
2. Grant this account login and necessary database permissions on your SQL Server.
3. In Power BI Service, under Manage Gateways, edit the SQL data source:
• Use Windows Authentication.
• Enter the credentials of the same account that has SQL Server access.

Alternatively, use SQL Authentication with a dedicated SQL login instead of Windows Authentication. To verify which account is being used and has access, try logging into SQL Server Management Studio (SSMS) using the gateway service account. If login fails, that confirms the access issue.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Thank's,
Akhil.

[Akash_Varuna]

Hi @awg201 It might be due to the incremental refresh policy overwriting the manually created partition during the model refresh. To retain the cold data partition while allowing incremental refresh, avoid manually creating partitions through XMLA after publishing. Instead, configure your incremental refresh policy to handle cold and recent data, or maintain archived data separately in your source system or a separate model.

[awg201]

Thanks, I have a feeling it is not as complicated as that.

I have an admin account that is used to access the on-prem gateway as this is how IT has configured it.

Yet my SQL account uses my standard user account and is connected via Windows SSO. I have a feeling these are conflicting as my admin account does not have any SQl server access...