SSRS Custom Security with Single Sign-on

By: Kannan Kandasamy | Updated: 2017-07-31 | Comments (6) | Related: > Reporting Services Security

Problem

How do you implement Single Sign-On authentication based on SAML 2.0 response on SQL Server Reporting Services 2016 and Power BI Report Server?

Solution

This tip explains how to implement Single Sign-On authentication based on SAML 2.0 Response in SQL Server Reporting Services (SSRS) 2016. I will also discuss required C# coding for decoding the SAML Response and decrypt the response data using ASSYMETRIC decryption algorithm provided by Cryptography libraries. This solution is primarily for SSRS native mode as SharePoint integrated mode comes with built in integration for custom authentication.

To implement custom Authentication in SSRS 2016, you need to use Custom Security Sample provided by Microsoft. I therefore will only discuss implementation of SAML 2.0 integration along with Custom authentication.

Implementing SAML 2.0 in Report Server

In order to implement Custom authentication in SSRS 2016 native mode we need to implement C# coding as provided in Custom Security Sample provided by Microsoft. Here we are going to modify a few steps on Custom Authentication to implement our Single Sign-on authentication validation using SAML 2.0 response.

There are many approaches in which Single Sign-on might be implemented. Here I am considering one of those scenarios as an example so that you can implement/change according to your scenario.

In this scenario users will be authenticated based on Single Sign-On and SAML 2.0 response will be sent to reporting Services. Report Server web application uses SAML 2.0 response based on our custom code and authenticates user and allows users to SSRS if users are valid report user. This user is not Windows user or domain users. Here "users" means custom users created on a database or any application.

At the high level we need to do the following steps:

SAML Processing – Processes SAML 2.0 response and check whether authentication token is valid
Integrating SAML authentication along with Custom Authentication – If SAML 2.0 authentication token is valid redirect to SSRS using this custom user

1. SAML 2.0 Processing

In my example I am considering that I will be getting SAML 2.0 response where I need to do steps below to validate the authentication.

Decode Response if encoded
SAML 2.0 XML Parsing – Parse the XML and get header
Decrypting user related data
Validating Authenticated information

Decoding SAML Response

We normally get encoded SAML Response and that requires decoding as shown below:

public string Decode64Bit(string samlData)
{
    byte[] data = Convert.FromBase64String(samlData);
    string decodedString = Encoding.UTF8.GetString(data);
    return decodedString;
}

SAML XML Parsing

We need to parse the SAML 2.0 xml as shown below to get AuthenticationStatus, encrypted xml node which contains user related information as shown below.

