Script a new connection

[daive]

Hi all,

Is it possible to script a new connection? As in this action:

This is my script, its failing with a 404 Not Found

$tenantId = "<tenant-id>"
$clientId = "<client-id>"
$clientSecret = "<client-secret>"
$gatewayId = "<your-gateway-id>"
$resource = "https://analysis.windows.net/powerbi/api"
$loginUrl = "https://login.microsoftonline.com/$tenantId/oauth2/token"

# Get Access Token for Power BI API
$body = @{
    grant_type    = "client_credentials"
    client_id     = $clientId
    client_secret = $clientSecret
    resource      = $resource
}
$tokenResponse = Invoke-RestMethod -Method Post -Uri $loginUrl -Body $body
$accessToken = $tokenResponse.access_token

# Define API Endpoint
$uri = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources"

# Define Connection Details
$body = @{
    "dataSourceType" = "Sql Server"
    "connectionDetails" = @{
        "server" = "your-sql-server-name"
        "database" = "your-database-name"
    }
    "credentialDetails" = @{
        "credentialType" = "ServicePrincipal"  # Using Service Principal
        "credentials" = @{
            "applicationId" = "$clientId"
            "tenantId" = "$tenantId"
            "clientSecret" = "$clientSecret"
        } | ConvertTo-Json -Compress
        "encryptedConnection" = "Encrypted"
        "privacyLevel" = "Organizational"
    }
} | ConvertTo-Json -Depth 10

# API Headers
$headers = @{
    Authorization = "Bearer $accessToken"
    "Content-Type" = "application/json"
}

# Make API Call to Create the Data Source
Invoke-RestMethod -Uri $uri -Method Post -Headers $headers -Body $body

Hopefully this is possible 🙂

Thanks,
David

[MaZoSR]

The Power BI API does not support ServicePrincipal as the credentialType. You will have to use the Fabric API instead.

You will also have to encrypt the Service Principal credentials. This can be done as follows (using the pre-release v5 version of the Microsoft.PowerBI.API library - unfortunately no helper class exists for ServicePrincipal so doing this manually instead):

$credentialData = @{
        credentialData = @(
            @{
                name = "tenantId"
                value = $tenantId
            },
            @{
                name = "servicePrincipalClientId"
                value = $clientId
            },
            @{
                name = "servicePrincipalSecret"
                value = $secret
            }
            )
        } | ConvertTo-Json -Depth 3

    $gatewayKeyObject = [Microsoft.PowerBI.Api.Models.MicrosoftPowerBIApiModelFactory]::GatewayPublicKey($gatewayExponent, $gatewayModulus)
    $credentialsEncryptor = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObject)
    $spnCreds = $CredentialsEncryptor.EncodeCredentials($credentialData)

    $credentialDetails = @{
        singleSignOnType = "None"
        connectionEncryption = "Any"
        skipTestConnection = $true
        credentials = @{
            credentialType = "ServicePrincipal"
            values = @(
                @{
                    gatewayId = $gatewayId
                    encryptedCredentials = $spnCreds
                }
            )
        }
    }

[Anonymous]

Hi @daive,

Thanks @Akash_Varuna for Addressing the issue.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @daive,

As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @daive,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[daive]

According to https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/create-datasource#sql-example 

The body should be formatted like this:

{
  "dataSourceType": "SQL",
  "connectionDetails": "{\"server\":\"MyServer\",\"database\":\"MyDatabase\"}",
  "datasourceName": "Sample Datasource",
  "credentialDetails": {
    "credentialType": "Windows",
    "credentials": "AB....EF==",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "RSA-OAEP",
    "privacyLevel": "None"
  }
}

 
I need to set more options and my script looks ok?

[Akash_Varuna · Accepted solution]

Hi @daive , I think 

• connectionDetails must be a JSON string (not nested).
• credentials must be Base64-encoded.
• Credentials must be encrypted using the gateway's public key.
• And Ensure you have relevant API Permissions
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance