PowerShell Learning – SSRS Data Source Mystery - Part 3 of 4

Here is a continuation blog on things I learned to do with PowerShell to fix an issue in my day-to-day task list. This one may not be daily, but I think it might help someone out there.

Part 1 of 4

Part 2 of 4

Issue:

A couple weeks ago we had a group of users ask for help with reports that they could no longer run.

What?! These reports have been running for months without any issues and now there is a problem?! Well, I logged in to SSRS and proceeded to the report(s). And…sure enough the report had an error loading the data source.

Let’s check the data source!!

Well, wouldn’t you know it; there is a username of an individual who is no longer with the company and that account has now been disabled as per security policy.

This is not a standard practice for our data sources so it was an anomaly that has occurred.

After fixing it, I wondered if we have any other “anomalies” that might be sneaking around our SSRS environment?

Off to Google I go to see how easy or not it is to find all the usernames being used by SSRS data sources.

Ha!!! Not so easy as I would think it would have been. I found a possibility that uses the built in RS.exe and some C# code.

Resolution:

Reference to RS.exe example: SQL Server Central Forum

But then I thought PowerShell has been a life saver for other DBA related tasks, so I turned to that tool and my thinking was again this should be easy.

After asking on Slack and getting a couple people that had advise, Friedrich Weinmann (b | t) threw out some code that I still am in awww of and not 100% sure what it does.

$WebServiceUrl = 'http://SSRS_ServerName.domain.com/ReportServer/ReportService2005.asmx?WSDL';
$ssrs = New-WebServiceProxy -uri $WebServiceUrl -UseDefaultCredential;
$items = $ssrs.ListChildren("/",$true);
$property_username = @{
    n = "UserName";
    e = {
        if ($_.Type -eq 'datasource') {$ssrs.GetDataSourceContents($_.Path).UserName}
    }
}
$items | Where-Object Type -EQ "Datasource" | Select-Object Name, Path, $property_username | Export-Csv C:\Temp\SSRSDataSourceUserNames_SSRS_ServerName.txt -Append

$WebServiceUrl = 'http://SSRS_ServerName.domain.com/ReportServer/ReportService2005.asmx?WSDL';

$ssrs = New-WebServiceProxy -uri $WebServiceUrl -UseDefaultCredential;

$items = $ssrs.ListChildren("/",$true);

$property_username = @{

n = "UserName";

e = {

if ($_.Type -eq 'datasource') {$ssrs.GetDataSourceContents($_.Path).UserName}

}

$items | Where-Object Type -EQ "Datasource" | Select-Object Name, Path, $property_username | Export-Csv C:\Temp\SSRSDataSourceUserNames_SSRS_ServerName.txt -Append

That code was much cleaner than the 30 lines I had started hacking together.

So now I have the ability to do an audit on all Data Sources on any SSRS server in my environment.

Hooray!!!

Off to the next one…

Notes:

You may have a different URI depending on your SSRS version. See link for more detail.

PowerShell Learning – SSRS Data Source Mystery – Part 3 of 42 min read

Previous Posts:

Issue:

Resolution:

Notes:

Related

One Reply to “PowerShell Learning – SSRS Data Source Mystery – Part 3 of 42 min read”

Leave a Reply Cancel reply