Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Creating new SQL Datasource for Gateway with Powershell.


Is it possible to automatise creating SQL Datasource in specified Gateway with Powershell?
Documentation says it's possible to create new Datasource with Rest API by 'Invoke-PowerBiRestMethod' in PS.
If I'm not wrong as credentials we can use example from here: (update credentials).

So we've got body:

$body = '{
"datasourceType": "Sql",
"connectionDetails": "{\"server\":\"real_sql_server_address\",
"datasourceName": "new_name_for_datasource"
"credentialDetails": {"credentialType": "Basic",
                                "credentials": "{\"credentialData\":[{\"name\":\"username\",
                                 "encryptedConnection": "Encrypted",
                                 "encryptionAlgorithm": "None",
                                 "privacyLevel": "None"

Invoke-PowerBiRestMethod -Url -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",
  "datasourceName": "new_name_for_datasource"
  "credentialDetails": {"credentialType": "Basic",
                        "credentials": "{"credentialData\":[{"name":"username",
                        "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 -Method Post -Body $body
Position       : At line:1 char:1
                 + Invoke-PowerBiRestMethod -Url ...
                 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 -Method Patch -Body $body -Verbose


VERBOSE: Status Code: BadRequest (400)

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


Advocate I
Advocate I

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.

Not applicable

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

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

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.