Skip to content

SqlRS

Jump to bottom
dscbot edited this page Oct 26, 2024 · 11 revisions

Parameters

Parameter Attribute DataType Description Allowed Values
InstanceName Key String Name of the SQL Server Reporting Services instance to be configured.
DatabaseServerName Required String Name of the SQL Server to host the Reporting Services database.
DatabaseInstanceName Required String Name of the SQL Server instance to host the Reporting Services database.
ReportServerVirtualDirectory Write String Report Server Web Service virtual directory. Optional.
ReportsVirtualDirectory Write String Report Manager or Report Web App virtual directory name. Optional.
ReportServerReservedUrl Write StringArray[] Report Server URL reservations. Optional. If not specified, 'http://+:80' URL reservation will be used.
ReportsReservedUrl Write StringArray[] Report Manager or Report Web App URL reservations. Optional. If not specified, 'http://+:80' URL reservation will be used.
UseSsl Write Boolean If connections to the Reporting Services must use SSL. If this parameter is not assigned a value, the default is that Reporting Services does not use SSL.
SuppressRestart Write Boolean Reporting Services need to be restarted after initialization or settings change. If this parameter is set to $true, Reporting Services will not be restarted, even after initialization.
Encrypt Write String Specifies how encryption should be enforced when using command Invoke-SqlCmd. When not specified, the default value is Mandatory. Mandatory, Optional, Strict
IsInitialized Read Boolean Returns if the Reporting Services instance initialized or not.

Description

The SqlRS DSC resource initializes and configures SQL Reporting Services server.

Requirements

  • Target machine must be running Windows Server 2012 or later.

  • Target machine must be running SQL Server Reporting Services 20012 or later.

  • To use parameter UseSSL target machine must be running SQL Server Reporting Services 2012 or later.

  • If PsDscRunAsCredential common parameter is used to run the resource, the specified credential must have permissions to connect to the SQL Server instance specified in DatabaseServerName and DatabaseInstanceName, and have permission to create the Reporting Services databases.

  • Parameter Encrypt controls whether the connection used by Invoke-SqlCmd should enforce encryption. This parameter can only be used together with the module SqlServer v22.x (minimum v22.0.49-preview). The parameter will be ignored if an older major versions of the module SqlServer is used. Encryption is mandatory by default, which generates the following exception when the correct certificates are not present:

    A connection was successfully established with the server, but then
an error occurred during the login process. (provider: SSL Provider,
error: 0 - The certificate chain was issued by an authority that is
not trusted.)

    For more details, see the article Connect to SQL Server with strict encryption and Configure SQL Server Database Engine for encrypting connections.

Known issues

  • This resource does not currently have full SSL support, please see issue #587 for more information.

All issues are not listed here, see here for all open issues.

Known error messages

Error: The parameter is incorrect (HRESULT:-2147024809)

This is caused by trying to add an URL with the wrong format i.e. 'htp://+:80'.

Error: The Url has already been reserved (HRESULT:-2147220932)

This is caused when the URL is already reserved. For example when 'http://+:80' already exist.

Error: Cannot create a file when that file already exists (HRESULT:-2147024713)

This is caused when trying to add another URL using the same protocol. For example when trying to add 'http://+:443' when 'http://+:80' already exist.

Error: A connection was successfully established with the server, but then an error occurred during the login process

This is cause by encryption certificates are not correctly configured. Configure the certificates so that encryption works, or turn off the need of encryption by setting Encrypt to Optional (not recommended).

Examples

Example 1

This example performs a default SQL Server Reporting Services configuration. It will initialize SQL Server Reporting Services and register default Report Server Web Service and Report Manager URLs.

Report Server Web Service: http://localhost:80/ReportServer Report Manager: http://localhost:80/Reports

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlRS 'DefaultConfiguration'
        {
            InstanceName         = 'MSSQLSERVER'
            DatabaseServerName   = 'localhost'
            DatabaseInstanceName = 'MSSQLSERVER'
        }
    }
}

Example 2

This example performs a custom SQL Server Reporting Services configuration. It will initialize SQL Server Reporting Services and register the below custom Report Server Web Service and Report Manager URLs.

Report Server Web Service: http://localhost:80/MyReportServer https://localhost:443/MyReportServer

Report Manager: http://localhost:80/MyReports https://localhost:443/MyReports

Note

