Update DataSource and Subscription Password for SSRS

Tagged: 

This topic contains 5 replies, has 1 voice, and was last updated by  Kawika Moss 3 weeks, 4 days ago.

  • Author
    Posts
  • #78328

    Kawika Moss
    Participant

    When you have to update your AD credentials, you then have to go in and update your password for all the datasources and subscriptions, in SSRS, that are owned by you.

    I'm trying to automate this with a PS script. What I have so far is...

    script to come...

  • #78329

    Kawika Moss
    Participant

    Line 64 is where I'm getting the error...

    Cannot find an overload for "GetSubscriptionProperties" and the argument count: "6".
    At line:64 char:11
    + $details = $reporting.GetSubscriptionProperties($Subscription.Subscrip ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

    $uri = "http://localhost//ReportServer_KESSEL/ReportService2010.asmx?WSDL"
    $password = 'newPassword'
    $username = 'jeremy.teran'
    $UpdatePassword = $true
    
    
    $reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingService2010" -class Reporting
    $reporting.url = $uri	#Makes sure URL is set correctly after call; sometimes this flips to a default URL
    
    $Description = new-object -TypeName System.String -argumentList ("")
    $Status = new-object -TypeName System.String -argumentList ("")
    $EventType = new-object -TypeName System.String -argumentList ("")
    $MatchData = new-object -TypeName System.String -argumentList ("")
    $details = new-object -TypeName System.String -argumentList ("")
    
    ##Initialize Variable Types required for GetSubscription call
    $ExtensionSettings = new-object -TypeName ReportingService2010.ExtensionSettings
    $ExtensionRow = new-object -TypeName ReportingService2010.ExtensionSettings
    $DataRetrievalPlan = new-object -TypeName ReportingService2010.DataRetrievalPlan
    $Active =  new-object -TypeName ReportingService2010.ActiveState
    $ParametersValue = new-object -TypeName ReportingService2010.ParameterValue
    $subscriptions= new-object -TypeName ReportingService2010.Subscription
    $subscription= new-object -TypeName ReportingService2010.Subscription
    $ExtensionPassword = new-object -TypeName ReportingService2010.ParameterValue
    $Parameters = new-object -TypeName ReportingService2010.ParameterValue
    		
    # $reports = $reporting.listchildren("/", $true) | Where-Object {$_.TypeName -eq "Report"}
    # $reports
    
    $ExtensionPassword.Name = "PASSWORD"
    $ExtensionPassword.value = $password
    
    #Write-host $dataSource.Item.UserName
    
    
    #$subscriptions += $rs2010.ListSubscriptions("/"); # use "/" for default native mode site 
    
    
    foreach ($report in $reports){
    
        $subscriptions = $reporting.ListSubscriptions($report.Path)
        #$subscriptions
    
    	foreach ($subscription in $subscriptions){
    	    $ReportSubscriptionID = [String]$subscription.SubscriptionID	#Sets a variable to the SubscriptionID Value
            #$ReportSubscriptionID
            If ($ReportSubscriptionID){
    		    ##Looks through the extensions stored within the subscription for Username/Password
    		    foreach ($ParameterValue in $subscription.DeliverySettings.ParameterValues){
    			    If ($ParameterValue.name -eq "USERNAME"){	#Finds the username parameter                    
    				    If ($ParameterValue.value -eq $username){	#Checks if this is the username we are looking for
    					    Write-Host " Subscription Name: " $subscription.Report
    					    #Write-Host "   SubscriptionID: " $Subscription.SubscriptionID
    					    Write-Host "   DeliveryExtension: " $subscription.DeliverySettings.Extension
    					    write-host "      Field: " $ParameterValue.name
    					    #write-host "      Label: " $ParameterValue.label		##Not used
    					    write-host "       Value: " $ParameterValue.value
    
                            #Sets the new password
    					    $PasswordUpdated = $false
    					    If ($UpdatePassword -eq $true){
    						    $details = ""
    						    #Gets the subscription Properties prior to updating the password
    						    $details = $reporting.GetSubscriptionProperties($Subscription.SubscriptionID, [REF]$ExtensionSettings, [REF]$Description, [REF]$EventType, [REF]$MatchData, [REF]$ParametersValue)
    								
    						    $i=0	#initialize counter variable
    						    foreach ($ExtensionRow in $ExtensionSettings.ParameterValues){		#Pulls all the ExtensionSettings
    							    write-host "       ExtensionRow: " $ExtensionRow.name
    							    write-host "       ExtensionRow.Values: " $ExtensionRow.value
    							    If ($ExtensionRow.name -eq "PASSWORD"){	#Checks to see if PASSWORD is unencrypted and available to pull
                                        Write-Host "updating password..."
    							     #$ExtensionRow.value = $password	#Sets the value
    							     Write-Host "!!   WARNING: Unencrypted password retrieved" -ForegroundColor "Red"
    							     $PasswordUpdated = $true
    							     break	#Exits for loop -- password updated, now to commit change
    							   }
    							    $i++	#Increments counter
    							    If ($ExtensionSettings.ParameterValues.count -eq $i){	#Determines if this is the final iteration and whether the password field was found
    								    $ExtensionSettings.ParameterValues += $ExtensionPassword
    								    $PasswordUpdated = $true
    								    break	#Exits for loop -- member group added and set
    								    #}
    							    }
    						    }
    
    						     # #Sets the Subscription settings -- with the new Password
    						     # If ($PasswordUpdated -eq $true){	#Password has actually been updated
    							 #     $details = $reporting.SetSubscriptionProperties($ReportSubscriptionID, $ExtensionSettings, $Description, $EventType, $MatchData, $ParametersValue)
    						     # }
    						     # ELSE{
    							 #     Write-Host "     ERROR: PASSWORD NOT UPDATED" -ForegroundColor "Red"
    						     # }
    					    }					    
    				    }
    			    }
    		    }
    	    }	    
        }
    }
    
  • #78331

    Kawika Moss
    Participant

    At this point I have only gotten to the subscription piece, and I am stuck for now. Any guidance on how to accomplish this would be great!!

    Thanks!

  • #78547

    Kawika Moss
    Participant

    I changed the variables that are used in the GetSubscriptionProperties call to:

    $ExtensionSettings = ''
    $ExtensionRow = ''
    $Description = ''
    $Active = ''
    $Status = ''
    $EventType = ''
    $MatchData = ''
    $Parameters = ''
    

    And then called the method like:

    $details = $reporting.GetSubscriptionProperties($Subscription.SubscriptionID, [REF]$ExtensionSettings, [REF]$Description, [REF]$Active, [REF]$Status, [REF]$EventType, [REF]$MatchData, [REF]$ParametersValue)
    

    and I got this error...

    Exception calling "GetSubscriptionProperties" with "8" argument(s): "Cannot convert the "System.Management.Automation.PSReference`1[System.String]" value of type 
    "System.Management.Automation.PSReference`1[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" to type "ReportingService2010.ExtensionSettings"."
    At C:\Users\kawika.moss\Documents\PowershellScripting\TestUpdateSubscriptionPassword.ps1:74 char:11
    +                             $details = $reporting.GetSubscriptionProperties($Subscription.Subscrip ...
    +    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : PSInvalidCastException
    
  • #78551

    Kawika Moss
    Participant

    I've also tried setting the initialized variables as:

    ##Initialize Variable Types required for GetSubscription call
    $ExtensionSettings = new-object -TypeName ReportingService2010.ExtensionSettings
    $ExtensionRow = new-object -TypeName ReportingService2010.ExtensionSettings
    $Description = new-object -TypeName System.String -argumentList ("")
    $Active =  new-object -TypeName ReportingService2010.ActiveState
    $Status = new-object -TypeName System.String -argumentList ("")
    $EventType = new-object -TypeName System.String -argumentList ("")
    $MatchData = new-object -TypeName System.String -argumentList ("")
    $Parameters = new-object -TypeName ReportingService2010.ParameterValue
    

    and the error is:

    Exception calling "GetSubscriptionProperties" with "8" argument(s): "Cannot convert the "System.Management.Automation.PSReference`1[ReportingService2010.ExtensionSettings]" value of type 
    "System.Management.Automation.PSReference`1[[ReportingService2010.ExtensionSettings, dnxukfbc, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null]]" to type 
    "ReportingService2010.ExtensionSettings"."
    At C:\Users\kawika.moss\Documents\PowershellScripting\TestUpdateSubscriptionPassword.ps1:76 char:11
    +                             $details = $reporting.GetSubscriptionProperties($Subscription.Subscrip ...
    +    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : PSInvalidCastException
    
  • #78559

    Kawika Moss
    Participant

    Tried GetType() on the $ExtensionSettings object and got:

    IsPublic IsSerial Name                                     BaseType                                                                                                                           
    -------- -------- ----                                     --------                                                                                                                           
    False    False    PSReference`1                            System.Management.Automation.PSReference 

You must be logged in to reply to this topic.