Forum Discussion

awg201's avatar
awg201
Frequent Visitor
1 year ago
Solved

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 gatew...
Need Help
  • Wikkleyn_81's avatar
    Wikkleyn_81
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.