Forum Discussion

mark_carlisle's avatar
mark_carlisle
Advocate IV
7 years ago
Solved

Gateways - Update Datasource how to build the body in PowerShell

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

Need Help
  • mark_carlisle's avatar
    mark_carlisle
    7 years ago

    Solution

     

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

3 Replies

  • mark_carlisle's avatar
    mark_carlisle
    Advocate IV
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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