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

Question

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

Answer 1

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

Answer 2

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.