Power BI API - Patch Import Azure SQL DB Datasource with Windows credentials

lapora1976 — Wed, 11 Apr 2018 20:12:53 GMT

We are integrating Power BI Embedded within our SAAS application with the need to deploy reports through different environments (Development, System Test, Production) without user intervention. I have successfully been able to import reports, update the datasource on a report dataset to change the server/database (as each of our environments user a different database on the same server) and have also been successful in using the Gateway API to PATCH the credentials using the Basic credential type when using a SQL account that is set up on the database.

I have been unable to do the patch using the Windows credential type. I used the Gateway API call from this doc: Patch data source (gateways) to get the Basic auth working and it states that the Windows credential type should be sent the same way. However, I get the following error back, using fiddler, to the call when using Windows as the credentialType:

{"error":{"code":"DMTS_InvalidCredentialTypeError","pbi.error":{"code":"DMTS_InvalidCredentialTypeError","parameters":{},"details":[],"exceptionCulprit":1}}}

Here is the code used to patch the datasource:

Dim jsonDelta As String = "{""credentialDetails"":{""credentials"":""{\""credentialData\"":[{\""name\"":\""username\"",\""value\"":\""{removed}.onmicrosoft.com\""},{\""name\"":\""password\"",\""value\"":\""{removed}""}]}"",""credentialType"":""Windows"",""encryptedConnection"":""Encrypted"",""privacyLevel"":""None"",""encryptionAlgorithm"":""none""}}"

        Dim patchRequestBody As HttpContent = New StringContent(jsonDelta)
        patchRequestBody.Headers.ContentType = New MediaTypeWithQualityHeaderValue("application/json")
        Dim method = New HttpMethod("PATCH")
        Dim requestCred = New HttpRequestMessage(method, restUrlPatchCredentials)
        requestCred.Content = patchRequestBody

        Dim client As HttpClient = New HttpClient()
        client.DefaultRequestHeaders.Add("Accept", "application/json")
        client.DefaultRequestHeaders.Add("Authorization", "Bearer " & accessToken)
        client.SendAsync(requestCred)

As I said, I used this same code but credentialType Basic and the UN/PW of a valid SQL user and was able to successfully patch the datasource.

I also see the same error when using the Power BI Service and trying to edit the credentials on the Workspace > Settings > Datasets > {DataSet Name} > Data source credentials and selecting Windows as the Authentication method.

The Windows user I am trying to patch the dataset with is an AAD user and is also part of a group with rights to login to the SQL database I am trying to connect to.

Has anyone been able to patch an Import mode dataset with Windows credentials through the API? Could this be a permission issue on the Azure SQL DB?

All of the examples and articles I've seen have used the Basic auth type but we would like to leverage existing users in AAD without the need to add SQL users to each database everytime we add a new client.

Re: Power BI API - Patch Import Azure SQL DB Datasource with Windows credentials

v-micsh-msft — Thu, 12 Apr 2018 09:12:25 GMT

I think the issue here should be related to Permissions (credential type) under the Azure SQL Database.

Azure SQL Database only supports two types of authentication:

1. SQL Authentication

2. Azure Active Directory Authentication,

and AAD authentication supports the following methods of connecting to a database using Azure AD identities:

Using integrated Windows authentication
Using an Azure AD principal name and a password
Using Application token authentication

For more details about how to configure the AAD Authentication, please check:

Azure SQL Database access control

Use Azure Activa Directory Authentication for authentication with SQL Database, Managed instance, or SQL Data Warehouse

How to configure

And the supported credential type for the Power BI API patch is:

"Basic|Windows|Anonymous|OAuth|Key"

There is no Azure Active Directory Authentication listed.

Regards,

Michael

topic Power BI API - Patch Import Azure SQL DB Datasource with Windows credentials in Developer

Power BI API - Patch Import Azure SQL DB Datasource with Windows credentials

Re: Power BI API - Patch Import Azure SQL DB Datasource with Windows credentials