PowerShell - Report Server Integration - Part 4

The final part of the process is to activate the Report Server Integration feature for all existing site collections.  In previous posts we configured SS2008 and SP2010.  Outside the scope of these posts, my automation process has created some base sites outside of central administration.  Because our changes apply for all new sites, the existing sites do not have access to the Report Server feature.

Microsoft tells you how to do this manually per site: http://msdn.microsoft.com/en-us/library/bb677366.aspx.

I, on the other hand, want to do this via PowerShell as a last step in auto-configuration of Report Server Integration.

First, I have a little utility function for activating features within SharePoint.

# #######################
# ##### Utility Functions #####
# #######################
# A generic function for enabling a SharePoint feature
function ActivateFeature()
{
    # Required parameters for enabling a feature
    param([string]$Identity = $(throw "-Identity parameter is required"), [string]$Url = $(throw "-Url parameter is required"))

    Enable-SPFeature -Identity $Identity -Url $Url
}
# #######################
# #######################

Second, I use that function during the process of looping through site collections on the farm.

# ############################################
# ##### Activate Report Server Integration Feature #####
# ############################################

try
{
    # Capture array of Farm SPService objects into a local variable
    $webAppSrvs = (Get-SPFarm).Services

    # Loop through each SPService object
    foreach($webService in $webAppSrvs)
    {
        Write-Log $logFile "Web Service ($($webService.TypeName)) WebApp.Cnt: $($webService.WebApplications.Count)" $lvlInfo

        # Loop through each SPWebApplication object that exists under the current SPService object.
        foreach($webApp in $webService.WebApplications)
        {
            Write-Log $logFile "Web App: $($webApp.name); Feat Cnt: $($webApp.Features.Count); Prop Cnt: $($webApp.Properties.Count)" $lvlInfo

            # Check for any SPSiteCollection objects under the SPWebApplication object
            if ($webApp.Sites.Count -gt 0)
            {
                # Loop through each SPSiteCollection object
                foreach($siteColl in $webApp.Sites)
                {
                    # Capture the current state of access denied exception handling.
                    $accessDenied = $siteColl.CatchAccessDeniedException

                    # Prevent access denied exceptions from being swallowed.
                    $siteColl.CatchAccessDeniedException = $false

                    # Activate features related to Report Server Integration
                    foreach($feature in $features)
                    {
                        try
                        {
                           ActivateFeature -Identity $feature.Identity -Url "$($siteColl.Url)"
                        }
                        catch
                        {
                            Write-Log $logFile $Error[0] $lvlWarn
                        }
                    }

                    # Return the access deniend exception handling to previous state
                    $siteColl.CatchAccessDeniedException = $accessDenied
                }
            }
        }
    }

    Write-Log $logFile "Successfully activated Report Server Integration feature on all existing site collections." $lvlInfo
}
catch
{
    Write-Log $logFile $Error[0] $lvlError
}

********************
PowerShell - Report Server Integration - Part 1
PowerShell - Report Server Integration - Part 2
PowerShell - Report Server Integration - Part 3
********************

PowerShell - Report Server Integration - Part 3

After configuring SS2008, SP2010 needs to have its Report Server Integration configured.  The manual process takes care of both defining the configuration and activating the feature.

To manually do so, read http://msdn.microsoft.com/en-us/library/bb326213.aspx.

Because the manual process really takes two separate actions, this post will focus on configuring SP2010 Report Server Integration.  The next post will focus on activating the Report Server Integration feature in existing site collections.

The script below will perform the following steps:
1)  Find the existing configuration and delete it.
2)  Create the new configuration, defining its properties
3)  Apply the new configuration to the farm.

# ###############################################
# ##### Configure SharePoint Report Server Integration #####
# ###############################################

try
{
    Write-Log $logFile "Being configuration of SharePoint 2010 Report Server Integration" $lvlInfo

    # Load the library that contains the SPRSSServiceConfiguration class.
    [Reflection.Assembly]::LoadWithPartialName(“Microsoft.ReportingServices.SharePoint.Common”) | Out-Null

    # Retrieve the name of the service from a string constant.  Placed in a local variable for “short-hand” use later
    $serviceName = [Microsoft.ReportingServices.SharePoint.Common.SPRSServiceConfiguration]::RSServiceName

    # Get the current SharePoint farm.
    $spFarm = Get-SPFarm

    # Find all SPService objects that are of the SPRSServiceConfiguration type
    $rsServiceList = [array] $spFarm.Services | Where {$_.TypeName -eq "Microsoft.ReportingServices.SharePoint.Common.SPRSServiceConfiguration"}

    if($rsServiceList -ne $null)
    {
        # Delete any found SPService objects.  We will be making a new one for our configuration purproses
        $rsServiceList | %{$_.Delete()}
    }
       
    # Generate a new SPRSServiceConfiguration object for the Report Server Integration on the SharePoint farm.
    $ssrs = new-object -typeName Microsoft.ReportingServices.SharePoint.Common.SPRSServiceConfiguration($serviceName, $spFarm)

    # Set the configuration properties to work with the SQL Server configuration
    $ssrs.RSServerUrl = $ssrsURL
    $ssrs.AuthenticationType = $authType

    # Execute update functions to commit new configuration
    $ssrs.update() | Out-Null
    $ssrs.provision() | Out-Null

    # Provide the SQL Server Reporting Services service account with SharePoint access
    Get-SPWebApplication -IncludeCentralAdministration | %{$_.GrantAccessToProcessIdentity($sqlServiceAcct)}

    Write-Log $logFile "Successfully configured SharePoint 2010 Report Server Integration" $lvlInfo
}
catch
{
    Write-Log $logFile $Error[0] $lvlError
}

