Project Approval Lifecycle Part 2

Miss part 1? Read it here ! 

This process requires that you take business requirements and translate them into detailed solution requirements.  In general, this process takes the business concepts and translates those into solution requirements that can be developed within the recommended alternative.   This process is intuitive for most project teams since it is typical to jump to a solution when a person or team is presented a problem.  However, just as the steps to document the business process was done iteratively to ensure all steps were included and provided a complete context to understand the problem, the process to progressively elaborate the solution requirements will take many iterations to completely define the technical requirements.  For example, if the mid-level business requirement is to send notifications when a triggering event occurs, this can translate into many detailed solution requirements to set up a service to send a notification and the rules that trigger the notification.    The Stage 3 document can take another 3 to 6 months to prepare for projects that are within the organization’s delegated purchasing authority and more time if the project requires a BCP.

Stage 3 Keys to Success

  • Spend adequate time, which can be anywhere from 3 to 12 months depending on the project scope, to define detailed solution requirements. The solution requirements will be the key input to develop the work breakdown structure and determine deliverable expectation documents for the team in charge of the project’s execution phase.
  • Develop the procurement vehicle based on CDT recommendations to ensure all aspects of the procurement have been considered and documented.

that are derived from the mid-level business requirements.  This process requires that you take business requirements and translate them into detailed solution requirements.  In general, this process takes the business concepts and translates those into technical requirements that can be developed in the recommended solution.   This process is intuitive for most project teams since it is typical to jump to a solution when a person or team is presented a problem.  However, just as the steps to document the business process was done iteratively to ensure all steps were included and provided a complete context to understand the problem, the process to progressively elaborate the technical requirements will require many iterations to completely define the technical requirements that convey what is needed to perform the business process step in the new solution.  For example if the mid-level business requirement is to send notifications when a triggering event occurs, this can translate into many detailed technical requirements to set up a service to send a notification with parameters to handle the many different situations when the notification service will be used.    The Stage 3 document can take another 3 to 6 months to prepare for projects that are within the organization’s delegated purchasing authority and more time if the budget requires a BCP and project oversight due to additional documentation requirements.

Stage 4 is the culmination of all the project planning and is the final check to ensure the business objectives have not changed since the Stage 1 analysis was completed, the mid-level requirements and finances gathered in Stage 2 remain consistent with organization’s needs and budget, and that the procurement methodologies and detailed requirements defined in Stage 3 have been prepared completely and are in alignment with prior analyses.  This final review is the last stage prior to issuing the procurement vehicle.  At this stage key action dates for the procurement are finalized, the planning phase is officially over, and the procurement begins.  The Stage 4 documentation consists of a final checklist to ensure the project team is ready to manage the tasks to address bidder questions, make any necessary changes as addenda to the procurement documents, evaluate the bidder responses, negotiate contract items, manage any protests and make the final decision to award.  Stage 4 documentation requires that each of these steps is documented and ensures the procurement was managed according to the plan.  All in all the completion of the stage gate PAL process can take anywhere from 9 months for mature organizations attempting to migrate a well-defined manual process to an electronic solution to 2 years for organizations which have little experience developing project plans or for large scale projects involving many business units across multiple systems.

If you have a project you want to explore more fully but need some help to get it off the ground, Estrada Consulting has excellent staff resources with considerable experience performing the work to complete the Project Approval Lifecycle process.  Our extensive experience in executing system development and implementation projects for government organizations will provide the insight to best prepare your organization to initiate and plan a successful project.

Project Approval Lifecycle Part 1

Project Approval Lifecycle 

Lessons from the California Department of Technology

Do you want to have better success implementing project deliverables?  Do you think you know how to solve a business problem but not sure how to initiate a project?

The CA Department of Technology (CDT) offers an excellent set of tools and techniques, that with practice, a dedicated project team and a deliberate effort will get you on your way to initiating and planning a successful project.

The tools I am referring to are the Project Approval Lifecycle (PAL) stage gate templates and instructions.  The techniques include focus groups and meetings with subject matter experts, key stakeholders and managers, depicting As-Is business processes using conceptual and process flow diagrams, and developing business narratives of the business problems and opportunities.

