SSRS bulk change report datasource credentials

• ahanson 91097

  SSC Veteran

  Points: 274

  More actions

  June 18, 2012 at 4:41 am

  #262586

  Hi,

  I have the below script which i found online.

  CLS

  [string] $message = ""

  [string] $reportingServicesServer = ""

  [string] $startingFolder = "/"

  [string] $userName = ""

  [string] $password = ""

  [string] $uri = "http://{0}/ReportServer/ReportService2005.asmx?WSDL" -f $reportingServicesServer

  write-host $uri

  $reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingWebService"

  $reports = $reporting.ListChildren($startingFolder, $false) | Where-Object {$_.type -eq "Report"}

  foreach($report in $reports)

  {

  $message = "{0}: Updating UserId and Password for the following report: '{1}'." -f $(get-date -displayhint DateTime), $report.Path

  Write-Host $message

  $dataSource = $reporting.GetItemDataSources($report.Path)[0]

  $dataSource.Item.Prompt = $null

  $dataSource.Item.WindowsCredentials = $false

  $dataSource.Item.UserName = $userName

  $dataSource.Item.Password = $password

  $reporting.SetItemDataSources($report.Path, $dataSource)

  write-host $dataSource.Item.ConnectString

  $message = "{0}: Update completed." -f $(get-date -displayhint DateTime)

  Write-Host $message

  Write-Host ""

  }

  I have used this and it works for some reports but oher reports i get an error

  Exception calling "SetItemDataSources" with "2" argument(s): "The combination of values for the fields UserName and CredentialRetrieval are not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidElementCombinationException: The combination o

  f values for the fields UserName and CredentialRetrieval are not valid."

  I believe this is because some of the datasources have been set to no credentials required.

  I would like to change these, ideally without going through every report individually.

  Thanks in advance.

• ahanson 91097

  SSC Veteran

  Points: 274

  More actions

  June 18, 2012 at 10:03 am

  #1502317

  for anyone else who may have the same problem in the future, i wrote a very simple / messy web page (which could quite easily be adapted into a relatively simple little application) code below

  DataSource[] dataSources;

  DataSourceReference dsReference;

  DataSourceDefinition dsDefinition;

  string Path = string.Empty;

  string dsPath;

  string BatchID = string.Empty;

  ReportingService2005SoapClient rService = new ReportingService2005SoapClient();

  rService.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

  rService.ChannelFactory.Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;

  CatalogItem[] catalogItems;

  rService.ListChildren(Global.ReportPath, true, out catalogItems);

  foreach (CatalogItem item in catalogItems)

  {

  if (item.Type == ItemTypeEnum.Report || item.Type == ItemTypeEnum.LinkedReport)

  {

  Path = item.Path;

  Response.Write("Updating " + Path + "<br />");

  rService.GetItemDataSources(Path, out dataSources);

  if (dataSources.Length > 0)

  {

  if (dataSources.Count() > 1)

  {

  Response.Write("<h1>This one has multiple datasources</h1>");

  }

  if (dataSources[0].Item.GetType() == typeof(DataSourceDefinition))

  {

  dsDefinition = (DataSourceDefinition)dataSources[0].Item;

  }

  else

  {

  dsReference = (DataSourceReference)dataSources[0].Item;

  dsPath = dsReference.Reference;

  rService.GetDataSourceContents(dsPath, out dsDefinition);

  }

  dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Store;

  dsDefinition.UserName = "";

  dsDefinition.Password = "";

  rService.CreateBatch(out BatchID);

  BatchHeader h = new BatchHeader();

  h.BatchID = BatchID;

  rService.SetItemDataSources(h, Path, dataSources);

  rService.ExecuteBatch(h);

  }

  Response.Write("Updated " + p + "<br />");

  }