Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
awg201
Frequent Visitor

Cannot add SQL SSMS to On-Prem Gateway

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!

1 ACCEPTED SOLUTION
Wikkleyn_81
Super User
Super User

Hi @awg201 Usually when I get that error it is a credential issue. The password expired or needs to be updated.

 

Best would be to use a Service Account or if you then have an Admin account as mentioned where the password doesn't expire often. Otherwise you will have to update the connection regularly. 

 

Firstly I would test connecting to the sql server in SSMS using the admin account. Run SSMS as the Admin user and connect as you would with your account.
https://woshub.com/run-program-as-different-user-windows/ 

If that works. Create the gateway connection.

When creating the connection make sure you use Windows authentication and user the domain and username.

View solution in original post

6 REPLIES 6
v-agajavelly
Community Support
Community Support

Hi @awg201 ,

I’m following up to confirm that all items discussed have been addressed. If there’s anything else you need, I’d be happy to assist.

Regards,
Akhil.

Wikkleyn_81
Super User
Super User

Hi @awg201 Usually when I get that error it is a credential issue. The password expired or needs to be updated.

 

Best would be to use a Service Account or if you then have an Admin account as mentioned where the password doesn't expire often. Otherwise you will have to update the connection regularly. 

 

Firstly I would test connecting to the sql server in SSMS using the admin account. Run SSMS as the Admin user and connect as you would with your account.
https://woshub.com/run-program-as-different-user-windows/ 

If that works. Create the gateway connection.

When creating the connection make sure you use Windows authentication and user the domain and username.

This was my suspicion and was right. The gateway is running under an admin account, so when using windows credential, it had failed because that account didn't have access to the database.

 

After adding db_reader pemissions in SSMS, i was able to add the connection without errors... only took my IT department a month to finally answer my ticket... 🙄

v-agajavelly
Community Support
Community Support

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
Super User
Super User

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.

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... 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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