The PAL process defines the stages (stage gates), which if followed closely will help to move your project from just an idea at initiation to a formal plan for procurement, and the knowledge to support a successful project execution phase.  The goal for all projects is to have the foundation of the project so well defined, documented and understood that the execution phase will be in the best shape to handle technical complexities and unanticipated issues as they arise.

The PAL process employs four templates, which are named Stage 1 – Business Analysis, Stage 2 – Alternatives Analysis, Stage 3 – Solution Development and Stage 4 – Project Readiness and Approval.  The names themselves provide a high-level overview of the activities the project team will complete.  But don’t let the name fool you, there are many aspects that the project team must confront to produce quality analyses and acceptable documentation.

The first template,

Stage 1

– Business Analysis will have you focus on defining the project team, business background, stakeholders, business problems and/or opportunities and measurable objectives.  This seems like a simple list of items to tackle, but it’s not.  The first step in defining the project team is to identify staff who have the business knowledge and the willingness to take precious time out of their day to support a new effort which will not see benefits for some time.  These staff members must represent the entire process which will be affected by the eventual solution and be willing to work together to describe the current activities no matter how poorly each is executed.  The goal is to describe the current situation well enough that there is context for the team who will eventually help develop a solution.  If your audience does not understand the context no matter how bad the situation is, they will not be able to help solve the business problems and will not know how to help meet the business objectives.  This means that all staff who play a role in the business process will need to be represented to know you have captured the context fully.  When the project team does not know how to describe the context of the current situation the staff that is later employed to help solve the business problems will be in no place to help.  Estrada Consulting has been involved in many application development projects and can identify those organizations who have prepared themselves to tackle the difficult challenges of implementing a solution that hits the mark.  It takes the experience of senior business analysts to be able to coax the information from the project team and develop the project artifacts that go into the Stage 1 documentation.

For example, imagine you receive a business problem that states, “The business is not meeting its goal to complete customer requests within 4 business days”.  The corresponding objective may be: “Reduce the number of requests that are delayed due to missed communication by 20% within 6 months of solution implementation”.  This appears to be complete from a SMART (Specific, Measurable, Achievable, Realistic and Time Based) perspective, but there is not enough context for someone on the outside to understand how to help solve the business problem nor satisfy the objective.   The reader does not really understand why requests are being delayed.  The objective provides some insight that communications are being missed.  This seems simple to address by not missing any future communications.  We know that it is never this simple and if we had more context, we would know why these are missed and how to help.  If the business background description were complete we may find that the reason for missed communication is that the current process relies on email alone to communicate activities performed to complete a customer request.  Now the project team or an outside resource can begin to understand that the staff who manage customer requests are overwhelmed with email, which can be solved in a number of ways.  By knowing the context we can begin to determine if we have seen this problem before and what approaches were successful in similar situations in the past.  I may not have the right solution, but the community you rely on to provide solution alternatives will be able to provide a meaningful solution.

For mature organizations that have well-stated policies and procedures the answer to why the delay is occurring may be more easily determined by an outside person by performing a document review.  However, most organizations are facing new business problems and do not have these tools at their disposal.  As a departure from the CDT formula to define the As-IS Business process at the later Stage 2 – Alternatives Analysis, Estrada recommends for less mature organizations or those facing a new business problem, your organization analyze the business process fully before even documenting the business problems and objectives.  If the project team members can’t describe why they believe the problem exists and is not confident enough to put it in writing for the entire project team to see, we contend that the business problems are not completely known and the objectives when met will not have satisfied the needs of the organization.  It is easy to think you know why the business is encountering a problem, but without knowledge of all the actors and steps to complete the process, you will reliably miss important issues that will reduce the affects of the solution.

