question

TaylorDamien-8003 avatar image
0 Votes"
TaylorDamien-8003 asked Joyzhao-MSFT edited

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @TaylorDamien-8003
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.