PowerShell:Updating Password of SQL Reporting Service Datasource

Posted: June 20, 2015 in PowerShell
Tags: ,
2

In an enterprise organization changing password for service account is a pain. For security reason, you have to change password periodically.  The below PowerShell script will help to get data source under given user account and will update the password for the same.

$ReportingServer = "localhost"
$userName = "domain\UserName"
$password = "password" #UpdatedPassword
$uri = "http://{0}/ReportServer/ReportService2010.asmx?WSDL" -f $ReportingServer

$reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"
$DataSources = $reporting.ListChildren('/', $false) | Where-Object {$_.TypeName -eq "DataSource"}

foreach($Object in $DataSources) {
  $dataSource = $reporting.GetDataSourceContents($Object.path)[0]
  $Verify = $Object.UserName
  if ($Verify -eq $userName) {
    $dataSource.WindowsCredentials = $true
    $dataSource.UserName = $userName
    $dataSource.Password = $password
    $reporting.SetDataSourceContents($Object.Path, $dataSource)
  }
}

I have tested this code with SQL 2012 Reporting Services.

Comments
  1. kiran patel says:
    March 22, 2016 at 9:10 am

    Thanks for sharing the script…its really helpful in my case..

    Like

    Reply
  2. kiran patel says:
    March 22, 2016 at 1:31 pm

    I am having issue with getting username associated with datasource so I changed:

    $Verify = $dataSource.UserName instead of $Verify = $Object.UserName as per your version

    My Version of script:

    $ReportingServer = “localhost”
    $UserName = “UserName”
    $Password = “Password”
    $ReportingServerURI = “http://{0}/ReportServer/ReportService2010.asmx?WSDL” -f $ReportingServer

    $reporting = New-WebServiceProxy -uri $ReportingServerURI -UseDefaultCredential -namespace “ReportingWebService”
    $DataSources = $reporting.ListChildren(‘/’, $false) | Where-Object {$_.TypeName -eq “DataSource”}

    foreach($Object in $DataSources) {
    $dataSource = $reporting.GetDataSourceContents($Object.path)[0]
    Write-Host ” DataSource:” $Object.path
    Write-Host ” DataSource UserName:” $dataSource.UserName
    Write-Host ” Given UserName:” $UserName

    $Verify = $dataSource.UserName
    if ($Verify -eq $UserName) {
    $dataSource.WindowsCredentials = $true
    $dataSource.UserName = $UserName
    $dataSource.Password = $Password
    $reporting.SetDataSourceContents($Object.Path, $dataSource)
    }
    else
    {
    Write-Host ” UserName” $UserName “is not associated with DataSource :” $Object.path
    }

    Write-Host ” **************************************************************”
    }

    Like

    Reply

Leave a comment