Functions/CatalogItems/Rest/Set-RsRestItemDataSource.ps1

# Copyright (c) 2017 Microsoft Corporation. All Rights Reserved.
# Licensed under the MIT License (MIT)

function Set-RsRestItemDataSource
{
    <#
        .SYNOPSIS
            This script updates data sources related to a catalog item from the Report Server.

        .DESCRIPTION
            This script updates data sources related to a catalog item from the Report Server.

        .PARAMETER RsItem
            Specify the location of the catalog item whose data sources will be updated.

        .PARAMETER RsItemType
            Specify the type of the $RsItem. Valid values are: Dataset, PowerBIReport or Report.

        .PARAMETER DataSources
            Specify the datasources which were initially fetched via Get-RsItemDataSource.

        .PARAMETER ReportPortalUri
            Specify the Report Portal URL to your SQL Server Reporting Services Instance.

        .PARAMETER RestApiVersion
            Specify the version of REST Endpoint to use. Valid values are: "v2.0".

        .PARAMETER Credential
            Specify the credentials to use when connecting to the Report Server.

        .PARAMETER WebSession
            Specify the session to be used when making calls to REST Endpoint.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPowerBIReport'
            $dataSources[0].DataModelDataSource.AuthType = 'Windows'
            $dataSources[0].DataModelDataSource.Username = 'domain\\user'
            $dataSources[0].DataModelDataSource.Secret = 'UserPassword'
            Set-RsRestItemDataSource -RsItem '/MyPowerBIReport' -RsItemType PowerBIReport -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable for Power BI Reports.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPowerBIReport'
            $dataSources[0].DataModelDataSource.AuthType = 'UsernamePassword' # UsernamePassword should be used when specifying SQL or Basic Credentials
            $dataSources[0].DataModelDataSource.Username = 'sqlSa'
            $dataSources[0].DataModelDataSource.Secret = 'sqlSaPassword'
            Set-RsRestItemDataSource -RsItem '/MyPowerBIReport' -RsItemType PowerBIReport -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable for Power BI Reports.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPowerBIReport'
            $dataSources[0].DataModelDataSource.AuthType = 'Key'
            $dataSources[0].DataModelDataSource.Secret = 'aASDBsdas12?asd2+asdajkda='
            Set-RsRestItemDataSource -RsItem '/MyPowerBIReport' -RsItemType PowerBIReport -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable for Power BI Reports.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPaginatedReport'
            $dataSources[0].CredentialRetrieval = 'Integrated'
            Set-RsRestItemDataSource -RsItem '/MyPaginatedReport' -RsItemType Report -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable to Paginated Reports.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPaginatedReport'
            $dataSources[0].CredentialRetrieval = 'Store'
            $dataSources[0].CredentialsInServer = New-RsRestCredentialsInServerObject -Username "domain\\username" -Password "userPassword" -WindowsCredentials
            Set-RsRestItemDataSource -RsItem '/MyPaginatedReport' -RsItemType Report -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable to Paginated Reports.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPaginatedReport'
            $dataSources[0].CredentialRetrieval = 'Prompt'
            $dataSources[0].CredentialsByUser = New-RsRestCredentialsByUserObject -PromptMessage "Please enter your credentials" -WindowsCredentials
            Set-RsRestItemDataSource -RsItem '/MyPaginatedReport' -RsItemType Report -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable to Paginated Reports.

        .EXAMPLE
            $dataSources = Get-RsRestItemDataSource -RsItem '/MyPaginatedReport'
            $dataSources[0].CredentialRetrieval = 'None'
            Set-RsRestItemDataSource -RsItem '/MyPaginatedReport' -RsItemType Report -DataSources $datasources

            Description
            -----------
            Updates data sources to the specified $dataSources object. This example is only applicable to Paginated Reports.

        .LINK
            https://docs.microsoft.com/en-us/power-bi/report-server/connect-data-source-apis-pre-oct-2020
    #>


    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
    param(
        [Parameter(Mandatory = $True)]
        [Alias('ItemPath','Path')]
        [string]
        $RsItem,

        [Parameter(Mandatory = $True)]
        [ValidateSet("PowerBIReport", "Report")]
        [string]
        $RsItemType,

        [Parameter(Mandatory = $True)]
        $DataSources,

        [string]
        $ReportPortalUri,

        [Alias('ApiVersion')]
        [ValidateSet("v2.0")]
        [string]
        $RestApiVersion = "v2.0",

        [Alias('ReportServerCredentials')]
        [System.Management.Automation.PSCredential]
        $Credential,

        [Microsoft.PowerShell.Commands.WebRequestSession]
        $WebSession
    )
    Begin
    {
        $WebSession = New-RsRestSessionHelper -BoundParameters $PSBoundParameters
        if ($null -ne $WebSession.Credentials -and $null -eq $Credential) {
            Write-Verbose "Using credentials from WebSession"
            $Credential = New-Object System.Management.Automation.PSCredential "$($WebSession.Credentials.UserName)@$($WebSession.Credentials.Domain)", $WebSession.Credentials.SecurePassword
        }
        $ReportPortalUri = Get-RsPortalUriHelper -WebSession $WebSession
        $dataSourcesUri = $ReportPortalUri + "api/$RestApiVersion/{0}(Path='{1}')/DataSources"
    }
    Process
    {
        try
        {
            # Basic DataSource Validation - Start
            foreach ($ds in $DataSources)
            {
                if ($ds.DataSourceSubType -eq 'DataModel')
                {
                    # DataModelDataSource.AuthType must be specified!
                    if ($ds.DataModelDataSource.AuthType -eq $null)
                    {
                        throw "DataModelDataSource.AuthType must be specified: $ds!"
                    }
                    elseif (($ds.DataModelDataSource.AuthType -LIKE 'Windows' -or
                            $ds.DataModelDataSource.AuthType -LIKE 'UsernamePassword' -or
                            $ds.DataModelDataSource.AuthType -LIKE 'Impersonate') -and
                            ($ds.DataModelDataSource.Username -eq $null -or
                            $ds.DataModelDataSource.Secret -eq $null))
                    {
                        # Username and Secret are required for Windows, UsernamePassword and Impersonate Auth Types
                        throw "Username and Secret must be specified for this AuthType: $ds!"
                    }
                    elseif ($ds.DataModelDataSource.AuthType -LIKE 'Key' -and
                            $ds.DataModelDataSource.Secret -eq $null)
                    {
                        # Secret is required for Key Auth Type
                        throw "Secret must be specified for this AuthType: $ds!"
                    }
                }
                elseif ($ds.DataSourceSubType -eq $null)
                {
                    if (!$ds.IsReference) {
                        # DataSourceType, ConnectionString and CredentialRetrieval must always be specified!
                        if ($ds.DataSourceType -eq $null -or
                            $ds.ConnectionString -eq $null -or
                            $ds.CredentialRetrieval -eq $null -or
                            !($ds.CredentialRetrieval -LIKE 'Integrated' -or
                            $ds.CredentialRetrieval -LIKE 'Store' -or
                            $ds.CredentialRetrieval -LIKE 'Prompt' -or
                            $ds.CredentialRetrieval -LIKE 'None'))
                        {
                            throw "Invalid data source specified: $ds!"
                        }
                        elseif ($ds.DataModelDataSource -ne $null)
                        {
                            # since this is an embedded data source for Paginated Report/Shared data set,
                            # you should not set any value to DataModelDataSource
                            throw "You cannot specify DataModelDataSource for this datasource: $ds!"
                        }

                        if ($ds.CredentialRetrieval -LIKE 'Store' -and $ds.CredentialsInServer -eq $null)
                        {
                            # CredentialsInServer must be specified for Store
                            throw "CredentialsInServer must be specified when CredentialRetrieval is set to Store: $ds!"
                        }
                        elseif ($ds.CredentialRetrieval -LIKE 'Prompt' -and $ds.CredentialsByUser -eq $null)
                        {
                            # CredentialsByUser must be specified for Prompt
                            throw "CredentialsByUser must be specified when CredentialRetrieval is set to Prompt: $ds!"
                        }
                    }
                }
                else
                {
                    throw "Unexpected data source subtype!"
                }
            }
            # Basic DataSource Validation - End

            $dataSourcesUri = [String]::Format($dataSourcesUri, $RsItemType + "s", $RsItem)

            # Converting $DataSources into array as PowerBIReport(...)/DataSources expects data sources
            # to be in an array in the request body. If $DataSources is already an array, this operation
            # combines $DataSources array with an empty array, so result is still an array.
            $dataSourcesArray = @($DataSources)

            # Depth needs to be specified otherwise whenever DataModelDataSource is present
            # Supported Auth Types will not be serialized correctly
            $payloadJson = ConvertTo-Json -InputObject $dataSourcesArray -Depth 3

            if ($RsItemType -eq "DataSet" -or $RsItemType -eq "Report")
            {
                $method = "PUT"
            }
            elseif ($RsItemType -eq "PowerBIReport")
            {
                $method = "PATCH"
            }
            else
            {
                throw "Invalid item type!"
            }

            if ($PSCmdlet.ShouldProcess($RsItem, "Update data sources"))
            {
                Write-Verbose "Updating data sources for $($RsItem)..."
                if ($Credential -ne $null)
                {
                    Invoke-WebRequest -Uri $dataSourcesUri -Method $method -Body ([System.Text.Encoding]::UTF8.GetBytes($payloadJson)) -ContentType "application/json" -WebSession $WebSession -Credential $Credential -UseBasicParsing -Verbose:$false | Out-Null
                }
                else
                {
                    Invoke-WebRequest -Uri $dataSourcesUri -Method $method -Body ([System.Text.Encoding]::UTF8.GetBytes($payloadJson)) -ContentType "application/json" -WebSession $WebSession -UseDefaultCredentials -UseBasicParsing -Verbose:$false | Out-Null
                }
                Write-Verbose "Data sources were updated successfully!"
            }
        }
        catch
        {
            throw (New-Object System.Exception("Failed to update data sources for '$RsItem': $($_.Exception.Message)", $_.Exception))
        }
    }
}