AAS Refresh Issue: The credentials provided for the SQL source are invalid

Vipul Singh 21 Reputation points
2021-04-20T21:02:21.237+00:00

We have been trying to refresh AAS(full process) from SSMS with AAD authentication and from Azure runbooks with Service Principal authentication.
But, we are getting below error in both the scenario:

Invoke-ProcessASDatabase : Failed to save modifications to the server. Error returned: 'The credentials provided for
the SQL source are invalid. (Source at "synapse sql pool server/db name".). The exception was
raised by the IDbCommand interface.

Technical Details:
RootActivityId: 349070d5-09f3-4496-9f2b-69203c485e04
Date (UTC): 4/20/2021 7:54:43 PM
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.

for Azure runbook method, we have followed the below link, but getting above error.
[https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-refresh-azure-automation][1]

Our Source for AAS is Synapse Sql Pool.

How to resolve above error. Let me know if more information is required.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
2,626 questions
Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
338 questions
No comments
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 33,136 Reputation points Microsoft Employee
    2021-04-21T06:23:11.65+00:00

    Hi @Vipul Singh ,

    Thanks for reaching out.

    Usually this error occurs when an invalid AAD token is used for authentication. Also please check if AAD account used in the data source credential of AAS has MFA enabled? If you would like to use Oauth2 authentication from AAS to Azure synapse, please try to use a normal AAD account which does not have MFA or location control constraints as those accounts would have limitations to retrieve access token once the previous access token expires.

    Regarding Azure AD account (Oauth):
    This will specify the Azure AD access token and refresh token (and some other properties) on the connection string.
    The Azure AD access token is valid for one hour, after this the AS engine uses the refresh token to request a new access token which will than be used to login to Azure SQL DB

    So other possibility could be access token couldn’t be refreshed.
    Possible reasons for this may be:

    • There was no refresh token specified, this may happen if the connection was updated but didn’t also include a valid refresh token
    • The refresh token was expired, refresh tokens are valid for 90 days, unlikely but if the connection wasn’t update in the last 90 days this may happen.
    • Some issue on Azure AD side to refresh the token (eg Azure AD conditional access rules, ...)

    Hope this info helps. Do let us know how it goes.

    ----------

    Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.


1 additional answer

Sort by: Most helpful
  1. Enkay 1 Reputation point
    2021-06-22T21:46:03.227+00:00

    @Vipul Singh We recently started seeing the exact same issue in our processes and currently do not have a remediation. The cube refreshes in our environment are auto scheduled and run via MFA disabled accounts but have started failing citing credential issues. Were you able to resolve yours finally?