Skip to content
/ SSRS.Security Public

SQL Server Reporting Services Security Package

License

Apache-2.0 license
7 stars 6 forks Branches Tags Activity
Star
Notifications You must be signed in to change notification settings

PracticeEngine/SSRS.Security

BranchesTags

Repository files navigation

SSRS.Security

SQL Server Reporting Services Security Package

Configuration Settings

The configuration settings are needed needed:

  • AuthUrl
  • PEUrl
  • PEAppId
  • PEAppKey
  • SSRSIntegrationSecret
  • Machine Key & Decryption Key

Here are the steps to identify or create all the settings you will need in subsequent sections.

  1. Identify the following Settings for your Environment:
    1. Auth Url (typically 'https://server/auth')
    2. PE Url (typically 'https://server/PE/')
  2. Create an App Login & ID
    1. Create a new login to a group only with API_Security permission
    2. Create an App ID & App Key on the API Authentication page within PE for that login
  3. Generate a Random secret (any string you make up or randomly generate is fine)
  4. Generate a Machine Key and Decryption Key using IIS
    1. Click onto the server
    2. Open the Machine Keys feature.
    3. Click the Generate Keys link in the Actions at right.
    4. Copy the Machine Key and Decryption Key
    5. Do not save the IIS settings

Configure the PE /Auth App

Make sure you have a current version of the /Auth application. Only versions greater than [9.6.xxxx] support the SSRS pass-through authentication properly. Earlier 9.6 versions will offer some support, but certain management functions (such as connecting via SSMS) will fail.

Set the value in appsettings.json:

{
        "PEAuth":{
                "SSRSIntegrationSecret":"{your-made-up-secret}"
        }
}

SQL Server 2016 - Custom PE Authentication Installation

Installing the Custom Authentication requires several manual steps. Please follow these instructions carefully. Please note these are only for SSRS 2016.

Copy Files

  1. Install and Configure Reporting Services 2016 (Normal Way)
  2. Verify Services are working (connect to Instance)
  3. Identify the Instance Source Directory (e.g. C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services)
  4. Copy the /bin files to the following subdirectories Of the Instance:
    1. \ReportServer\bin
    2. \RSWebApp\bin
  5. Copy the oidclogon.aspx file
    1. Edit the file - set the Authority to the AuthUrl
    2. Place it in the \ReportServer directory

Edit \ReportServer\rsreportserver.config file

Edit the file, replacing any existing <Authentication> <Security> and <Authentication> sections

<Authentication>
	<AuthenticationTypes> 
		<Custom/>
	</AuthenticationTypes>
	<RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>
	<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
	<EnableAuthPersistence>true</EnableAuthPersistence>
</Authentication>
<Security>
    <Extension Name="Forms" Type="SSRS.OpenIDConnect.Security.SSRSAuthorization, SSRS.OpenIDConnect.Security">
        <Configuration>
            <AdminConfiguration>
                <UserName>securityadmin@praceng.com</UserName>
            </AdminConfiguration>
        </Configuration>
    </Extension>
</Security>

When updating the <Security> section - the username provided is the 'root' administrator, and has unlimited and unrestricted access. Use this immediately after configuration to set all other necessary permissions

<Authentication>
    <Extension Name="Forms" Type="SSRS.OpenIDConnect.Security.SSRSAuthentication,SSRS.OpenIDConnect.Security">
        <Configuration>
            <Authentication>
                <AuthUrl>{your-auth-url-NO-trailing-slash}</AuthUrl>
                <PEUrl>{your-pe-url-with-trailing-slash}</PEUrl>
                <PEAppId>{your-app-id}</PEAppId>
                <PEAppKey>{your-app-key}</PEAppKey>
                <SSRSIntegrationSecret>{your-ssrs-secret}</SSRSIntegrationSecret>
            </Authentication>
        </Configuration>
    </Extension>
</Authentication>

Add the following within the <UI> element

<CustomAuthenticationUI>
    <PassThroughCookies>
        <PassThroughCookie>PESSRS</PassThroughCookie>
    </PassThroughCookies>
</CustomAuthenticationUI>

Edit \ReportServer\rssrvpolicy.config file

SSRS uses a strict policy-based system to ensure only trusted code is executed. We must update this with the following sections to allow the new Code files to run.

Find the <CodeGroup> section with Url="$CodeGen$/*" like this:

<CodeGroup
        class="UnionCodeGroup"
        version="1"
        PermissionSetName="FullTrust">
<IMembershipCondition
        class="UrlMembershipCondition"
        version="1"
        Url="$CodeGen$/*" />
</CodeGroup>

Now add all these new sections immediately after that section. Make sure you adjust the path on all entries to match the installation path for your instance.

<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\SSRS.OpenIDConnect.Security.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\IdentityModel.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\Newtonsoft.Json.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\System.IdentityModel.Tokens.Jwt.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\Microsoft.IdentityModel.Logging.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\Microsoft.IdentityModel.Protocols.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\Microsoft.IdentityModel.Protocols.OpenIdConnect.dll"/>
</CodeGroup>
<CodeGroup
        class="UnionCodeGroup"
        version="1"
        Name="SecurityExtensionCodeGroup" 
        Description="Code group for the sample security extension"
        PermissionSetName="FullTrust">
<IMembershipCondition 
        class="UrlMembershipCondition"
        version="1"
        Url="C:\Program Files\Microsoft SQL Server\MSRS13.SSRS\Reporting Services\ReportServer\bin\Microsoft.IdentityModel.Tokens.dll"/>
</CodeGroup>

Edit the \ReportServer\web.config file

Set all the following values within the <system.web> element, replacing the validation and decryption keys with your generated values.

<configuration>
  <system.web>
    ...
    <machineKey validationKey="{generated-validation-key}" decryptionKey="{generated-decryption-key}" validation="AES" decryption="AES" />
    ...
    <authentication mode="Forms">
      <forms loginUrl="oidclogon.aspx" name="PESSRS" timeout="60" path="/"></forms>
    </authentication>
    ...
    <authorization> 
     <deny users="?" />
    </authorization>  
    ...
    <identity impersonate="false" />
    ...
  </system.web>
</configuration>

Within the <assemblyBinding> element, add the following <dependentAssembly> element:

<dependentAssembly>
    <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
    <bindingRedirect oldVersion="9.0.0.0-11.0.0.0" newVersion="11.0.0.0" />
</dependentAssembly>

Edit the \RSWebApp\Microsoft.ReportingServices.Portal.WebHost.exe.config file

Within the WebHost Configuration File, we must make 2 modifications.

Add a <system.web> element within <configuration> replacing the validation and decryption keys with your generated values so that both applications share the same keys.

<system.web>
  <machineKey validationKey="{generated-validation-key}" decryptionKey="{generated-decryption-key}" validation="AES" decryption="AES" />
</system.web>

Within the <assemblyBinding> element, add the following <dependentAssembly> element:

<dependentAssembly>
    <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
    <bindingRedirect oldVersion="9.0.0.0-11.0.0.0" newVersion="11.0.0.0" />
</dependentAssembly>

About

SQL Server Reporting Services Security Package

Topics

security sql reporting openidconnect ssrs

Resources

Readme

License

Apache-2.0 license
Activity
Custom properties

Stars

7 stars

Watchers

3 watching

Forks

6 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages