SSRS - Datasource - Update Credentials from Powershell or .rds file

Taylor, Damien 1 Reputation point
2021-05-05T10:09:55.227+00:00

Hi All,

Have written a powershell script to assist me in automatically deploying my SSRS scripts. It most works, but I really want to automate the creation of credentials against each datasource as well.

First thought was to update the .rds file dynamically but have been unable to find description or examples of the fields that I would need to add to my .rds file:

<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="MYDATASOURCENAME">
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>Data Source=MYSERVERNAME;Initial Catalog=MYDBNAME</ConnectString>
<UserName>tempusername</UserName>
<Password>temppassword</Password>
<Enabled>True</Enabled>
</ConnectionProperties>
</RptDataSource>

Was hoping to update the "TYPE OF CREDENTIALS" field to "Database Username and Password"
and then update the username and password fields under that
Impersonate = FALSE

However no matter what I do here it always deploys as "without credentials"

I have also tried to find syntax for using the Powershell "Write-RsFolderContent" command, but unable to find anything.

Anybody able to advise on a solution?

Thanks,
Damien

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,101 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 25,721 Reputation points
    2021-05-06T06:32:52.243+00:00

    There are several ways using PowerShell to change data source credentials for a report.

    Direct in report XML before you deploy
    https://www.mssqltips.com/sqlservertip/4429/sql-server-reporting-services-data-source-deployment-automation-with-powershell/

    Or using SSRS web service after deployment
    https://stackoverflow.com/questions/58709268/change-credential-retrieval-in-ssrs-with-powershell

    No comments

  2. Joyzhao-MSFT 15,361 Reputation points Microsoft Employee
    2021-05-06T09:11:45.533+00:00

    Hi @Taylor, Damien
    To be honest, PowerShell is beyond my abilities. The following content is for reference only, and may not be of significance to resolving the issue:
    If updating the connection string and the data source makes use of stored credentials, you need to provide the account password.

    To update a data source connection string:

    $dataSources[0].ConnectionString = 'data source=myCatalogServer;initial catalog=ReportServer;persist security info=False'  
    

    To change the data source credential type:

    $dataSources[0].DataModelDataSource.AuthType = 'Integrated'  
    

    To change the data source username/password:

    $dataSources[0].DataModelDataSource.Username = 'domain\user'  
    $dataSources[0].DataModelDataSource.Secret = 'password'  
    

    Best Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments