SSRS Custom Authentication Part 2

Implementing Custom SSRS Authentication

Let`s start working on the implementation.

Step 1: Creating the UserAccounts Database

Create the tables exactly how it`s shown in the Database Model Section explained earlier

Step 2: Building the Sample

Microsoft provides a sample project that implements custom authentication. It can be downloaded from https://github.com/microsoft/Reporting-Services/tree/master/CustomSecuritySample2016

If you have not already created a strong name key file, generate the key file using the following instructions in this link https://docs.microsoft.com/en-us/dotnet/framework/app-domains/how-to-sign-an-assembly-with-a-strong-name

Before compiling the sample, let`s make a small change on it.

  • Open the Logon.aspx file
  • Remove the Register button from the file

In our example users will be registered based on the AD FS claims

To compile the sample using Visual Studio

  • Open CustomSecuritySample.sln in Microsoft Visual Studio.
  • In Solution Explorer, select the CustomSecuritySample project.
  • Look at the CustomSecuritySample project’s references. If you do not see Microsoft.ReportingServices.Interfaces.dll, then complete the following steps:
    • On the Project menu, click Add Reference. The Add References dialog box opens.
    • Click the .NET tab.
    • Click Browse, and find Microsoft.ReportingServices.Interfaces on your local drive. By default, the assembly is in the <install>\ReportServer\bin directory. Click OK. The selected reference is added to your project.
  • On the Build menu, click Build Solution.

Debugging

To debug the extension, you might want to attach the debugger to both ReportingServicesService.exe and Microsoft.ReportingServices.Portal.Webhost.exe. And add breakpoints to the methods implementing the interface IAuthenticationExtension2.

Step 3: Deployment and Configuration

The basic configurations needed for custom security extension are the same as previous releases. Following changes are needed in for web.config and rsreportserver.config present in the ReportServer folder. There is no longer a separate web.config for the reportmanager, the portal will inherit the same settings as the reportserver endpoint.

To deploy the sample

  • Copy the Logon.aspx page to the <install>\ReportServer directory.
  • Copy Microsoft.Samples.ReportingServices.CustomSecurity.dll and Microsoft.Samples.ReportingServices.CustomSecurity.pdb to the <install>\ReportServer\bin directory.
  • Copy Microsoft.Samples.ReportingServices.CustomSecurity.dll and Microsoft.Samples.ReportingServices.CustomSecurity.pdb to the <install>\RSWebApp\bin directory.

If a PDB file is not present, it was not created by the Build step provided above. Ensure that the Project Properties for Debug/Build is set to generate PDB files.

Modify files in the ReportServer Folder

To modify the RSReportServer.config file.

  • Open the RSReportServer.config file with Visual Studio or a simple text editor such as Notepad. RSReportServer.config is located in the <install>\ReportServer directory.
  • Locate the <AuthenticationTypes> element and modify the settings as follows:

<Authentication>

<AuthenticationTypes>

<Custom/>

</AuthenticationTypes>

<RSWindowsExtendedProtectionLevel>Off</RSWindowsExtendedProtectionLevel>

<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>

<EnableAuthPersistence>true</EnableAuthPersistence>

</Authentication>

  • Locate the <Security> and <Authentication> elements, within the <Extensions> element, and modify the settings as follows:

<Security>

<Extension Name=”Forms” Type=”Microsoft.Samples.ReportingServices.CustomSecurity.Authorization, Microsoft.Samples.ReportingServices.CustomSecurity” >

<Configuration>

<AdminConfiguration>

<UserName>username</UserName>

</AdminConfiguration>

</Configuration>

</Extension>

</Security>

<Authentication>

<Extension Name=”Forms” Type=”Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension,Microsoft.Samples.ReportingServices.CustomSecurity” />

</Authentication>

 

To modify the RSSrvPolicy.config file

 

  • You will need to add a code group for your custom security extension that grants FullTrust permission for your extension. You do this by adding the code group to the RSSrvPolicy.config file.
  • Open the RSSrvPolicy.config file located in the <install>\ReportServer directory.
  • Add the following <CodeGroup> element after the existing code group in the security policy file that has a URL membership of $CodeGen as indicated below and then add an entry as follows to RSSrvPolicy.config. Make sure to change the below path according to your ReportServer installation directory:

<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.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.Samples.ReportingServices.CustomSecurity.dll”/>

</CodeGroup>

To modify the Web.config file for Report Server

  • Open the Web.config file in a text editor. By default, the file is in the <install>\ReportServer directory.
  • Locate the <identity> element and set the Impersonate attribute to false.

<identity impersonate=”false” />

  • Locate the <authentication> element and change the Mode attribute to Forms. Also, add the following <forms> element as a child of the <authentication> element and set the loginUrl, name, timeout, and path attributes as follows:

<authentication mode=”Forms”>

<forms loginUrl=”logon.aspx” name=”sqlAuthCookie” timeout=”60″ path=”/” domain=”.yourDomain” enableCrossAppRedirects=”true” ></forms>

</authentication>

  • Add the following <authorization> element directly after the <authentication> element.

<authorization>

<deny users=”?” />

</authorization>

This will deny unauthenticated users the right to access the report server. The previously established loginUrl attribute of the <authentication> element will redirect unauthenticated requests to the Logon.aspx page.

 

Step 4: Some of the other changes required in the web.config file and Microsoft.ReportingServices.Portal.WebHost.exe.config

Adding Machine Keys

  • In <RSPATH>\ReportServer\web.config, add under <system.web>

<machineKey validationKey=”[YOUR KEY]” decryptionKey=”[YOUR KEY]” validation=”SHA1″ decryption=”AES” compatibilityMode=”Framework45″/>

  • Then <RSPATH>\RSWebApp\Microsoft.ReportingServices.Portal.WebHost.exe.config, add under <configuration>

<system.web>  <machineKey validationKey=”[YOUR KEY]” decryptionKey=”[YOUR KEY]” validation=”AES” decryption=”SHA1″ compatibilityMode=”Framework45″ /></system.web>

Our example is a little bit different from Microsoft`s example, we are setting he decryption to SHA1 and compatibility mode to Framework 4.5 in order to share cookies between our Ladining Page ( MVC APP ) and SSRS

Step 5: Configure Passthrough cookies

The new portal and the reportserver communicate using internal soap APIs for some of its operations. When additional cookies are required to be passed from the portal to the server the PassThroughCookies properties is still available. More Details: https://msdn.microsoft.com/en-us/library/ms345241.aspx In the rsreportserver.config file add following under <UI>

<UI>   <CustomAuthenticationUI>      <PassThroughCookies>         <PassThroughCookie>sqlAuthCookie</PassThroughCookie>      </PassThroughCookies>   </CustomAuthenticationUI></UI>

 

Integrating MVC with SSRS

Once the focus of this post is to show how to authenticate with SSRS I will not go through of how to communicate with AD FS.

Supposing that your AD FS integration is done and working let`s see below how to implement the User Registration Process

Step 1: Creating the Authentication Class

  • Add the SSRS Service web reference. The Web Service url is <Your_SSRS_ReportServerURL>/ReportService2010.asmx
  • Add a new class to your MVC project named AuthenticationHelper
  • This class needs to implement the Singleton pattern to avoid losing policies when setting policies using the SSRS WS. See below the Singleton implementation

The class must have the following methods:

  • private List<OrgGroupRole> GetOrgGroups(int orgId)
    • This method will query for the Organization Roles
    • Query: SELECT * FROM OrgGroupRole WHERE OrganizationId = @orgId
  • private void CreateUser(string userName, int orgId)
    • This method will create a new User
    • Query: INSERT INTO Users VALUES(@userName, @organizationId)
  • public Organization GetOrgId(string orgName)
    • This method will find the OrgId based on the Organization name
    • Query: SELECT * FROM Organization WHERE UPPER(OrganizationName) = UPPER(@orgName)
  • private bool IsUserRegistered(string username, int orgId)
    • This method will check if the user already exists
    • Query: SELECT 1 FROM Users WHERE UserName = @userName and OrganizationId = @orgId
  • private List<string> GetSSRSRoleNames(List<int> ids)
    • This method will get the SSRS roles that are mapped to the Organization roles
    • Query: $”SELECT DISTINCT SSRSRoleName FROM OrgGroupRoleSSRS Where OrgGroupRoleId in ({string.Join(“, “, ids)})”

 

For simplicity the methods above are described only with the signature and the query you need to execute to return the data.

The methods below contain the full implementation:

 

SetPolicies Method

This method will call the SSRS Web Service to set policies for the user that is being registered based on the path used as parameter.

AddUserRoles Method

This method will call the SetPolicies Method passing the SSRS Root Folder and Report Builder Role as parameters (You can use the Role that works better for you in the Root Folder) and will call again the SetPolicies Method using the Organization Folder Path

ExecuteUserBasedAuthenticationFlow Method

This method will execute the Authentication flow using the ADFS claims as input parameters.

Step 2: Creating a filter to register users

Once the authentication happens in AD FS and the MVC executes the actions the user authentication flow will be executed.

To do it create an Action Filter and override the OnActionExecuting method as below:

The filter will get the necessary claims, run the authentication flow and if the flow succeed the Forms Authentication Cookies will be created allowing you to access SSRS Reports.

The SSRS cookie name, domain and claims are set in the web.config file, check below:

The domain and cookie names need to match with the ones used in the SSRS setup.

The claims need to match the claim names returned by the AD FS, change it if you need.

SSRS Custom Authentication Part 1

Authentication with the Report Server

SQL Server Reporting Services (SSRS) offers several configurable options for authenticating users and client applications against the report server. By default, the report server uses Windows Integrated authentication and assumes trusted relationships where client and network resources are in the same domain or in a trusted domain. Depending on your network topology and the needs of your organization, you can customize the authentication protocol that is used for Windows Integrated authentication, use Basic authentication, or use a custom forms-based authentication extension that you provide. Each of the authentication types can be turned on or off individually. You can enable more than one authentication type if you want the report server to accept requests of multiple types.

All users or applications who request access to report server content or operations must be authenticated before access is allowed.

When Windows Integrated Authentication does not meet the requirements

In one of our projects we had a scenario where Windows Authentication would not help us to meet the project requirements.

The project was using ADFS to authenticate users from different organizations in a single MVC Application, once the users were authenticated, they should access SSRS based on their ADFS Username and Role claims.

Per Microsoft definition SSRS does not support Single Sign On technologies, so what to do? To solve this problem a Custom Authentication was implemented.

Custom Authentication Flow

Before we start going through the technical implementation is important to understand the authentication flow we used for this scenario.

User Registration Process

See below how the User Registration Process works:

  1. User authenticates in the AD FS
  2. AD FS returns Organization, Role and Username claims
  3. The App will look for the Organization in the Organization Table
  4. The app will create the user in the Users table linked to the Organization
  5. The app will get the AD FS roles and look for them in the OrgGroupRole table
  6. For each role found in the OrgGroupRole Table, the app will map them to SSRS Roles based on the OrgGroupRoleSSRS
  7. Once the mapping is done the app will call SSRS web services to set the policies for the user in the Organization`s folder (each organization will have its own folder in SSRS)

Database Model

Let’s look at the Database model we created to map AD FS Claims to SSRS.

This model is used to create users and map the organization roles to real SSRS roles. See the table’s details below:

  • Organization Table – As it was told in earlier, AD FS will integrate with different organizations. This table will store the Organizations and the SSRS Folder path for the organization
  • Users Table – This table will store the users of the organizations
  • OrgGroupRole Table – This table will store the organization roles provided by AD FS claims
  • OrgGroupRoleSSRS Table – This table will relate the organization roles to real SSRS roles

 

Continue Reading Part 2 

Happy 5 Years at ECI! Congratulations, and Thank You!

October is a big month for ECI and it’s members;

We are happy to announce the 5 year anniversary of Staya Vunnam & Namrata Khatri at Estrada Consulting Inc.

Estrada Consulting Inc. is proud to have some of the greatest and most dedicated talent around as a part of its team. We know and appreciate the hard work and dedication that goes in to pulling off such a successful record of work across the industry.

We understand that you have a choice and we congratulate ourselves on having you a part of our team.

Thank you for bringing your best to help us achieve success across North America year after year.

From the ECI Family, we would like to congratulate you, and thank you on your 5 years anniversary of being a part of our story.

Congratulations on achieving this anniversary with us! We know you have worked hard for this accomplishment and we truly appreciate your dedication.

Re-Thinking Communication on Complex Projects Part 1

Since the initial Chaos studies by the Standish Group in 1995, the industry has been trying to resolve the
high failure rates of projects. Since most organizations trying to address the problem were associated
with project management, improvements were recommended in the entire project environment with a
high-level of emphasis on the project manager to ensure the right conditions exist. In 1995, when I
reviewed the results of the Chaos study and the many lessons learned published from industry sources, I
created a term I called “Informed Misperception.” Today, I still believe that this one factor is the root of
many project problems.

What is Informed Misperception (IM) and what causes it?

Just think about how many messages you interpret from others in a given day. Now recall how many of
these may have had a misinterpretation at first that needed to be clarified and explained. This is a
natural phenomenon of communication. In projects, communication between some parties needs to be
complete and precise. Rarely, however, do individuals feel like they have the time to get to complete
and precise communication on many points that matter. In fact, there are many conditions that worsen
the actual level of completeness or precision each individual delivers.
Consider the following conditions:

 A technology designer is in a meeting to communicate design.

1. The design individual’s supervisor or lead is not in the meeting.
2. Few, if any, peers exist for interpretation of the design.
3. Parties that have to use the design to complete implementation are present.
4. There are many individuals in the meeting.
5. The design individual is remote and the meeting is a virtual session.
6. The meeting is scheduled for an hour.
7. The content of the design took over 3 weeks to define.
8. The documentation for the design is not detailed.
9. The design individual is a contracted staff member of the implementation vendor.
10. The business users are present; they intend to ensure their requirements would be met
with the design.

While the list of conditions could expand from the list above, the intent of this document is to explain
enough of the problem to recommend a solution.
What is the potential impact for each of these conditions?
For the numbered items above, here is a brief explanation of only one or two scenarios with each that
could lead to misperception. Note that in all cases, the design individual is likely not trying to be vague
or uncooperative; however, when time is short and demands are high, the level of stress for an
individual can build to a level that requires a relief valve to open – more on this after the explanations
for each condition.

1. The design individual’s supervisor or lead is not in the meeting: when technical supervision is not
present, there is a chance that the individual’s design is not an internally approved design nor that
the formal process for design has been followed. This may have no bearing on the quality of the
design; however, systemically unsupervised design tends to be less focused on what the end system

needs to do and is more focused on technical accuracy. This means the system could be doing the
wrong thing very well. Lapses in supervision have been known to drive poor design or
implementation since the individuals can mean to do the right thing, but have less incentive to
check their own work.

2. Few, if any, peers exist for interpretation of the design: when individuals available to receive or
interpret design, if their skill level for the kind of design being presented is low, the design individual
should try to alter the presentation to explain not only what the design is, but why the design is like
it is. This is to help participants to understand how the design allows the implementation to achieve
what the business needs. Of course, this takes additional time, and most individuals will have the
pressure of time constraints that would lead to short-cuts in explanations. This is a dangerous
situation if you have less than adequate design resources. Knowing that the participants are not
able to interpret the design, the explanations can be vague and the answers to questions can also be
vague and superficial so that the details will always have to come later (or perhaps never). Even if
the designer is excellent, they might not understand the business enough to consider some aspect
of design that should have been incorporated. Due to time constraints, the details are often left for
later discussions for which there seems never to be enough time.

3. Parties that have to use the design to complete implementation are present: while this is a very
good practice, the more parties in the room can mean that the people who really need to
understand the details of the design might not have enough time to get what they need. Formal
meetings hinder the right kind of progress, at times. When the details are discussed and questioned
(is this what you mean, is that what you mean) someone very familiar with the business needs to
ensure that the business intent is still going to work with the details that are being clarified. This
level of clarification rarely takes place, but is the most essential. If business users review
implementation every two weeks and get to adjust the functionality to their needs, this might not
be a problem. If the design is followed with many months of implementation prior to business
review, there is a lot more risk that the design, as implemented, won’t do what the business needs.
In public, formal meetings, when detailed questions of the design may not have answers, the actual
answer can be vague. Implementers start to make assumptions and add details they believe are
accurate to which an incomplete designer could respond a simple “yes.”

4. There are many individuals in the meeting: again, as the meeting grows in participation, the time
becomes less to address all concerns or clarification needs. The conversation tends to elevate to a
higher level to complete all conversations at one level that would never provide the details
necessary to truly interpret completely. The designer also may not get the feedback required to
ensure the design can be adjusted, where needed. The higher level of individuals in the meeting or
design session can also imply that other organizations are trying to cover their responsibilities
through participation. Coupled with other of these conditions, all individuals will tend to say enough
to ensure their point was heard or documented, yet often is insufficient to convey all of the meaning
since there might be objections individuals do not want to address in this large of a meeting. This
can lead to much more ambiguity.

5. The design individual is remote and the meeting is a virtual session: virtual meetings make
communication much more difficult. It is bad enough with language barriers and then the tendency
to be more vague to avoid questions, and other techniques identified here; but, with virtual
participation, the body language (over 80% of true communication) is missed and participants don’t
feel the connection. Often virtual participants begin to do other work and are truly just “phoning it
in” for that session.

6. The meeting is scheduled for an hour: most meetings are scheduled in a way that often does not
consider what is to be reviewed. An hour can easily be wasted with meaningless, high-level
discussions. For design details, it is best to isolate a particular area and to set a time appropriate for
that area. 20-40 minutes is recommended due to adult attention span issues. Small groups that
understand the area under discussion (from the business and technical perspective).

7. The content of the design took over 3 weeks to define: there could be a lot of information that
needs to be communicated. Since even an hour can be challenging to allow for attention span,
many meetings may be needed to clarify what the design means. If many people are involved, the
time required will increase which indicates a need for limiting detail or taking other short cuts to get
through the reviews. It is better to review in short increments than to try to complete an entire
design and try to get all of it approved at once. At that point, the detail will likely have difficulty to
get the attention needed.

8. The documentation for the design is not detailed: more and more designs are relegated to user
mock-ups or wire frames with little consideration for the detailed handling of states or data behind
the scenes. If the details are not available, those who implement will make up their own mind and
even greater communication with the business will be required. Expect many changes if review and
update cycles are short. Expect an insurmountable set of changes if review cycles are long and
following significant implementation. Talking about design with little detail in writing, diagrams, or
other representations can be very misleading. Discussions could be endless and this actually causes
individuals to start agreeing to everything, whether right or wrong, just to get out of the meeting.

9. The design individual is a contracted staff member of the implementation vendor: some contract
professionals are highly responsible to get everything right. Some tailor their effort to the vendor
oversight and do what is necessary to get paid. The days where all participants were employees and
where employees were really concerned with corporate performance or reputation are gone. If the
people you are dealing with have less than adequate integrity, you might never know it until it is too
late. Test their ability to make things right to see what you are dealing with.
10. The business users are present; they intend to ensure their requirements would be met with the
design: this condition is generally a great step to take to ensure that the end product will meet
expectations. If this level of understanding is possible, it is more likely in smaller increments and
should be immediately followed by implementation since the gap in understanding between design
and implementation could be wide. Still, business needs and designs may seem aligned when they
are not. The explanations to show how the design will result in delivering the business needs should
take place. This level of discussion becomes, again, burdensome and not possible in a single
meeting for a large design.

In all, you may begin to understand that the level of detail required at implementation level needs to be
crystal clear and validated quickly. The Informed Misperception term was coined for all cases where the
details become a little too burdensome to explain. The design individual will claim to have informed all
parties in the sessions; yet, the level of true understanding was never achieved and the greater detail
with understanding might never have even been attempted. I have yet to see a technology
implementation project where this does not happen.

Continue Reading Part 2 

Find us at an event near you!

In the next few months the ECI team will be attending multiple events across the West Coast. From California, Washington to British Columbia!

That’s right you heard it! We are now offering services, hiring developers and attending events in Canada.

To keep track of our work, job and learning opportunities, follow us on Twitter, Facebook, Linkedin, & Medium.

Or meet us in Person at one of the events below! We’re the ones with the biggest smile’s in the room waiting to hear from you!

Make sure to get in touch with us if you have any questions!