XmlDocument xDoc = new XmlDocument();
samlData = samlData.Replace(@"\", "");

xDoc.LoadXml(samlData);
XmlNamespaceManager xMan = new XmlNamespaceManager(xDoc.NameTable);
xMan.AddNamespace("samlp", "urn:oasis:names:tc:SAML:2.0:protocol");
xMan.AddNamespace("saml", "urn:oasis:names:tc:SAML:2.0:assertion");
xMan.AddNamespace("ds", "http://www.w3.org/2000/09/xmldsig#");
xMan.AddNamespace("xenc", "http://www.w3.org/2001/04/xmlenc#");

XmlNode xNode = xDoc.SelectSingleNode("/samlp:Response/samlp:Status/samlp:StatusCode/@Value", xMan);
if (xNode != null)
{
    this.AuthenticationStatus = false;
    string statusCode = xNode.Value;
    if (statusCode.EndsWith("status:Success"))
    {
        this.AuthenticationStatus = true;
    }
}

Decrypting USER related data

We normally get encrypted data based on the authentication provider. In this example the authentication provider sends encrypted data based on an ASSYMETRIC key. We need to decrypt that using the below code snippet to get the USERS related information.

XmlDocument xmlDoc = new XmlDocument();
byte[] pkcs8privatekey;
RSACryptoServiceProvider rsa = new RSACryptoServiceProvider();

StreamReader sr = File.OpenText(fileName);
String pemstr = sr.ReadToEnd().Trim();
sr.Close();
if (pemstr.StartsWith(pemp8header) && pemstr.EndsWith(pemp8footer))
{
    pkcs8privatekey = DecodePkcs8PrivateKey(pemstr);
    if (pkcs8privatekey != null)
    {
        rsa = DecodePrivateKeyInfo(pkcs8privatekey);
        if (rsa != null)
        {
            try
            {
                xmlDoc.PreserveWhitespace = true;
                xmlDoc = encryptedData;

                Decrypt(xmlDoc, rsa, "your.rsakey.com");
                return (xmlDoc);
            }
            catch (Exception)
            {
                rsa.Clear();
                return (null);
            }

        }
        else
            return (null);
    }
}
return (null);

Validating Information

Based on the decryption step above we will get a decrypted xml. By parsing this decrypted xml like decoding the SAML Response we will get the user name and other authentication information. If this user is valid in our current users list then we can allow the user to authenticate to Reporting services.

2. Integrating SAML 2.0 Authentication with Custom Authentication in SSRS

As discussed earlier we need to modify our Custom Authentication sample to integrate SAML 2.0 authentication. For integration we need to follow the steps below:

Modification in Page Load in Custom Authentication
Providing Private key if it requires to decrypt
Providing FULL TRUST for new assemblies if used during custom Authentication
Other Changes and Restarting Reporting Services

Page Load modification in Custom Authentication

During Custom Authentication we will use the Logon.aspx page placed on the Report Server folder. Normally that folder will be in this location:

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer

This will change if your Installation location for Reporting Services is different. Please use the location according to your changed location.

This Logon.aspx is used to initiate our custom form to show the users login. Since we are going to use SAML response as the authentication mechanism we need to modify Page_Load to do all the authentication as shown below:

In this scenario it loads Page_Load on Logon.aspx and it uses the SAML response and if the USER passed in the response is valid and the token authentication status is success then this will redirect to the Reporting Services web portal else it will use Logon.aspx to get the correct user.

private void Page_Load(object sender, System.EventArgs e)
{
    bool passwordVerified = false;
    try
    {
        string encodedString = Request.Form["SAMLResponse"].ToString();
        
        string retrievedUserName = GetUserNameFromEncryptedString(encodedString);
        passwordVerified =
           AuthenticationUtilities.VerifyPassword(retrievedUserName, "");
        if (passwordVerified)
        {
            FormsAuthentication.RedirectFromLoginPage(
               retrievedUserName, false);
        }
        else
        {
            Response.Redirect("logon.aspx");
        }
    }
    catch (Exception ex)
    {
        lblMessage.Text = string.Format(CultureInfo.InvariantCulture, ex.Message);
        return;
    }
    if (passwordVerified == true)
    {
        lblMessage.Text = string.Format(CultureInfo.InvariantCulture,
           Logon_aspx.LoginSuccess);
    }
    else
    {
        lblMessage.Text = string.Format(CultureInfo.InvariantCulture,
          Logon_aspx.InvalidUsernamePassword);
    }
}

If we use separate assemblies for decryption and processing we need to add that assembly to the Custom Authentication project.

Private Key for Decryption

While placing our assemblies under the Report Server folder we need to provide the Private Key in the respective folder as shown below:

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin

FULL TRUST for assemblies

For newly added assemblies we need to provide FULL Trust to Reporting Services to execute as shown below:

Below node to be added under PolicyLevel nodes in rssrvpolicy.config.

RSSRvpolicy.config file will be in the location:

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer

<CodeGroup
    class="UnionCodeGroup"
    version="1"
    Name="SecurityExtensionCodeGroup"
    Description="Code group for the yourcompany security extension"
    PermissionSetName="FullTrust">
  <IMembershipCondition
     class="UrlMembershipCondition"
     version="1"
     Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\YourCompany.Saml.SamlProcessor.dll"/>
</CodeGroup>

Other Changes

After replacing all the assemblies as per Custom Authentication setup along with above assemblies we need to recycle Reporting Services as shown below:

reporting services configuration manager

Next Steps

By implementing the above steps using C# with .NET Framework 4.5 we can achieve Single Sign-On authentication in Reporting Services.
Consider this authentication option for your Reporting Services implementation.

About the author

Kannan Kandasamy has over 12 years of experience in SQL Server and has primary focus on Microsoft BI, dimensional modeling and performance tuning

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2017-07-31

SQL Server Reporting Services Custom Security with Single Sign-on