datasource in embeded datasource

No built-in SSRS UI tool exists to bulk update credentials for embedded data sources. That’s why you’re forced to edit each report manually.

But yes — you can automate this using scripts. These are the correct and commonly used solutions:

Best Solution: Convert to Shared Data Source (Recommended)

If possible, create one Shared Data Source and point all reports to it.

Why this is best

• One place to store username/password

• Change credentials once ? affects all reports

• No future maintenance headache

You can update reports using:

• Report Manager

• PowerShell / RS.exe script

PowerShell / RS.exe Script (For Existing Embedded Data Sources)

You can bulk update credentials programmatically.

High-level idea

• Loop through all reports

• Read embedded data sources

• Update credentials (username/password)

• Save back to Report Server

Example (RS.exe script):

Public Sub Main()
    Dim reports = rs.ListChildren("/", True)

    For Each item In reports
        If item.Type = ItemTypeEnum.Report Then
            Dim ds = rs.GetItemDataSources(item.Path)

            For i As Integer = 0 To ds.Length - 1
                Dim d As DataSource = ds(i)
                Dim def As DataSourceDefinition = d.Item

                def.CredentialRetrieval = CredentialRetrievalEnum.Store
                def.UserName = "db_user"
                def.Password = "db_password"
                def.Enabled = True

                rs.SetItemDataSources(item.Path, ds)
            Next
        End If
    Next
End Sub

Run using:

rs.exe -i UpdateCredentials.rss -s http://server/ReportServer

PowerShell Alternative

Using SSRS Web Service (ReportService2010):

• Faster

• More flexible

• Scriptable and schedulable

Used widely in production environments.

Important Notes

• Works for SQL Server Express with Advanced Services (SSRS)

• Passwords are stored securely in SSRS database

• Backup before running scripts

• Test on few reports first

Summary

• No UI tool for bulk update

• Script is the only practical solution

• Best practice: Shared Data Source

• Use RS.exe / PowerShell for automation