Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |