Industrialisation Reporting Services

3 mai 2017

Commentaires fermés

Pour l’industrialisation des tâches d’administration Reporting Services, on peut exploiter les Web Services Reporting Services via Powershell/C#, la méthode la plus viable. Dans certains cas, on peut procéder à une mise à jour en masse dans la base de données ReportServer, à condition qu’elle soit supportée. Dans cet article, nous passerons en revue les deux méthodes sur la base de quelques exemples tels que :

– Changer le propriétaire d’un abonnement
– Changer le mot de passe d’une DataSource
– Changer les permissions sur un dossier

Et puis ReportingServicesTools est arrivé…

Changer le propriétaire d’un abonnement

Si vous avez changé récemment de domaine Active Directory (nomenclature de nommage des comptes différente) ou que le compte propriétaire d’un abonnement a été supprimé, voici le message d’erreur que vous pouvez rencontrer :

Failure sending mail: The user or group name ‘DOMAIN\User’ is not recognized.Mail will not be resent.

C’est moche… Pour résoudre cet problème, il suffit de changer le propriétaire de l’abonnement. On me dit dans l’oreillette que le propriétaire d’un abonnement est désormais éditable dans Reporting Service 2016 :

Nouveautés Reporting Services 2016 : https://docs.microsoft.com/en-us/sql/reporting-services/what-s-new-in-sql-server-reporting-services-ssrs

Pour les versions antérieures à SQL Server 2016 et/ou on n’a pas forcément le cœur à y passer des heures en éditant la masse d’abonnements concernés, un à un, on pourra procéder comme suit en T-SQL :

USE [ReportServer]
GO

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier

SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'
SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'

UPDATE dbo.Subscriptions SET 
OwnerID = @NewUserID 
WHERE OwnerID = @OldUserID

USE [ReportServer]

DECLARE @OldUserID uniqueidentifier

DECLARE @NewUserID uniqueidentifier

SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\OldUser'

SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAINA\NewUser'

UPDATE dbo.Subscriptions SET

OwnerID = @NewUserID

WHERE OwnerID = @OldUserID

Source : https://blogs.msdn.microsoft.com/miah/2008/07/10/tip-change-the-owner-of-sql-reporting-services-subscription/

On pourra effectuer la même opération depuis Powershell en exploitant les Web Servcies Reporting Services :

$ssrsProxy = New-WebServiceProxy -Uri "http://localhost/reportserver/ReportService2010.asmx" -UseDefaultCredential
       
#Get subscriptions
$Subscriptions = $ssrsProxy.ListSubscriptions("/")

ForEach ($Subscription in $Subscriptions)
{
    If ($Subscription.Owner -eq "<Enter Old UserName here>")
    {
        $ssrsProxy.ChangeSubscriptionOwner($Subscription.SubscriptionID,"<Enter New Username Here>")
    }
}

$ssrsProxy = New-WebServiceProxy -Uri "http://localhost/reportserver/ReportService2010.asmx" -UseDefaultCredential

#Get subscriptions

$Subscriptions = $ssrsProxy.ListSubscriptions("/")

ForEach ($Subscription in $Subscriptions)

