Creating new SQL Datasource for Gateway with Powershell.

sm3 — Thu, 23 May 2019 12:17:41 GMT

Hello,

Is it possible to automatise creating SQL Datasource in specified Gateway with Powershell?
Documentation says it's possible to create new Datasource https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/createdatasource with Rest API by 'Invoke-PowerBiRestMethod' in PS.
If I'm not wrong as credentials we can use example from here: https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/updatedatasource#examples (update credentials).

So we've got body:

$body = '{
"datasourceType": "Sql",
"connectionDetails": "{\"server\":\"real_sql_server_address\",
                       \"database\":\"real_database_name\"}",
"datasourceName": "new_name_for_datasource"
"credentialDetails": {"credentialType": "Basic",
                                "credentials": "{\"credentialData\":[{\"name\":\"username\",
                                                                      \"value\":\"real_sql_username\"},
                                                                     {\"name\":\"password\", 
                                                                      \"value\":\"real_password\"}]
                                }",
                                 "encryptedConnection": "Encrypted",
                                 "encryptionAlgorithm": "None",
                                 "privacyLevel": "None"
},
}'

Invoke-PowerBiRestMethod -Url https://api.powerbi.com/v1.0/myorg/gateways/Id-of-real-gateway/datasources -Method Post -Body $body -Verbose

*with bold are marked real parameters we can use

When we execute above code: we've got response:

Status Code: BadRequest (400)

Above code without slashes and with option to convert to json format doesn't work either:

$body = '{"datasourceType": "Sql",
"connectionDetails": "{"server":"real_sql_server_address",
                       "database":"real_database_name"}",
  "datasourceName": "new_name_for_datasource"
  "credentialDetails": {"credentialType": "Basic",
                        "useEndUserOAuth2Credentials":false,
                        "credentials": "{"credentialData\":[{"name":"username",
                                                              "value":"real_sql_username"},
                                                             {"name":"password", 
                                                              "value":"real_password"}]}",
                        "encryptedConnection": "Encrypted",
                        "encryptionAlgorithm": "None",
                        "privacyLevel": "None"
  },
}'
$body = $body | ConvertTo-Json

After executing command:

Resolve-PowerBIError -last

we receive:

Message        : 400 (Bad Request).
StackTrace     :    w System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
                    w Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__31.MoveNext()
Exception      : System.Net.Http.HttpRequestException
InvocationInfo : {Invoke-PowerBIRestMethod}
Line           : Invoke-PowerBiRestMethod -Url https://api.powerbi.com/v1.0/myorg/gateways/Id-of-real-gateway/datasources -Method Post -Body $body
                  -Verbose
Position       : At line:1 char:1
                 + Invoke-PowerBiRestMethod -Url https://api.powerbi.com/v1.0/myorg/gate ...
                 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HistoryId      : xxx

Even simple update datasource doesn't work:

$body = '{
  "credentialDetails": {
    "credentialType": "Basic",
    "credentials": "{"credentialData":[{"name":"username", "value":"real_sql_username"},{"name":"password", "value":"real_sql_password"}]}",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "None",
    "privacyLevel": "None"
  }
}'

$body = $body | ConvertTo-Json



Invoke-PowerBiRestMethod -Url  https://api.powerbi.com/v1.0/myorg/gateways/Id-gateway/datasources/Id-datasource -Method Patch -Body $body -Verbose

Response:

VERBOSE: Status Code: BadRequest (400)

Am I doing something wrong or it's just doesn't work?
Is it even possible with Powershell?

Greetings

Re: Creating new SQL Datasource for Gateway with Powershell.

Anonymous — Tue, 09 Jul 2019 21:13:51 GMT

I am having the exact same issue. No clue how to resolve. Have you had any luck?

Re: Creating new SQL Datasource for Gateway with Powershell.

Anonymous — Wed, 02 Sep 2020 23:29:40 GMT

Was this issue ever solved? I am getting a 400 bad request.

Re: Creating new SQL Datasource for Gateway with Powershell.

Anonymous — Thu, 17 Sep 2020 12:58:25 GMT

Anyone figured out how to add a datasource without encryption and without credentials like the checkbox "skip test connection"?

I tried using "anonymus" credential type but that did not work yet in my tests.

topic Re: Creating new SQL Datasource for Gateway with Powershell. in Developer

Creating new SQL Datasource for Gateway with Powershell.

Re: Creating new SQL Datasource for Gateway with Powershell.

Re: Creating new SQL Datasource for Gateway with Powershell.

Re: Creating new SQL Datasource for Gateway with Powershell.