CODEWITHSUNDEEP
powershelloauth-2.0azure-active-directoryazure-data-lakeazure-analysis-services
Ashish-BeJovial
Ashish-BeJovial

Reputation: 1867

Service to service authentication is failing for Azure Datalake Gen1 to Azure Analysis services

I am using Azure Data lake Gen1 and Azure Analysis Services. I am authorizing my Azure Analysis Services Data models with Azure Data lake connection.

For this activity, Microsoft maintains a document. I am following the same way. Based on the theory of this document I have created a PowerShell script, which executes and brings access_token. As I get access_token, I update the XMLA of the Azure Analysis Services Data model connection part and deploys it.

I have tried to achieve it with End-User authentication mechanism, but that didn't work.

As I deploy and process the Same model, it works fine but as I process after 1 hour it gives me the following error.

Failed to save modifications to the server. Error returned: '<pii>The credentials provided cannot be used for the DataLake source. (Source at https://mydatalake.azuredatalakestore.net/.)</pii>. The exception was raised by the IDbCommand interface.

Technical Details:
RootActivityId: 46646584-7ccb-4946-a38c-b91c1963e82c
Date (UTC): 9/13/2021 7:53:10 PM
<pii>The credentials provided cannot be used for the DataLake source. (Source at https://mydatalake.azuredatalakestore.net/.)</pii>. The exception was raised by the IDbCommand interface.
<pii>The credentials provided cannot be used for the DataLake source. (Source at https://mydatalake.azuredatalakestore.net/.)</pii>. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
'.

My PowerShell Code

$dataModelsList = "MY-DM-Cost-Test"
$datalakeName= 'mydatalakename'
$aasName= 'asazure://aspaaseastus2.asazure.windows.net/myaasname'

$password = ConvertTo-SecureString -String "lajsdfkjjfdakasjdfhjkud&98asdllfkf" -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",$password)
Connect-AzAccount -Credential $credential -Tenant $tenantID -ServicePrincipal

$authUrl = "https://login.windows.net/" + $tenantID + "/oauth2/token/"
$body = @{
    "resource" = "https://management.azure.com/";
    "grant_type" = "client_credentials";
    "client_id" = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
    "client_secret" = "lajsdfkjjfdakasjdfhjkud&98asdllfkf"
}

$adlsToken = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body

$date = Get-Date -Format r
$password = ConvertTo-SecureString -String $secretKey -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential($ClientID,$password)
Connect-AzAccount -Credential $credentials -Tenant $tenantID

for($f = 0; $f -lt $dataModelsList.Count; $f++)
{
    if($dataModelsList.Count -eq 1)
    {
        $AASDatabaseName = $dataModelsList
    }
    else
    {
        $AASDatabaseName = $dataModelsList[$f]
    }

    Write-Output "Refreshing $AASDatabaseName data model ..."
    $updateDataSource = '
    {
        "createOrReplace": {
            "object": {
                "database": "'+$AASDatabaseName+'",
                "dataSource": "DataLake/https://'+$datalakeName+' azuredatalakestore net/",
            },
            "dataSource": {
                "type": "structured",
                "name": "DataLake/https://'+$datalakeName+' azuredatalakestore net/",
                "connectionDetails": {
                    "protocol": "data-lake-store",
                    "address": {
                        "url": "https://' + $datalakeName + '.azuredatalakestore.net"
                    }
                },
                "options": {
                    "pageSize": 999999999
                },
                "credential": {
                    "DataSourceKind": "DataLake",
                    "AuthenticationKind": "OAuth2",
                    "Expires": "'+$date+'",
                    "RefreshToken":"'+$adlsToken.access_token+'",
                    "token_type": "Bearer",
                    "scope": "user_impersonation",
                    "ext_expires_in": "'+$adlsToken.ext_expires_in+'",
                    "expires_on": "'+$adlsToken.expires_on+'",
                    "not_before": "'+$adlsToken.not_before+'",
                    "resource": "https://management.azure.com",
                    "AccessToken":"'+$adlsToken.access_token+'"
                }
            }
        }
    }'

    $result = Invoke-ASCmd -Server $AASServerName -Database $AASDatabaseName -Query $updateDataSource -Credential $credentials #-ServicePrincipal
}

Above code returns following resposne.

token_type     : Bearer
expires_in     : 3599
ext_expires_in : 3599
expires_on     : 1631542903
not_before     : 1631539003
resource       : https://management.azure.com/
access_token   : eyxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xx.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxx
                 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
                 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
                 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
                 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
                 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

As far as I have understood, my Data models fails to refresh after 1 hour because I am not getting refresh token in response, so with the help of may be I could have get new access token. But it is just an imaginary idea for me.

Please help me to understand why my Azure Analysis Services Data model is failing to process after one hour followed by refresh credentials using above scripts.

Upvotes: 1

Views: 408

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36332

Your token is expiring, which isn't really a huge issue since you can just test to see if your token is valid and get a new one if it is not. So there is a property in the token response expires_on that is the number of seconds past 1970-01-01T0:0:0Z UTC that represents when the token will expire. So, we can test against that simply enough with:

If(([datetime]::UtcNow - [datetime]'01/01/1970 00:00:00Z').totalseconds -gt $adlsToken.expires_on){ $adlsToken = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body }

Now we just put that before you reference the access token, and you should be all set.

    Write-Output "Refreshing $AASDatabaseName data model ..."
    If(([datetime]::UtcNow - [datetime]'01/01/1970 00:00:00Z').totalseconds -gt $adlsToken.expires_on){ $adlsToken = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body }
    $updateDataSource = '

Upvotes: 0

Related Questions