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.

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

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's avatar
awg201
Frequent Visitor
1 year ago

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