Snippet Name: Extract_SSRS_Username
Tags:
Created Date: 2025-06-16 13:12:44
Last Modified Date: 2025-05-31 10:32:18
Dependencies:
DBA Notes:
Sample Output:
Title: Extract SSRS Username
Author: user
i have some SSRS shared data sources that have been modified to have a new username and password. How can i extract the username from the data soruce. I am under the impression it is stored encrypted inside a chunk varbinary int he database
Author: ChatGPT
You're absolutely correct: in **SQL Server Reporting Services (SSRS)**, the username and password for a **Shared Data Source** are stored encrypted in the `Catalog` table (or `dbo.DataSource` depending on SSRS version) in the **ReportServer** database, typically inside a serialized XML blob within a `varbinary(max)` column (like `Content` or `Link`).
To **extract the username**, here???s how you can approach it:
---
### ???? **1. Identify Shared Data Sources in the `Catalog` Table**
You can find shared data sources in the `Catalog` table where `Type = 5`.
```sql
SELECT
ItemID,
Name,
Path,
CONVERT(xml, CAST(Content AS VARBINARY(MAX))) AS DataSourceXml
FROM
ReportServer.dbo.Catalog
WHERE
Type = 5 -- Shared Data Source
```
This XML contains a `DataSourceDefinition` with the login name ??? but **not the password** (it???s encrypted).
---
### ???? **2. Extract Username from the XML**
Use XML parsing to extract the `<UserName>` node:
```sql
WITH DataSourceContent AS (
SELECT
Name,
Path,
CAST(Content AS VARBINARY(MAX)) AS BinaryContent
FROM
ReportServer.dbo.Catalog
WHERE
Type = 5
)
SELECT
Name,
Path,
DataSourceXml.value('(//UserName)&#91;1]', 'NVARCHAR(256)') AS Username
FROM (
SELECT
Name,
Path,
CONVERT(XML, BinaryContent) AS DataSourceXml
FROM
DataSourceContent
) AS Extracted
```
> ???? If you get an error when casting to XML, it's likely the `Content` is compressed or uses a serialization format that only SSRS understands ??? in which case, you can extract it via the **RS.EXE tool** or **PowerShell** (see below).
---
### ???? **Alternative via PowerShell + ReportService2010.asmx (or v13 API)**
You can programmatically extract this info with PowerShell using the ReportService SOAP API:
```powershell
# Load SSRS Web Service Proxy
$reportServerUri = "http://your-report-server/reportserver/ReportService2010.asmx?wsdl"
$ssrs = New-WebServiceProxy -Uri $reportServerUri -UseDefaultCredential
# Get all shared data sources
$dataSources = $ssrs.ListChildren("/", $true) | Where-Object { $_.TypeName -eq "DataSource" }
foreach ($ds in $dataSources) {
$definition = $ssrs.GetDataSourceContents($ds.Path)
Write-Host "DataSource: $($ds.Path)"
Write-Host " Username: $($definition.CredentialRetrieval -eq 'Store' ? $definition.UserName : 'Not stored')"
}
```
This gives you the username if it's **stored credentials** (not Windows integrated or prompt).
---
### ???? Notes
- The password is **never visible** ??? it's encrypted and cannot be decrypted even from the database directly.
- If someone manually updated the data source via the Report Manager UI or script, this method still applies.
- Ensure you're querying the correct `ReportServer` database if you have multiple instances or versions.
---
Would you like help generating the same results via PowerShell with a saved list of shared data sources and usernames?
Author: user
that is incorrect. that is the static location whent eh data soruce was created. it might point to say a dev server, witha dev login. that is NOT the modified shared data source
Author: ChatGPT
Thanks for the clarification ??? you're right, and this is an important distinction.
What you're referring to is when a **shared data source was modified**, for example, via the SSRS web portal (or `SetDataSourceContents`) ??? the changes, especially credentials, are **stored separately** from the original `Catalog.Content` XML. The XML in `Catalog.Content` is only the **original deployed definition** and **does not reflect changes** made afterward.
Let me walk you through the **correct method to retrieve the actual, current** username stored in a shared data source.
---
### ??? Where SSRS Stores the *Current* Data Source Info
When you modify a shared data source (especially credentials), the actual values are stored in:
- `dbo.DataSource` (older SSRS)
- or more reliably in **linked** records between:
- `dbo.Catalog` (shared data source item metadata)
- `dbo.DataSource` (connection info, with encrypted credential XML)
- `dbo.DataSourceDefinition` (on some SSRS versions, depending on how credentials were applied)
The username and password are **stored in encrypted binary blobs**, typically in a `varbinary(max)` column like `Data` in `DataSource`.
---
### ??????? Script to Pull the *Actual Modified Username*
Unfortunately, the username is inside encrypted binary in `Catalog.Content` or `DataSource.Data`. You **cannot decrypt this in T-SQL alone**.
But you *can* retrieve the **current effective values** using the SSRS **SOAP API**, which reflects runtime data source configuration.
---
### ??? Correct Way: Use ReportService2010 API to get actual live username
Here???s how to **accurately extract the currently saved username** (even if it was modified later) using PowerShell:
```powershell
# SSRS endpoint - change to match your server
$reportServerUri = "http://your-server/reportserver/ReportService2010.asmx?wsdl"
$ssrs = New-WebServiceProxy -Uri $reportServerUri -UseDefaultCredential
# List all shared data sources
$dataSources = $ssrs.ListChildren("/", $true) | Where-Object { $_.TypeName -eq "DataSource" }
foreach ($ds in $dataSources) {
try {
$def = $ssrs.GetDataSourceContents($ds.Path)
Write-Host "`nData Source: $($ds.Path)"
Write-Host " CredentialRetrieval: $($def.CredentialRetrieval)"
if ($def.CredentialRetrieval -eq 'Store') {
Write-Host " Username: $($def.UserName)"
} else {
Write-Host " Username: <not stored>"
}
} catch {
Write-Warning "Failed to retrieve info for $($ds.Path): $_"
}
}
```
#### Output Example:
```
Data Source: /Shared Data Sources/MyDataSource
CredentialRetrieval: Store
Username: report_user_prod
```
This reflects the **actual credentials being used**, regardless of what was originally deployed.
---
### ???? Recap
| Method | What You Get | Limitation |
|--------|--------------|------------|
| T-SQL `Catalog.Content` | Original deployed data source XML | Doesn't reflect later changes |
| SSRS Web Portal | Current, but manual | Not scriptable |
| `ReportService2010.asmx` | ??? Current, stored username | Password remains encrypted (as expected) |
---
Would you like a version of this PowerShell that writes the results to a `.csv` or SQL table? Or help wiring this into a credential audit?