Gateways - Update Datasource how to build the body in PowerShell

[mark_carlisle]

We have a lot of data sources (~200) and an IS enforced policy to change passwords every 45 days. The current process is for someone to go through these data sources and input the new credentials as and when they change. I want to be able to improve the efficiency of this process by creating a PowerShell script.

I'm using the officially supported Power BI PowerShell module but I'm having difficulty building the Windows version of the -Body for the Invoke-PowerBIRestMethod command.

The request body should be as follows;

{
  "credentialDetails": {
    "credentialType": "Windows",
    "credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"contoso\\john\"},{\"name\":\"password\", \"value\":\"*****\"}]}",
    "encryptedConnection": "Encrypted",
    "encryptionAlgorithm": "RSA-OAEP",
    "privacyLevel": "Organizational"
  }
}

Its the bit in red I cannot create, I can get;

{
  "credentialDetails": {
    "encryptedConnection": "Encrypted",
    "privacyLevel": "Organizational",
    "credentialType": "Windows",
    "encryptionAlgorithm": "RSA-OAEP",
    "credentials": "???"
  }
}

By using the following PowerShell;

$TEST = 
  [pscustomobject]
    @{
      'credentialDetails' = 
        @{
          credentialType = 'Windows'; 
          credentials = '???';
          encryptedConnection = 'Encrypted'; 
          encryptionAlgorithm = 'RSA-OAEP'; 
          privacyLevel = "Organizational"
        }
    } 
  | ConvertTo-Json

How do I build in the red text into the PowerShell above, I've tried various things but cannot seem to get the syntax correct.

Thanks

[mark_carlisle · Accepted solution]

Solution

$TEST = @{
      'credentialDetails' = 
        @{
          credentialType = 'Windows'; 
          credentials = '{"credentialData":[{"name":"username", "value":"<REDACTED>"},{"name":"password", "value":"<REDACTED>"}]}';
          encryptedConnection = 'Encrypted'; 
          encryptionAlgorithm = 'RSA-OAEP'; 
          privacyLevel = "Organizational"
        }
    }   | ConvertTo-Json

[Anonymous]

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"?

[mark_carlisle]

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!