This resource does not currently handle SSL bindings for HTTPS endpoints.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlRS 'DefaultConfiguration'
        {
            InstanceName                 = 'MSSQLSERVER'
            DatabaseServerName           = 'localhost'
            DatabaseInstanceName         = 'MSSQLSERVER'
            ReportServerVirtualDirectory = 'MyReportServer'
            ReportsVirtualDirectory      = 'MyReports'
            ReportServerReservedUrl      = @('http://+:80', 'https://+:443')
            ReportsReservedUrl           = @('http://+:80', 'https://+:443')
        }
    }
}

Example 3

This example performs a custom SQL Server Reporting Services configuration. It will initialize SQL Server Reporting Services and register the below custom Report Server Web Service and Report Manager URLs and enable SSL.

Report Server Web Service: https://localhost:443/MyReportServer () Report Manager: https://localhost:443/MyReports

Note

This resource does not currently handle SSL bindings for HTTPS endpoints.

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlRS 'DefaultConfiguration'
        {
            InstanceName                 = 'MSSQLSERVER'
            DatabaseServerName           = 'localhost'
            DatabaseInstanceName         = 'MSSQLSERVER'
            ReportServerVirtualDirectory = 'MyReportServer'
            ReportsVirtualDirectory      = 'MyReports'
            ReportServerReservedUrl      = @('https://+:443')
            ReportsReservedUrl           = @('https://+:443')
            UseSsl                       = $true
        }
    }
}

Example 4

This example installs to instances where the first named instance is used for the Reporting Services databases, and the second named instance is used for Reporting Services. After installing the two instances, the configuration performs a default SQL Server Reporting Services configuration. It will initialize SQL Server Reporting Services and register the default Report Server Web Service and Report Manager URLs:

Report Manager: http://localhost:80/Reports_RS Report Server Web Service: http://localhost:80/ReportServer_RS

Configuration Example
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]
        $SqlInstallCredential,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]
        $SqlServiceCredential,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]
        $SqlAgentServiceCredential,

        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.Management.Automation.PSCredential]
        $ReportingServicesServiceCredential
    )

    Import-DscResource -ModuleName 'xPSDesiredStateConfiguration' -ModuleVersion '9.1.0'
    Import-DscResource -ModuleName 'SqlServerDsc'

    Node localhost
    {
        xWindowsFeature 'NetFramework45'
        {
            Name   = 'NET-Framework-45-Core'
            Ensure = 'Present'
        }

        SqlSetup 'InstallDatabaseEngine'
        {
            InstanceName          = 'RSDB'
            Features              = 'SQLENGINE'
            SourcePath            = 'Z:\Sql2016Media'
            BrowserSvcStartupType = 'Automatic'
            SQLCollation          = 'Finnish_Swedish_CI_AS'
            SQLSvcAccount         = $SqlServiceCredential
            AgtSvcAccount         = $SqlAgentServiceCredential
            InstallSharedDir      = 'C:\Program Files\Microsoft SQL Server'
            InstallSharedWOWDir   = 'C:\Program Files (x86)\Microsoft SQL Server'
            UpdateEnabled         = 'False'

            SQLSysAdminAccounts   = @(
                $SqlAdministratorCredential.UserName
            )

            PsDscRunAsCredential  = $SqlInstallCredential

            DependsOn             = @(
                '[xWindowsFeature]NetFramework45'
            )
        }

        SqlSetup 'InstallReportingServicesInstance'
        {
            InstanceName          = 'RS'
            Features              = 'RS'
            SourcePath            = 'Z:\Sql2016Media'
            BrowserSvcStartupType = 'Automatic'
            RSSvcAccount          = $ReportingServicesServiceCredential
            InstallSharedDir      = 'C:\Program Files\Microsoft SQL Server'
            InstallSharedWOWDir   = 'C:\Program Files (x86)\Microsoft SQL Server'
            UpdateEnabled         = 'False'

            PsDscRunAsCredential  = $SqlInstallCredential

            DependsOn             = @(
                '[xWindowsFeature]NetFramework45'
                '[SqlSetup]InstallDatabaseEngine'
            )
        }

        SqlRS 'ConfigureReportingServiceInstance'
        {
            # Instance name for the Reporting Services.
            InstanceName         = 'RS'

            <#
                Instance for Reporting Services databases.
                Tge value $env:COMPUTERNAME can only be used if the configuration
                is compiled on the node that should contain the instance 'RSDB'.
                If not, set to the node name.
            #>
            DatabaseServerName   = $env:COMPUTERNAME
            DatabaseInstanceName = 'RSDB'

            PsDscRunAsCredential = $SqlInstallCredential

            DependsOn            = @(
                '[SqlSetup]InstallReportingServicesInstance'
            )
        }
    }
}

Home

Commands

Resources

Usage

Clone this wiki locally