{

If ($Subscription.Owner -eq "<Enter Old UserName here>")

{

$ssrsProxy.ChangeSubscriptionOwner($Subscription.SubscriptionID,"<Enter New Username Here>")

}

Changer le mot de passe d’une DataSource

Dans le cas où les comptes d’accès aux bases de données sont soumis à des règles d’expiration de mot de passe avec historisation pour ne pas faire les choses à moitié, qu’il est bon de pouvoir se reposer sur un script :

$UserName = "Domain\User"
$Password = "******" 

$ssrsProxy = New-WebServiceProxy -Uri "http://localhost/ReportServer/ReportService2010.asmx" -UseDefaultCredential

# Get DataSources
$DataSources = $ssrsProxy.ListChildren('/', $false) | Where-Object {$_.TypeName -eq "DataSource" -and $_.Name -eq "DSName"}
 
foreach($Object in $DataSources) {
    $DataSource = $ssrsProxy.GetDataSourceContents($Object.path)[0]
    $Verify = $DataSource.UserName
    if ($Verify -eq $UserName) {
        $DataSource.WindowsCredentials = $true
        $DataSource.UserName = $UserName
        $DataSource.Password = $Password
        $ssrsProxy.SetDataSourceContents($Object.Path, $DataSource)
  }
}

$UserName = "Domain\User"

$Password = "******"

$ssrsProxy = New-WebServiceProxy -Uri "http://localhost/ReportServer/ReportService2010.asmx" -UseDefaultCredential

# Get DataSources

$DataSources = $ssrsProxy.ListChildren('/', $false) | Where-Object {$_.TypeName -eq "DataSource" -and $_.Name -eq "DSName"}

foreach($Object in $DataSources) {

$DataSource = $ssrsProxy.GetDataSourceContents($Object.path)[0]

$Verify = $DataSource.UserName

if ($Verify -eq $UserName) {

$DataSource.WindowsCredentials = $true

$DataSource.UserName = $UserName

$DataSource.Password = $Password

$ssrsProxy.SetDataSourceContents($Object.Path, $DataSource)

}

Changer les permissions sur un dossier

Dans un contexte de migration, changement de domaine Active Directory à titre d’exemple, cette tâche serait tout autant laborieuse à mettre en œuvre que les précédentes, sans le concours efficace d’un script :

<#
.SYNOPSIS
    Set user permissions in SQL Reporting Services using Web Service
 
.DESCRIPTION
    Set user permissions in SQL Reporting Services using Web Service
 
.EXAMPLE
    Add-SSRSItemSecurity -webServiceUrl "http://[ServerName]/ReportServer/ReportService2010.asmx" -itemPath "MyReportFolder" -groupUserName DOMAIN\User1 -role Browser
 
.EXAMPLE
    Add-SSRSItemSecurity -url "http://[ServerName]/ReportServer/ReportService2010.asmx" -itemPath "MyReportFolder" -u DOMAIN\User1 -r "Content Manager"
 
#>
function Add-SSRSItemSecurity
(
    [Parameter(Position=0,Mandatory=$true)]
    [Alias("url")]
    [string]$webServiceUrl,
 
    [Parameter(Position=1,Mandatory=$true)]
    [Alias("path")]
    [string]$itemPath,
     
    [Parameter(Position=2,Mandatory=$true)]
    [Alias("u")]
    [string]$groupUserName,
     
    [Parameter(Position=3,Mandatory=$true)]
    [Alias("r")]
    [string]$role,
     
    [Parameter(Position=2)]
    [bool]$inherit=$true
)
 
{
     
    #Fix item path if not starting with /
    if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}
     
    #Create Proxy
    Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"
    $ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential
     
    $type = $ssrsProxy.GetType().Namespace;
    $policyType = "{0}.Policy" -f $type;
    $roleType = "{0}.Role" -f $type;
     
    Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."
    $policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);
     
    $a = 1;
    foreach($policy in $policies)
    {
 
        foreach($r in $policy.Roles)
        {
            $msg = "[Add-SSRSItemSecurity()]  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
            Write-Host $msg
        }
        $a+=1;
    }
 
    $msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;
    Write-Host $msg
     
    $Policy = $policies | 
    Where-Object { $_.GroupUserName -eq $groupUserName } | 
    Select-Object -First 1
     
    if (-not $Policy) {
        $Policy = New-Object ($policyType)
        $Policy.GroupUserName = $GroupUserName
        $Policy.Roles = @()
        $Policies += $Policy
        $msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName
        Write-Host $msg
    }
 
    $r = $Policy.Roles |
        Where-Object { $_.Name -eq $role } |
        Select-Object -First 1
    if (-not $r) {
        $r = New-Object ($roleType)
        $r.Name = $role
        $Policy.Roles += $r
        $msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role
        Write-Host $msg
    }
     
    #Set the policies
    $ssrsProxy.SetPolicies($itemPath,$policies);
 
}

Add-SSRSItemSecurity -webServiceUrl "http://localhost/ReportServer/ReportService2010.asmx" -itemPath "Reports" -groupUserName DOMAIN\NewUser -role Browser

.SYNOPSIS

Set user permissions in SQL Reporting Services using Web Service

.DESCRIPTION

Set user permissions in SQL Reporting Services using Web Service

.EXAMPLE

Add-SSRSItemSecurity -webServiceUrl "http://[ServerName]/ReportServer/ReportService2010.asmx" -itemPath "MyReportFolder" -groupUserName DOMAIN\User1 -role Browser

.EXAMPLE

Add-SSRSItemSecurity -url "http://[ServerName]/ReportServer/ReportService2010.asmx" -itemPath "MyReportFolder" -u DOMAIN\User1 -r "Content Manager"

function Add-SSRSItemSecurity

(

[Parameter(Position=0,Mandatory=$true)]

[Alias("url")]

[string]$webServiceUrl,

[Parameter(Position=1,Mandatory=$true)]

[Alias("path")]

[string]$itemPath,

[Parameter(Position=2,Mandatory=$true)]

[Alias("u")]

[string]$groupUserName,

[Parameter(Position=3,Mandatory=$true)]

[Alias("r")]

[string]$role,

[Parameter(Position=2)]

[bool]$inherit=$true

)

{

#Fix item path if not starting with /

if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}

#Create Proxy

Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"

$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential

$type = $ssrsProxy.GetType().Namespace;

$policyType = "{0}.Policy" -f $type;

$roleType = "{0}.Role" -f $type;

Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."

$policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);

$a = 1;

foreach($policy in $policies)

{

foreach($r in $policy.Roles)

{

$msg = "[Add-SSRSItemSecurity()] Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name

Write-Host $msg

}

$a+=1;

}

$msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;

Write-Host $msg

$Policy = $policies |

Where-Object { $_.GroupUserName -eq $groupUserName } |

Select-Object -First 1

if (-not $Policy) {

$Policy = New-Object ($policyType)

$Policy.GroupUserName = $GroupUserName

$Policy.Roles = @()

$Policies += $Policy

$msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName

Write-Host $msg

}

$r = $Policy.Roles |

Where-Object { $_.Name -eq $role } |

Select-Object -First 1

if (-not $r) {

$r = New-Object ($roleType)

$r.Name = $role

$Policy.Roles += $r

$msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role

Write-Host $msg

}

#Set the policies

$ssrsProxy.SetPolicies($itemPath,$policies);

}

Add-SSRSItemSecurity -webServiceUrl "http://localhost/ReportServer/ReportService2010.asmx" -itemPath "Reports" -groupUserName DOMAIN\NewUser -role Browser

Source : https://randypaulo.wordpress.com/2012/02/22/powershell-set-user-permission-in-ssrs-item-sql-reporting-server-using-powershell/

ReportingServicesTools

La bonne nouvelle c’est que Microsoft a mis à disposition un pack de fonctionnalités Powershell sous le nom de ReportingServicesTools, qui couvre un spectre bien plus large en terme d’industrialisation Reporting Services.