How to update PowerBI Datasource credentials for an Azure AD application (service principal)

Question

I have an Azure AD Service Principal which is the admin of a Azure SQL Database. There is a PowerBI dashboard with a DataSource that connects to the Database. After publishing the dashboard programmatically (CI/CD), I need to update the parameters and the Datasource credentials.

Considering the examples from the documentation, what credentialDetails definition is required to authenticate to Azure SQL Server using a Service Principal client id and credential?

The only way I managed to have access with the SP was using OAuth2 with an accessToken for the database scope. However, the token expires in 1 hour which would force a redeployment of the dashboard continuously.

{
  "credentialDetails": {
    "credentialType": "OAuth2",
    "credentials": "{\"credentialData\":[{\"name\":\"accessToken\", \"value\":\"eyJ0....fwtQ\"}]}",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "None",
    "privacyLevel": "None"
  }
}

Using Azure AD Service Principal authentication is required for compliance policies within the company.

Example of the error message when using Basic credential type is used:

{
   "error":{
      "code":"DM_GWPipeline_Gateway_DataSourceAccessError",
      "pbi.error":{
         "code":"DM_GWPipeline_Gateway_DataSourceAccessError",
         "parameters":{
            
         },
         "details":[
            {
               "code":"DM_ErrorDetailNameCode_UnderlyingErrorCode",
               "detail":{
                  "type":1,
                  "value":"-2146232060"
               }
            },
            {
               "code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage",
               "detail":{
                  "type":1,
                  "value":"Login failed for user 'aad_app_name'. Reason: Azure Active Directory only authentication is enabled. Please contact your system administrator."
               }
            },
            {
               "code":"DM_ErrorDetailNameCode_UnderlyingHResult",
               "detail":{
                  "type":1,
                  "value":"-2146232060"
               }
            },
            {
               "code":"DM_ErrorDetailNameCode_UnderlyingNativeErrorCode",
               "detail":{
                  "type":1,
                  "value":"18456"
               }
            }
         ],
         "exceptionCulprit":1
      }
   }
}

Found similar question: https://stackoverflow.com/q/67826138/377118 — Emer, Jul 30 '22 at 06:52

score 0 · Answer 1 · answered Jul 30 '22 at 14:44

The error:

 Login failed for user 'username'. Reason: Azure Active Directory only authentication is enabled.
      Please contact your system administrator.

occurs Azure AD-only authentication is enabled while trying to test with (SSMS) to actually connect to SQL Database or SQL Managed Instance as in this case SQL authentication is disabled at the server or managed instance level and prevents any authentication based on any SQL authentication credentials. So try using SQL authentication for the connection.

To allow both SQL and Azure AD authentication for Azure SQL to connect, We may need to disable the Azure AD-only authentication.

To disable using Azure cli.

Sign into Azure account having the SQL Security Manager role.

az login
diasble command by replacing with SQL server name, and with your Azure Resource that holds the SQL server

az sql server ad-only-auth disable --resource-group <myresource> --name <myserver>

After disabling Azure AD-only authentication, try updating the credentials .

You can also disable by using azure portal by unchecking the Support only Azure Active Directory authentication for this server .

Reference : Enable Azure Active Directory only authentication - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs

Also please check the code snippet below from update Azure SQL datasource credentials of PowerBI dataset programmatically? - Stack Overflow using with Microsoft powerbi commandlet if required.

$patchBody = @{
    "credentialDetails" = @{
      "credentials" = "{""credentialData"":[ $userNameJson, $passwordJson ]}"
      "credentialType" = "Basic"
      "encryptedConnection" =  "NotEncrypted"
      "encryptionAlgorithm" = "None"
      "privacyLevel" = "Organizational"
    }
  }

  # Convert body contents to JSON
  $patchBodyJson = ConvertTo-Json -InputObject $patchBody -Depth 6 -Compress

  #PATCH operation to update datasource credentials
  Invoke-PowerBIRestMethod -Method Patch -Url $datasourePatchUrl -Body $patchBodyJson

Thanks Kavya for the detailed explanation. The answer suggests to do a workaround by enabling SQL Authentication and disabling Azure AD-only authentication. Does that mean, it is not possible to do what I am asking? — Emer, Aug 01 '22 at 09:52
If Azure AD-only authentication feature is disabled, it means you will be allowing both SQL authentication and Azure AD authentication for Azure SQL. And yes ,AFAIK its not possible only with azure ad enabled if it requies to connect to Azure SQL Server through (server)ssms .But you may force to use servers by AzureADONLY using [azure policy](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication?view=azuresql&tabs=azure-cli#feature-description) but also please check the limitation below. — kavyaS, Aug 01 '22 at 10:11

How to update PowerBI Datasource credentials for an Azure AD application (service principal)

1 Answers1

Linked