********************
PowerShell - Report Server Integration - Part 1
PowerShell - Report Server Integration - Part 2
PowerShell - Report Server Integration - Part 4
********************

PowerShell - Report Server Integration - Part 2

The first part in establishing Reporting Services is to configure SS2008.  Between cmdlets and WMI objects, this script became a success.  (Catch up on your SS2008 PowerShell reading here).

You can read all about how to manually use the management tools to do this at http://msdn.microsoft.com/en-us/library/bb283151.aspx.

In order to repeat this process through PowerShell, my script does the following steps:
1)  Generate and execute a new database SQL script.
2)  Generate and execute a permissions SQL script.
3)  Connect SS2008 Reporting Services to the new Report Server database.
4)  Stop, then Start the SS2008 Reporting Services service.

Note:  The "Get-WmiObject" cmdlet seemed to give me some fits when it was all lower cased (instead of the camel case at the beginning of this note).

# #######################################################
# ##### Configure SQL Server Report Server - PowerShell Script #####
# #######################################################

try
{
    Write-Log $logFile "Being configuration of SQL Server 2008 Reporting Services" $lvlInfo

    # Capture the wmi namespace property into a local variable
    $wmiNameSpace = "root\Microsoft\SqlServer\ReportServer"

    # Retrieve the ReportServer namespace definition object
    $nsDef = Get-WmiObject -ComputerName $serverName -class "__namespace" -namespace $wmiNameSpace -ErrorAction SilentlyContinue
   
    # Check for existence of namespace definition object
    if ($nsDef -eq $null)
    {
        throw "Failed to find the local Report Server."
    }
   
    # Expand the wmi namespace property to include the specific Report Server instance
    $wmiNameSpace = "$($wmiNameSpace)\$($nsDef.name)\v10\Admin"
   
    # Retrieve the MSReportServer_ConfigurationSetting object
    $rsConfig = Get-WmiObject -ComputerName $serverName -class "MSReportServer_ConfigurationSetting" -namespace $wmiNameSpace -ErrorAction SilentlyContinue
   
    # Check that the object was successfully retrieved
    if ($rsConfig -eq $null)
    {
        throw "Failed to obtain Report Server configuration object."
    }

    # Create the SQL script that will generate a new ReportServer database
    $methResult = $rsConfig.GenerateDatabaseCreationScript($reportServer, $lcid, $true)

    # Check the success of creating the SQL script
    if ($methresult["HRESULT"] -ne 0)
    {
        throw "Failed to create the SQL Script for generating a new report server database."
    }

    # Set the local SQL Server as the base location for executing SQL scripts
    Set-Location SQLSERVER:\SQL\$serverName\$instName

    # Execute the SQL Script for creating a new Report Server
    invoke-sqlcmd -Query $methResult["Script"] -Username $sqlUser -Password $sqlPwd -OutputSqlErrors $true | Out-Null
   
    # Set the base location back to the original command prompt for executing scripts
    Set-Location $currentLocation

    # Create a SQL Script that provides the service account access to the newly created database

    $methResult = $rsconfig.GenerateDatabaseRightsScript($sqlServiceAcct, $reportServer, $false, $true)

    # Check the success of creating the SQL script
    if ($methresult["HRESULT"] -ne 0)
    {
        throw "Failed to create the SQL Script for providing access for the service account to the new report server database"
    }

    # Set the local SQL Server as the base location for executing SQL scripts
    Set-Location SQLSERVER:\SQL\$serverName\$instName

    # Execute the SQL Script for providing the service account access to the new Report Server
    invoke-sqlcmd -Query $methResult["Script"] -Username $sqlUser -Password $sqlPwd -OutputSqlErrors $true | Out-Null

    # Set the base location back to the original command prompt for executing scripts
    Set-Location $currentLocation

    # Set the SQL Server Reporting Services to use the newly created Report Server
    $methResult = $rsConfig.SetDatabaseConnection($dbServer, $reportServer, $credType, $sqlUser, $sqlPwd)

    # Check the success of setting the database connection
    if ($methresult["HRESULT"] -ne 0)
    {
        throw "Failed to change SQL Server's Reporting Services to use the newly created Report Server"
    }

    # Stop and Start SQL Server's Reporting Services to ensure changes take affect
    $rsconfig.SetServiceState($false, $false, $false)
    $rsconfig.SetServiceState($true, $true, $true)

    # With the restarting of the service, recapture the WMI object and confirm changes
    $rsConfig = Get-WmiObject -ComputerName $serverName -class "MSReportServer_ConfigurationSetting" -namespace $wmiNameSpace -ErrorAction SilentlyContinue

    # Check that the SQL Server Reporting Services is ready for SharePoint integration.
    if ($rsConfig.IsSharePointIntegrated -eq $false)
    {
        throw "The SQL Server Reporting Services Report Server database is not configured for SharePoint integration."
    }

    Write-Log $logFile "Successfully Configured SQL Server with a new Report Server and is ready for SharePoint integration." $lvlInfo
}
catch
{
    Write-Log $logFile $Error[0] $lvlError
}

