Case Studies
SSC MSFT MySQL and LDAP Analysis
Project Overview
In early 2021, Shared Services Canada (SSC) tasked Core Migration with analyzing the Managed Secure File Transfer (MSFT) system.
The project focused on understanding how MySQL and LDAP were being used within the MSFT business application, identifying authentication tokens,
and documenting connection methods, stored data, and security risks.
Core conducted its analysis on a cloned MSFT Unix web server, allowing a full review of system artifacts without impacting production.
The assessment documented file structures, databases, LDAP repositories, user credentials, and provided recommendations for strengthening MSFT’s architecture and operations.
The Challenge
SSC faced significant challenges with its MSFT environment:
– Password security issues: Usernames and passwords were often stored in clear text within property/configuration files and sometimes hardcoded in source code.
– Obsolete server environment: Legacy Unix-based deployment with scattered artifacts and no clear repository of MSFT server components.
– Limited visibility: Difficulty tracing business rules and runtime processes across MySQL databases, LDAP repositories, and MSFT applications.
– Operational risks: Absence of expected server programs (SftServer, SftRouter, BIC) raised concerns about completeness of deployment.
– Undocumented applications: Discovery of a previously unknown SFT LDAP Administration web application written in HTML, JavaScript, PHP, and Perl.
These challenges created security vulnerabilities, integration difficulties, and gaps in operational control.
Our Approach
Core Migration applied a structured system analysis methodology to document and understand MSFT’s technical environment.
Key steps included:
– File System Analysis: Scanned the cloned Unix server, identifying over 294,000 files including source code, config files, and authentication tokens.
– Discovery of SFT LDAP Administration: Identified a new web application that manages LDAP entries, not included in the original MSFT gold code.
– Database Analysis: Documented MySQL databases (SftReportAdmin, sft_ftp) and schemas, detailing tables, user credentials, and application linkages.
– LDAP Repository Analysis: Reverse-engineered over 18,000 entries, including users, endpoints, and MSFT-specific attributes/rules.
– Authentication Token Documentation: Extracted usernames, tokens, and methods of connecting to MySQL and LDAP systems.
– Security Testing: Outlined procedures for resetting MySQL and LDAP admin passwords.
– Business Rule Tracing: Analyzed how runtime data in LDAP and MySQL connects to MSFT processes such as file transfers, reporting, and client management.
Results
The analysis provided SSC with a comprehensive understanding of its MSFT system and uncovered critical findings:
– Improved visibility: Full documentation of databases, LDAP repository, and file structures.
– Security risks identified: Exposed clear-text passwords and hardcoded authentication tokens.
– Operational insight: Clarified how runtime data drives MSFT processes like file routing, reporting, and LDAP administration.
– Extended architecture knowledge: Discovered undocumented applications (SFT LDAP Administration) and runtime data dependencies.
– Practical connection guidance: Documented commands for connecting to and managing MySQL and LDAP environments.
– Actionable roadmap: Provided SSC with recommendations for secure credential management, application documentation, and expanded analysis.
Why It Matters
The analysis provided SSC with a comprehensive understanding of its MSFT system and uncovered critical findings:
– Improved visibility: Full documentation of databases, LDAP repository, and file structures.
– Security risks identified: Exposed clear-text passwords and hardcoded authentication tokens.
– Operational insight: Clarified how runtime data drives MSFT processes like file routing, reporting, and LDAP administration.
– Extended architecture knowledge: Discovered undocumented applications (SFT LDAP Administration) and runtime data dependencies.
– Practical connection guidance: Documented commands for connecting to and managing MySQL and LDAP environments.
– Actionable roadmap: Provided SSC with recommendations for secure credential management, application documentation, and expanded analysis.2
Environment
– Legacy Components: Unix server hosting MySQL databases, LDAP repository, PHP/Perl scripts, and MSFT application artifacts.
– Databases:
– SftReportAdmin: primary reporting DB with multiple schemas.
– sft_ftp: supporting file transfer processes.
– LDAP Repository: Over 18,000 entries managing authentication, rules, and runtime file transfer logic.
– Applications: MSFT core processes (SftServer, SftRouter, BIC) and newly discovered SFT LDAP Administration web app.
– Tools Used: File system analysis, SQL queries, LDAP search commands, spreadsheet-based repository extraction.