Script a new connection

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?

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

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
                }
            )
        }
    }