# Ensure the return of the current location back to local system.
Set-Location $currentLocation

********************
PowerShell - Report Server Integration - Part 1
PowerShell - Report Server Integration - Part 3
PowerShell - Report Server Integration - Part 4
********************

PowerShell - Report Server Integration - Part 1

I have SQL Server 2008 (SS2008) and SharePoint 2010 (SP2010).  They are ingrained in a Virtual Machine (VM) template.  I've already had PowerShell (PS) scripts that run against these sysprep'd software applications.  Base configuration has occurred.  Now I want a PS script to establish the Report Server Integration feature within SP2010.

In order to accomplish this, I have to configure Reporting Services within SS2008, Report Server Integration in SP2010, and then activate the features for all existing site collections within SP2010.

The next several posts will describe the break out of each part of the script.  Below is a note about header information and the variables I used throughout the script code you'll see in future posts.

********************
PowerShell - Report Server Integration - Part 2
PowerShell - Report Server Integration - Part 3
PowerShell - Report Server Integration - Part 4
********************

PowerShell Snapins needed.  The use of IF blocks is because my script is daisy chained with others, and I don't know what has been previously loaded, and what still needs to be.

# Add Sql Server cmdlets references
if((Get-PSSnapin | Where {$_.Name -eq "SqlServerCmdletSnapin100"}) -eq $null)
{ Add-PSSnapin SqlServerCmdletSnapin100; }
if((Get-PSSnapin | Where {$_.Name -eq "SqlServerProviderSnapin100"}) -eq $null)
{ Add-PSSnapin SqlServerProviderSnapin100; }

# Add SharePoint cmdlets reference
if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null)
{ Add-PSSnapin Microsoft.SharePoint.PowerShell; }


The variety of local variables I use through out the scripts are:

# Capture the current directory where the script is executing from

$currentLocation = Get-Location

# Capture the name of the computer.
$serverName = $env:computername

# Load the XML configuration file
$config = [xml](Get-Content $args[0]) 

# Define logging variables

$lvlInfo = 0

$lvlWarn = 1
$lvlError = 2

# Get the log file fully qualified path and name.

$logFile = $config.Configuration.Log.Path

# Get the section with configuration for Reporting Services
$rsCfg = $config.Configuration.ReportingServices

$instName = $rsCfg.InstanceName     # Server Instance Name
$reportServer = $rsCfg.ReportServer.DatabaseName    # Name of new Report Server database
$lcid = [int]$rsCfg.ReportServer.Lcid    # Language definition id.  1033 for english

#  Administrative account with permission to execute changes in SharePoint
$sqlUser = $rsCfg.Administrator.Username
$sqlPwd = $rsCfg.Administrator.Password

$sqlServiceAcct = $rsCfg.ReportServer.ServiceAccount    # Service account to associate with Sql Server reporting services
$credType = [int]$rsCfg.ReportServer.CredentialType     # CredentialsType  (0 - Windows; 1 - SQL Server; 2 - Service)
$authType = $rsCfg.AuthenticationType    # How SharePoint will connect with Sql Server Report Server (Windows, Trusted)
$features = $rsCfg.Feature    # Features within SharePoint site collections that will need activating for Reporting Services to work  (Reporting, ReportServer)

$dbServer = "$serverName\$instName"    # Server and instance identifier for use with SQL Server Report Server Configuration
$ssrsURL = "http://$serverName/$($reportServer)_$instName"    # The URL of the report server used by SharePoint for integration.

PowerShell - Simple URL Check

I wanted a basic function that checks that a URL is valid.  So I created the following:

function IsValidUrl([string] $url)

{

    # Declare default return value

    $isValid = $false

   

    try

    {

        # Create a request object to "ping" the URL

        $request = [System.Net.WebRequest]::Create($url)

        $request.Method = "HEAD"

        $request.UseDefaultCredentials = $true

        # Capture the response from the "ping"

        $response = $request.GetResponse()

        $httpStatus = $response.StatusCode

   

        # Check the status code to see if the URL is valid

        $isValid = ($httpStatus -eq "OK")

    }

    catch

    {

        # Write error log

        Write-Host $Error[0].Exception

    }

   

    return $isValid

}