June 6, 2012 at 11:15 am
how to find out actual values (server, database) in ConnectionString column which is 'image' type ?
select ConnectionString,* from ReportServer..DataSource
June 6, 2012 at 11:25 am
try to see if it's convertable from binary to text:
i don't have any reportingservices that are touchable to test:
select
CONVERT(NVARCHAR(max),CONVERT(VARBINARY(max),ConnectionString)) As ConversionAttempt,
ConnectionString,*
from ReportServer..DataSource
Lowell
June 6, 2012 at 11:49 am
Tried to convert it to varchar (chinese output) and XML (parsing error) - both don't work.
July 20, 2012 at 12:52 pm
I'm also trying to do this (with the business goal of being able to generate a report of reports, including both connection string and username for every report, for auditing purposes).
I deal with SSRS reports that use a variety of authentication methods - some use shared data sources, some use Windows Authentication with a connection string, and some a "custom data source" or "embedded data source" to "securely" store the username, password, and connection string with the report itself on the SSRS server. So far, I've determined that these end up stored in different ways, which is going to make an audit a very interesting exercise.
An analysis of a small sample of my set of connection strings shows a very odd distribution, with definite spikes and troughs through the entire ASCII dataset range, but with every character represented; either I copied and pasted wrong (offset by a nibble, perhaps), or this is using some form of encryption or compression. Were the sample size larger, I'd think it was not and even enough distribution for me to expect a highly secure form of encryption.
Seeing that there are no duplicate connection strings argues for encryption with a different IV (Initialization Vector) or password for each row, since I know that some of ours should be identical.
Some references I found that don't have ready to run code (I don't think) are: Social.MSDN: Programatically Copying a Report with Embedded DataSource gives error while running[/url]
Social.MSDN: How to retrieve referenced data source information using the web service interface?[/url]
Here's various bits and bobs of code I've found so far relating to any kind of report about SSRS reports, none of which works on most of the reports I have ("custom data source" or "embedded data source"), but which I hope that we can use to find a community solution to reporting on SSRS reports.
The most promising uses rs.exe from the command line to run, but can actually return username for shared data sources. Like everything else I've found, it fails complete on "custom data source" or "embedded data source".
' Put this code into FileName.rss and then
' execute with: rs -i FileName.rss -s ServerName/ReportServer
' Originally from http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/a2a9ebe1-0417-46bf-8589-ae4e4a16181c in a post by Igor Gelin
' Additional fields from http://msdn.microsoft.com/en-us/library/reportservice2005.datasourcedefinition.aspx added.
' This appears to ONLY work for Shared data sources, not Custom data source or embedded data source (i.e. per-report data sources)
Public Sub Main()
Dim items As CatalogItem() = Nothing
Dim dataSource As DataSourceDefinition
Dim count as Integer = 0
Try
items = rs.ListChildren("/", True)
Console.WriteLine("Datasources:")
Console.WriteLine()
For Each catalogItem as CatalogItem in items
if (catalogItem.Type = ItemTypeEnum.DataSource)
Console.WriteLine(catalogItem.Path)
dataSource = rs.GetDataSourceContents(catalogItem.Path)
If Not (dataSource Is Nothing) Then
Console.WriteLine(" Connection String: {0}", dataSource.ConnectString)
Console.WriteLine(" Extension name: {0}", dataSource.Extension)
Console.WriteLine(" Credential retrieval: {0}", dataSource.CredentialRetrieval)
Console.WriteLine(" Windows credentials: {0}", dataSource.WindowsCredentials)
Console.WriteLine(" Username: {0}", dataSource.UserName)
Console.WriteLine(" Password: {0}", dataSource.Password)
Console.WriteLine(" Enabled: {0}", dataSource.Enabled)
Console.WriteLine(" EnabledSpecified: {0}", dataSource.EnabledSpecified)
Console.WriteLine(" ImpersonateUser: {0}", dataSource.ImpersonateUser)
Console.WriteLine(" ImpersonateUserSpecified: {0}", dataSource.ImpersonateUserSpecified)
Console.WriteLine(" OriginalConnectStringExpressionBased: {0}", dataSource.OriginalConnectStringExpressionBased)
Console.WriteLine(" Prompt: {0}", dataSource.Prompt)
Console.WriteLine(" UseOriginalConnectString: {0}", dataSource.UseOriginalConnectString)
Console.WriteLine("===================================")
End If
count = count + 1
end if
Next catalogItem
Console.WriteLine()
Console.WriteLine("Total {0} datasources", count)
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub
The second most promising item I found is a PowerShell script, which I HAVE NOT TESTED, and which CHANGES PASSWORDS!!!!
# Originally from http://gallery.technet.microsoft.com/scriptcenter/80d6ae2f-1968-43f0-b2f6-bebe89ecd3be
#OBTAIN CMD LINE INFO: "get-help .\GetSSRS.ps1"
<#
.SYNOPSIS
Update Passwords used within SQL Reporting Services
.DESCRIPTION
Scans and updates Passwords in SSRS via the Web service for all:
-Reports
-Datasources
-Subscriptions
Limitations and Known Issues:
Only works for SSRS 2008/R2 at this time
Not tested with SSRS in SharePont Integration mode
Sometimes pauses at "Searching for <useraccount> for a few minutes - be patient
If run fails, next run receives error: "Transcription has already been started." -- Can ignore: no impact
.EXAMPLE
(Run remote)
View: ssrs.ps1 -computer pqodtgpssrs01 -username domain\user
Update: ssrs.ps1 -computer pqodtgpssrs01 -username domain\user -password actualPassword
OR (Run local)
View: ssrs.ps1 -username domain\user
Update: ssrs.ps1 -computer pqodtgpssrs01 -username domain\user
#>
## Input##
Param([string]$computer=".",[parameter(mandatory=$true)][string]$username,[string]$password)
##Performs a quick parameter check
If ($computer -eq "."){
write-host "No SSRS Server specified -- checking localhost"
}
##Checks for null password to determine if updates are to occur
If ($password -eq ""){
[system.Boolean]$UpdatePassword=$false
write-host "Performing Check -- Password will not be updated"
}
ELSE{
[system.Boolean]$UpdatePassword=$true
write-host "Password specified, performing Update"
}
############# Functions ##################
#function main([string]$computer,[string]$username,[string]$password,[string]$UpdatePassword){
function main{
## Default Variables ##
$i = new-object -TypeName System.Int32
$j = new-object -TypeName System.Int32
$Found = new-object -TypeName System.Boolean
$i=0 #initialize Counter
$j=0 #initialize Counter
$Found = $false #initialize matching
$Application = @()
$SSRSURLString = @()
$foundURL = new-object -TypeName System.String -argumentList ("")
################################################################
# Starts log File
$dt = Get-Date -format "yyyyMMdd_hhmm"
$File ="SSRS_LOG_" + $dt + ".txt"
start-transcript -Path $File -Append #Starts logging
write-host "Server " -NoNewline; write-host $computer -ForegroundColor "Yellow"
write-host "Searching for $username"
################################################################
# Gets Web Service Instance/URL
## Get instance name
$ssrsinstance = Get-WmiObject -ComputerName $computer -class __namespace -namespace "root\Microsoft\SqlServer\ReportServer" -ErrorAction SilentlyContinue
If ($ssrsinstance -eq $null){
Write-Host " ERROR: SSRS Not found" -ForegroundColor "Red"
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
Exit -1
}
foreach ($result in $ssrsinstance){
write-host "SSRS Instance: " $result.name
$ssrsURL = Get-WmiObject -ComputerName $computer -class MSReportServer_ConfigurationSetting -namespace "root\Microsoft\SqlServer\ReportServer\$($result.name)\v10\admin"
$urls = $ssrsURL.ListReservedUrls() | Where-Object {$_.Application -eq "ReportManager"}
##List of ReportServerWebService and ReportManager
$Application = @($urls.Application) | select-string -pattern "Report" -simplematch
foreach ($item in $Application){
$i++ #increments counter for each item found
#write-host "Application " $item
If ($item -match "WebService"){ #Finds the application that contains the Web Service reference
write-host " Application " $item
$Found = $true
#write-host "Found! " $i
break #exits for loop so counter stops
}
}
#write-host "i " $i
$SSRSURLString = @($urls.urlString) | select-string -pattern "http" -simplematch
#Finds matching URL
If ($Found -eq $true){ #Checks to make sure that the web service was found before matching the URL
foreach ($item in $SSRSURLString){
#write-host "URL " $item
$j++
#If ($j -eq $i){
#Check if URL doesn't contain + character. If not, then check to see if this is labeled as Web Service
If ($item -match "\+"){
#write-host " Invalid URL" $foundURL
}
ELSE{
#Check if listed as Web Service
$foundURL = $item
#$foundURL = $foundURL -replace("\+", $computer)
#write-host "Replaced String: " $foundURL
#Write-Host "Applications " $Application[$j]
If ($Application[$j-1] -match "WebService"){
write-host " URL" $foundURL
break #exits for loop so counter stops
}
}
#write-host "j " $j " i " $i
}
}
#write-host "j " $j
################################################################
# Starts Web Service Call
#Gets SSRS Version
$SQL = Get-WmiObject -computer $computer -class MSReportServer_Instance -namespace "root\Microsoft\SqlServer\ReportServer\$($result.name)\v10"
Switch ($SQL.Version)
{
#SSRS 2008 version number starts with 10.0.
{$_ -match "10.0."} {
#$uri = "https://$($computer)/ReportServer/ReportService2005.asmx?WSDL"
$uri = "$($foundURL)/ReportServer/ReportService2005.asmx?WSDL"
#$uri = "$($foundURL)/ReportServer/ReportingService2005.asmx?WSDL"
Write-Host " SQL 2008 " $SQL.Version
$SQLVersion = "2008"
SQL2008($SQLVersion)
}
#SSRS 2008R2 version number starts with 10.50.
{$_ -match "10.50."} {
#$uri = "https://$($computer)/ReportServer/ReportService2010.asmx?WSDL"
$uri = "$($foundURL)/ReportServer/ReportService2010.asmx?WSDL"
Write-Host " SQL 2008 R2 " $SQL.Version
$SQLVersion = "2008R2"
SQL2008($SQLVersion)
}
default {
Write-Host " ERROR: Undefined SQL Version " $SQL.Version -ForegroundColor "Red"
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
exit -1
}
}
}
#Cleanup
Remove-Variable i
Remove-Variable j
Remove-Variable found
Remove-Variable Application
Remove-Variable SSRSURLString
Remove-Variable foundURL
Remove-Variable uri
Remove-Variable SQLVersion
Remove-Variable dt
Remove-Variable ssrsinstance
Remove-Variable ssrsURL
Remove-Variable urls
Remove-Variable result
Remove-Variable item
Remove-Variable SQL
}
function SQL2008([String]$SQLVersion){
If ($UpdatePassword -eq $true){
Write-Host " Updating Passwords..."
Write-Host
}
Else{
Write-Host " Looking for usage..."
Write-Host
}
$Description = new-object -TypeName System.String -argumentList ("")
$Status = new-object -TypeName System.String -argumentList ("")
$EventType = new-object -TypeName System.String -argumentList ("")
$MatchData = new-object -TypeName System.String -argumentList ("")
$details = new-object -TypeName System.String -argumentList ("")
#$reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportService2005" -class Reporting
#$user = Get-Credential
$user = [System.Net.CredentialCache]::DefaultCredentials
##Determines which query to use
Switch ($SQLVersion)
{
#SSRS 2008 methods
2008{
##Creates the Web Service
$reporting = New-WebServiceProxy -Uri $uri -UseDefaultCredential -namespace "ReportService2005" -class Reporting
$reporting.url = $uri #Makes sure URL is set correctly after call; sometimes this flips to a default URL
#$reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportService2005" -class Reporting
#$reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingService2005" -class Reporting
##Initialize Variable Types required for GetSubscription call
$ExtensionSettings = new-object -TypeName ReportService2005.ExtensionSettings
#$ExtensionSettings = new-object -TypeName ReportingService2005.ExtensionSettings
$ExtensionRow = new-object -TypeName ReportService2005.ExtensionSettings
#$ExtensionRow = new-object -TypeName ReportingService2005.ExtensionSettings
$DataRetrievalPlan = new-object -TypeName ReportService2005.DataRetrievalPlan
#$DataRetrievalPlan = new-object -TypeName ReportingService2005.DataRetrievalPlan
$Active = new-object -TypeName ReportService2005.ActiveState
#$Active = new-object -TypeName ReportingService2005.ActiveState
$ParametersValue = new-object -TypeName ReportService2005.ParameterValue
#$ParametersValue = new-object -TypeName ReportingService2005.ParameterValue
$subscriptions= new-object -TypeName ReportService2005.Subscription
#$subscriptions= new-object -TypeName ReportingService2005.Subscription
$subscription= new-object -TypeName ReportService2005.Subscription
#$subscription= new-object -TypeName ReportingService2005.Subscription
$ExtensionPassword = new-object -TypeName ReportService2005.ParameterValue
#$ExtensionPassword = new-object -TypeName ReportingService2005.ParameterValue
$Parameters = new-object -TypeName ReportService2005.ParameterValue
#$Parameters = new-object -TypeName ReportingService2005.ParameterValue
$reports = $reporting.listchildren("/", $true) | Where-Object {$_.Type -eq "Report"}
}
#SSRS 2008R2 methods
2008R2{
##Creates the Web Service
$reporting = New-WebServiceProxy -uri $uri -UseDefaultCredential -namespace "ReportingService2010" -class Reporting
$reporting.url = $uri #Makes sure URL is set correctly after call; sometimes this flips to a default URL
##Initialize Variable Types required for GetSubscription call
$ExtensionSettings = new-object -TypeName ReportingService2010.ExtensionSettings
$ExtensionRow = new-object -TypeName ReportingService2010.ExtensionSettings
$DataRetrievalPlan = new-object -TypeName ReportingService2010.DataRetrievalPlan
$Active = new-object -TypeName ReportingService2010.ActiveState
$ParametersValue = new-object -TypeName ReportingService2010.ParameterValue
$subscriptions= new-object -TypeName ReportingService2010.Subscription
$subscription= new-object -TypeName ReportingService2010.Subscription
$ExtensionPassword = new-object -TypeName ReportingService2010.ParameterValue
$Parameters = new-object -TypeName ReportingService2010.ParameterValue
$reports = $reporting.listchildren("/", $true) | Where-Object {$_.TypeName -eq "Report"}
}
#If nothing matches, run this
default {
Write-Host " ERROR: Undefined SQL Version " $SQL.Version -ForegroundColor "Red"
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
exit -1
}
}
$ExtensionPassword.Name = "PASSWORD"
$ExtensionPassword.value = $password
##List the datasource for all reports
##Loops through for all the Reports on Report Server
foreach ($report in $reports){
$dataSource = $reporting.GetItemDataSources($report.Path)[0]
If ($username -eq $dataSource.Item.UserName){
Write-Host "Report: " $report.name
Write-Host " UserName: " $dataSource.Item.UserName
Write-Host
#Sets the new password
If ($UpdatePassword -eq $true){
$dataSource.Item.Password = $password
$reporting.SetItemDataSources($report.Path, $dataSource)
}
}
}
##Determines which query to use
Switch ($SQLVersion)
{
#SSRS 2008 methods
2008{
$reports = $reporting.listchildren("/", $true) | Where-Object {$_.Type -eq "DataSource"}
}
#SSRS 2008R2 methods
2008R2{
$reports = $reporting.listchildren("/", $true) | Where-Object {$_.TypeName -eq "DataSource"}
}
#If nothing matches, run this
default {
Write-Host " ERROR: Undefined SQL Version " $SQL.Version -ForegroundColor "Red"
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
exit -1
}
}
##Loops through for all the Datasources on Report Server
foreach ($report in $reports){
$dataSource = $reporting.GetDataSourceContents($report.Path)
#Write-Host "Datasource: " $report.name
#Write-Host " User Name: " $dataSource.UserName
#If (!($username.CompareTo($dataSource.UserName) -eq 1)){
If ($username -eq $dataSource.UserName){
Write-Host "Datasource: " $report.name
#Write-Host " Path: " $report.path
#Write-Host " Type: " $report.TypeName #Report; Folder; DataSource
Write-Host " UserName: " $dataSource.UserName
Write-Host
#Sets the new password
If ($UpdatePassword -eq $true){
$dataSource.Password = $password
$reporting.SetDataSourceContents($report.Path, $dataSource)
}
}
}
##Determines which query to use
Switch ($SQLVersion)
{
#SSRS 2008 methods
2008{
$reports = $reporting.listchildren("/", $true) | Where-Object {$_.Type -eq "Report"}
}
#SSRS 2008R2 methods
2008R2{
$reports = $reporting.listchildren("/", $true) | Where-Object {$_.TypeName -eq "Report"}
}
#If nothing matches, run this
default {
Write-Host " ERROR: Undefined SQL Version " $SQL.Version -ForegroundColor "Red"
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
exit -1
}
}
foreach ($report in $reports){
##Determines which syntax and parameters to pass
Switch ($SQLVersion)
{
#SSRS 2008 syntax
2008{
##Gets a list of subscriptions for the Report being referenced
$subscriptions = $reporting.ListSubscriptions($report.Path,"")
}
#SSRS 2008R2 syntax
2008R2{
##Gets a list of subscriptions for the Report being referenced
$subscriptions = $reporting.ListSubscriptions($report.Path)
}
#If nothing matches, run this
default {
Write-Host " ERROR: Undefined SQL Version " $SQL.Version -ForegroundColor "Red"
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
exit -1
}
}
foreach ($subscription in $subscriptions){
$ReportSubscriptionID = [String]$subscription.SubscriptionID #Sets a variable to the SubscriptionID Value
If ($ReportSubscriptionID){
##Looks through the extensions stored within the subscription for Username/Password
foreach ($ParameterValue in $subscription.DeliverySettings.ParameterValues){
If ($ParameterValue.name -eq "USERNAME"){ #Finds the username parameter
If ($ParameterValue.value -eq $username){ #Checks if this is the username we are looking for
Write-Host " Subscription Name: " $subscription.Report
#Write-Host " SubscriptionID: " $Subscription.SubscriptionID
Write-Host " DeliveryExtension: " $subscription.DeliverySettings.Extension
write-host " Field: " $ParameterValue.name
#write-host " Label: " $ParameterValue.label ##Not used
write-host " Value: " $ParameterValue.value
#Sets the new password
$PasswordUpdated = $false
If ($UpdatePassword -eq $true){
$details = ""
#Gets the subscription Properties prior to updating the password
$details = $reporting.GetSubscriptionProperties($Subscription.SubscriptionID, [REF]$ExtensionSettings, [REF]$Description, [REF]$Active, [REF]$Status, [REF]$EventType, [REF]$MatchData, [REF]$ParametersValue)
$i=0 #initialize counter variable
foreach ($ExtensionRow in $ExtensionSettings.ParameterValues){ #Pulls all the ExtensionSettings
#write-host " ExtensionRow: " $ExtensionRow.name
#write-host " ExtensionRow.Values: " $ExtensionRow.value
If ($ExtensionRow.name -eq "PASSWORD"){ #Checks to see if PASSWORD is unencrypted and available to pull
$ExtensionRow.value = $password #Sets the value
Write-Host "!! WARNING: Unencrypted password retrieved" -ForegroundColor "Red"
$PasswordUpdated = $true
break #Exits for loop -- password updated, now to commit change
}
$i++ #Increments counter
If ($ExtensionSettings.ParameterValues.count -eq $i){ #Determines if this is the final iteration and whether the password field was found
$ExtensionSettings.ParameterValues += $ExtensionPassword
$PasswordUpdated = $true
break #Exits for loop -- member group added and set
#}
}
}
#Sets the Subscription settings -- with the new Password
If ($PasswordUpdated -eq $true){ #Password has actually been updated
$details = $reporting.SetSubscriptionProperties($ReportSubscriptionID, $ExtensionSettings, $Description, $EventType, $MatchData, $ParametersValue)
}
ELSE{
Write-Host " ERROR: PASSWORD NOT UPDATED" -ForegroundColor "Red"
}
}
}
}
}
}
}
#Write-Host
}
stop-transcript #Stops logging
(Get-Content $File) | Foreach-Object { $_ -replace '', '' } | Set-Content $File #Fix encoding of file for Notepad
Remove-Variable ExtensionSettings
Remove-Variable ExtensionRow
Remove-Variable DataRetrievalPlan
Remove-Variable Description
Remove-Variable Active
Remove-Variable Status
Remove-Variable EventType
Remove-Variable MatchData
Remove-Variable details
Remove-Variable ParametersValue
Remove-Variable Parameters
Remove-Variable subscriptions
Remove-Variable subscription
Remove-Variable ExtensionPassword
}
############# Main Code ##################
#main($computer,$username,$password,$UpdatePassword)
main
##Cleanup
Remove-Variable computer
Remove-Variable password
#Remove-Variable StackTrace
Remove-Variable UpdatePassword
Remove-Variable username
'Originally from http://blogs.msdn.com/b/jenss/archive/2009/09/01/list-reports-of-a-reporting-services-instance-via-rs-exe-script.aspx
' Lists just the path of all reports; this may be useful as a shell for a function that actually presents data source details.
Public Sub Main()
ListReports()
End Sub
Public Function ListReports() As Boolean
Console.WriteLine("Available Reports")
Console.WriteLine(("================================" + Environment.NewLine))
Dim cat As CatalogItem
For Each cat In rs.ListChildren("/",true)
If cat.Type = 2
Console.WriteLine(cat.Path)
End If
Next cat
End Function
Also possibly useful as a shell, this downloads all RDL
' Originally from http://blog.geektrainer.com/2012/04/download-all-reports.html
' Downloads all Report RDL for other parsing or use
' Does NOT download data source information, either Shared Data Sources or Custom Data Sources or embedded data source
Dim rootPath As String = "C:\Windows\Temp"
Sub Main()
Dim items As CatalogItem() = _
rs.ListChildren("/", true)
For Each item As CatalogItem in items
If item.Type = ItemTypeEnum.Folder Then
CreateDirectory(item.Path)
Else If item.Type = ItemTypeEnum.Report Then
SaveReport(item.Path)
End If
Next
End Sub
Sub CreateDirectory(path As String)
path = GetLocalPath(path)
System.IO.Directory.CreateDirectory(path)
End Sub
Sub SaveReport(reportName As String)
Dim reportDefinition As Byte()
Dim document As New System.Xml.XmlDocument()
reportDefinition = rs.GetReportDefinition(reportName)
Dim stream As New MemoryStream(reportDefinition)
document.Load(stream)
document.Save(GetLocalPath(reportName) + ".rdl")
End Sub
Function GetLocalPath(rsPath As String) As String
Return rootPath + rsPath.Replace("/", "\")
End Function
-- List datasources and what depends on them.
-- Originally from http://stackoverflow.com/questions/9638431/listing-all-data-sources-and-their-dependencies-reports-items-etc-in-sql-ser
-- WARNING: Reports which have been changed from using a shared data source to using a custom data source or embedded data source are still shown under the original shared data source, which is incorrect.
SELECT
DS.Name AS DatasourceName,
C.Name AS DependentItemNameWRONGIFCHANGEDTOCUSTOMDATASOURCE,
C.Path AS DependentItemPathWRONGIFCHANGEDTOCUSTOMDATASOURCE
FROM
dbo.Catalog AS C
INNER JOIN
dbo.Users AS CU
ON C.CreatedByID = CU.UserID
INNER JOIN
dbo.Users AS MU
ON C.ModifiedByID = MU.UserID
LEFT OUTER JOIN
dbo.SecData AS SD
ON C.PolicyID = SD.PolicyID AND SD.AuthType = 1
INNER JOIN
dbo.DataSource AS DS
ON C.ItemID = DS.ItemID
WHERE
DS.Name IS NOT NULL
ORDER BY
DS.Name;
-- Originally from http://gallery.technet.microsoft.com/scriptcenter/List-connection-strings-of-1a9a9adc
-- List connection strings of all SSRS Shared Datasources (and only the shared data sources)
;WITH XMLNAMESPACES -- XML namespace def must be the first in with clause.
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
,SDS.[Path]
,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
FROM dbo.[Catalog] AS SDS
WHERE SDS.Type = 5) -- 5 = Shared Datasource
SELECT CON.[Path]
,CON.SharedDsName
,CON.ConnString
FROM
(SELECT SDS.[Path]
,SDS.SharedDsName
,DSN.value('ConnectString[1]', 'varchar(MAX)') AS ConnString
FROM SDS
CROSS APPLY
SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
,CON.SharedDsName;
-- Originally from http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/a2a9ebe1-0417-46bf-8589-ae4e4a16181c, by Pritam_Shetty
-- Amended with information from http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.datasourcedefinition_members%28v=sql.90%29.aspx
-- Shows a little more information for Shared Data Sources only.
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
)
SELECT
name
,Path
,x.value('ConnectString[1]', 'VARCHAR(MAX)') AS ConnectString1
,x.value('ConnectString[2]', 'VARCHAR(MAX)') AS ConnectString2
,x.value('Extension[1]', 'VARCHAR(MAX)') AS Extension
,x.value('Prompt[1]', 'VARCHAR(MAX)') AS PromptForCredentials
,x.value('WindowsCredentials[1]', 'VARCHAR(MAX)') AS WindowsCredentials
,x.value('ImpersonateUser[1]', 'VARCHAR(MAX)') AS ImpersonateUser
,x.value('ImpersonateUserSpecified[1]', 'VARCHAR(MAX)') AS ImpersonateUserSpecified
FROM (
Select CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML ,Name,Path
from dbo.Catalog
Where /*Type = 5 and*/ content is not Null
) a
OUTER APPLY reportXML.nodes('/DataSourceDefinition') r(x)
ORDER BY name
-- Nothing about data sources, but once we figure it out, I'd add it to this main report.
-- Main metareport originally from http://sqlninja.blogspot.com/2009/01/querying-reportserver-database.html
SELECT DISTINCT CatalogParent.Name ParentName
,Catalog.Name ReportName
,ReportCreatedByUsers.UserName ReportCreatedByUserName
,Catalog.CreationDate ReportCreationDate
,ReportModifiedByUsers.UserName ReportModifiedByUserName
,Catalog.ModifiedDate ReportModifiedDate
,CountExecution.CountStart TotalExecutions
,ExecutionLog.InstanceName LastExecutedInstanceName
,ExecutionLog.UserName LastExecutedUserName
,ExecutionLog.Format LastExecutedFormat
,ExecutionLog.TimeStart LastExecutedTimeStart
,ExecutionLog.TimeEnd LastExecutedTimeEnd
,ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval
,ExecutionLog.TimeProcessing LastExecutedTimeProcessing
,ExecutionLog.TimeRendering LastExecutedTimeRendering
,ExecutionLog.Status LastExecutedStatus
,ExecutionLog.ByteCount LastExecutedByteCount
,ExecutionLog.[RowCount] LastExecutedRowCount
,SubscriptionOwner.UserName SubscriptionOwnerUserName
,SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName
,Subscriptions.ModifiedDate SubscriptionModifiedDate
,Subscriptions.Description SubscriptionDescription
,Subscriptions.LastStatus SubscriptionLastStatus
,Subscriptions.LastRunTime SubscriptionLastRunTime
,CONVERT (VARCHAR (MAX),CONVERT (VARBINARY (MAX),Catalog.content)) AS First64KBofRDLInGridMode
FROM dbo.Catalog
JOIN dbo.Catalog CatalogParent
ON Catalog.ParentID = CatalogParent.ItemID
JOIN dbo.Users ReportCreatedByUsers
ON Catalog.CreatedByID = ReportCreatedByUsers.UserID
JOIN dbo.Users ReportModifiedByUsers
ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID
LEFT JOIN
(
SELECT ReportID
,MAX (TimeStart) LastTimeStart
FROM dbo.ExecutionLog
GROUP BY ReportID
) LatestExecution
ON Catalog.ItemID = LatestExecution.ReportID
LEFT JOIN
(
SELECT ReportID
,COUNT (TimeStart) CountStart
FROM dbo.ExecutionLog
GROUP BY ReportID
) CountExecution
ON Catalog.ItemID = CountExecution.ReportID
LEFT JOIN dbo.ExecutionLog
ON LatestExecution.ReportID = ExecutionLog.ReportID
AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart
LEFT JOIN dbo.Subscriptions
ON Catalog.ItemID = Subscriptions.Report_OID
LEFT JOIN dbo.Users SubscriptionOwner
ON Subscriptions.OwnerID = SubscriptionOwner.UserID
LEFT JOIN dbo.Users SubscriptionModifiedByUsers
ON Subscriptions.OwnerID = SubscriptionModifiedByUsers.UserID
ORDER BY CatalogParent.Name
,Catalog.Name
-- Originally from http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/a2a9ebe1-0417-46bf-8589-ae4e4a16181c
-- Shows the various SQL statements in each report.
;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
path,
xaa.value('CommandType[1]', 'VARCHAR(MAX)') AS CommandType,
xaa.value('CommandText[1]','VARCHAR(MAX)') AS CommandText,
xaa.value('DataSourceName[1]','VARCHAR(MAX)') AS DataSourceNameBADBADBADIfChangedFromSharedToCustomDataSource
FROM (
select name, path,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
) a
OUTER APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') raaa(xaa)
--WHERE x.value('CommandType[1]', 'VARCHAR()') = 'StoredProcedure'
ORDER BY path, name
-- Originally from http://www.mssqltips.com/sqlservertip/1839/script-to-determine-sql-server-reporting-services-parameters-path-and-default-values/
--Find all the reports, and thier parameters and thier default values
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd --ReportDefinition
)
SELECT
NAME
, PATH
, x.value ('@Name', 'VARCHAR(100)') AS ReportParameterName
, x.value ('DataType[1]', 'VARCHAR(100)') AS DataType
, x.value ('AllowBlank[1]', 'VARCHAR(50)') AS AllowBlank
, x.value ('Prompt[1]', 'VARCHAR(100)') AS Prompt
, x.value ('Hidden[1]', 'VARCHAR(100)') AS Hidden
, x.value ('data(DefaultValue/Values/Value)[1]', 'VARCHAR(100)') AS Value
FROM (
SELECT PATH
, NAME
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
WHERE CONTENT IS NOT NULL AND TYPE = 2
) A
CROSS APPLY ReportXML.nodes('/Report/ReportParameters/ReportParameter') R(x)
--WHERE NAME = 'Sales_Report'
--Use the where clause above to look for a specific report
ORDER BY NAME
July 24, 2012 at 7:54 am
Is there any way to get the connection strings that are not from a shared datasource? I assume they have to be in there someplace.
July 24, 2012 at 8:08 am
mejo1111 (7/24/2012)
Is there any way to get the connection strings that are not from a shared datasource? I assume they have to be in there someplace.
That is exactly what I'm trying to do, and have not yet succeeded in. I have to think that they're either encrypted in some way inside the database (quite possibly using .NET techniques, not SQL Server procedures).
The most promising of the code I posted above were the two .rss scripts (execute at the command line with rs.exe) above, the first of which, at least, appears only to work on shared data sources right now. If someone can allocate some time to it, I would imagine that those .rss techniques can be used within reports, just to different nodes. It's possible we'll need to get a data source ID from inside the report, then look up that data source ID and get the connection string and username from elsewhere.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply