Forum Discussion
Gateways - Update Datasource how to build the body in PowerShell
Solution
$TEST = @{ 'credentialDetails' = @{ credentialType = 'Windows'; credentials = '{"credentialData":[{"name":"username", "value":"<REDACTED>"},{"name":"password", "value":"<REDACTED>"}]}'; encryptedConnection = 'Encrypted'; encryptionAlgorithm = 'RSA-OAEP'; privacyLevel = "Organizational" } } | ConvertTo-Json
Q1: Did this work for anyone?
I am getting following error:
{
"error": {
"code": "DM_GWPipeline_UnknownError",
"pbi.error": {
"code": "DM_GWPipeline_UnknownError",
"parameters": {},
"details": [
{
"code": "DM_ErrorDetailNameCode_UnderlyingErrorMessage",
"detail": {
"type": 1,
"value": "Invalid Ciphertext size. Expecting cipher text of atleast length, 344. Provided cipherText length, 104"
}
},
{
"code": "DM_ErrorDetailNameCode_UnderlyingHResult",
"detail": {
"type": 1,
"value": "-2146233296"
}
}
],
"exceptionCulprit": 1
}
}
}
When trying with following body request:
{
"dataSourceType": "SQL",
"connectionDetails": "{\"server\":\"localhost\",\"database\":\"AdventureWorksDW2017\"}",
"credentialDetails": {
"credentialType": "Basic",
credentials: '{"credentialData":[{"name":"username", "value":"<123>"},{"name":"password", "value":"<123>"}]}',
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "RSA-OAEP",
"privacyLevel": "Organizational"
},
"dataSourceName": "Test_SQL"
}
I also tested with encryptionAlgorithm = None or / and encryptedConnection = NotEncrypted
Q2: Anyone has an idea how to integrate "skipTestConnection": "true"?
No, I raised a ticket with MS on this a while ago and found that the actual credentials need to be encrypted with RSA-OAEP before submitting the request via the API, i.e. in PowerShell, the API does not do this for you, which I guess makes sense.
MS do have some C# examples on how to achieve (https://docs.microsoft.com/en-us/power-bi/developer/automation/configure-credentials?tabs=sdk3) this but they do not have PowerShell examples, nor were there any forthcoming when I raised my ticket.
I gave up after this as we only have ~30 data sources and while its a pain to change them one by one every n days its quicker than pouring in many weeks of dev time to learn and then develop a solution.
There is however a Dev Camp on 24/09 discussing PowerShell and the Power BI REST API (https://powerbi.microsoft.com/en-us/blog/join-us-power-bi-dev-camp-thursday-september-24-2020/) so hopefully this is on the agenda for discussion!