It is likely the project team will spend more time than expected to define the business background since it is extremely challenging to describe something that one does every day.  This is an iterative process to describe every step of the process and make sure the project team’s and stakeholder’s input is considered.  This is when an experienced business analyst can provide the most value to ensure that when conversations stall, the right probing questions are asked to keep the discussion moving along.  An additional benefit of doing the As-Is Business analysis at Stage 1 is that your team will learn the “hows and whys” each member does their work.  You will also find situations where you can make immediate process improvements that take little effort and will contribute to the success of the project.  This can create a synergy among the team where members will look to help each other and understand the trials of their co-workers.  The Stage 1 – Business Analysis along with the Stage 2 – Alternatives Analysis can often take the most time to complete within the PAL process, which it should.  Estrada Consulting recommends an organization spend 3 months at a minimum to complete the Stage 1 documentation.  For more mature organizations, that have documented the policies and procedures that describe the business background and context, this may only take weeks to complete.

Stage 2

focuses on the alternatives analysis and analyzing those against the current IT environment from the technical and financial perspectives.  Depending on the size and nature of the project and the number of stakeholders the S2AA can take as little as 3 to 6 months and as much as 6 to 12 months to complete.

The S2AA begins with defining the mid-level business requirements that can be derived from analyzing the AS-Is business process flows and narratives, and drive the market research.  Having a clear understanding of the business background, problems and mid-level business requirements is paramount to conducting quality market research.  This knowledge will allow the organization to clearly describe to your audience what the business needs are and how you plan to measure success through SMART objectives.  No matter the type of research the organization plans to conduct from research on the Internet to a formal Request for Information (RFI), the research analysis and assessment team will have the criteria to evaluate proposed alternative solutions.  One of the biggest returns on the market research investment will be quality analysis of the advantages/disadvantages of each alternative, which will be based on well-defined evaluation criteria.  The market research will also help the project team understand what is available out in the market and the cost of each alternative.  This knowledge can often translate into more meaningful requirements that can be prioritized.  The requirements become more meaningful since they can be evaluated and categorized more easily as mandatory versus optional and based on the market alternatives, the most important requirements begin to surface among the team as it argues for the features in each solution.

Another important aspect of the S2AA is to document the financial impact of the current environment and associate a value to the products and services provided by the organization.  The financial analysis will allow the project team to document the added costs (if applicable) to determine if the benefits in implementing the solution can be justified in efficiencies gained or when business growth can be better managed.  For government projects the goal isn’t typically to reduce costs associated with staffing levels, rather to improve efficiencies to address growing demand.  Therefore, the Financial Analysis should not focus for government organizations on reducing costs as the primary focus, rather to better understand what the new normal will cost against current expenditures to provide the costs that are associated with growth in the business when constituents ask how money is being spent and why.  Information technology projects can be expensive and typically cost more than what has been planned when the work to complete these two stages is not done properly.  Most projects that look to implement an electronic solution for a paper-based, manual process that can no longer sustain the business needs will likely increase operational costs and total cost of ownership but will ensure the likelihood of efficiencies as the business continues to grow.  When the organization can rely on the cost estimates at this stage, it will be better prepared to adjust to the new costs and will not be surprised with an out of control budget as the project progresses through the execution phase.

Once the organization has completed the S2AA and has determined final requirements, calculated a budget and identified a recommended alternative solution, the difficult work to define detailed solution requirements comes into play.  This is the Stage 3 – Solution Development work, in which the detailed requirements are derived from the mid-level business requirements and the project team decides on the approach and methodologies to procure goods and services.  Depending on the estimated costs for the project the organization may need to also prepare a Budget Change Proposal to acquire additional funding.  In any project that is estimated to surpass the organizations delegated purchasing authority and/or requires a BCP, there are additional forms and oversight that CDT will require while completing the Stage 3 and 4 documents and on into the execution phase of the project.  CDT offers an extensive resource to draft the procurement vehicle which describes each section that should be considered based on experience from previous implementation work, and an explanation that describes the content that should be included.  During Stage 3 most time will be spent defining detailed solution requirements

Continue Reading Part 2 

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

If you have not already created a strong name key file, generate the key file using the following instructions in this link

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.


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:









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


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









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



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:





Description=”Code group for the sample security extension”





Url=”C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.Samples.ReportingServices.CustomSecurity.dll”/>


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>


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


<deny users=”?” />


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: 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