cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sm3
Frequent Visitor

Creating new SQL Datasource for Gateway with Powershell.

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


3 REPLIES 3
MarcelSchue
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.

Anonymous
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

Announcements
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.