Final Assignment

# Securing and Hardening the Student Information System (SIS) The Student Information System (SIS) retains highly sensitive personal information (SPI) that includes financial records, grades, identities, and medical data; making it a primary target for ransomware and data exfiltration. Compliance frameworks (such as FERPA or GDPR) mandate absolute confidentiality, integrity, and availability.

Key Vulnerabilities Identified Privileged Account Compromise: Reliance on traditional credentials or basic Multi-Factor Authentication (MFA) leaves administrative access vulnerable to sophisticated phishing and session hijacking. Internal Lateral Movement Lack of internal network segmentation allows a compromise in a low-risk environment (like a student portal) to pivot directly into the core student database. Data Spillage in Non-Production Environments Copying live, unmasked student records into testing, reporting, or staging environments severely expands the organizational data-leak surface area.

** Strategic Hardening Recommendations**

1.* Identity & Access Hardening Enforce mandatory phishing-resistant MFA (e.g., FIDO2 hardware keys) for all faculty and administrative staff. Implement Just-In-Time (JIT) access to eliminate persistent administrative privileges. 2. Zero-Trust Network Architecture Micro-segment the network environment. Isolate the core database into a private subnet, cutting off all direct internet access and forcing all queries through an authenticated, rate-limited API gateway. 3. Pervasive Data Protection Encrypt student data at rest (AES-256) and in transit (TLS 1.3). Apply dynamic data masking at the query level to restrict viewable records based on role, and mandate automated data tokenization/anonymization for all non-production environments. 4. Resilient Recovery Capabilities* Deploy automated, immutable (WORM) backups in a separate, air-gapped environment. This architecture assumes an eventual compromise and guarantees data can be fully restored without paying a ransom or suffering catastrophic operational downtime.

To analyze and harden a computing system

Computing system used for retaining students’ personal information (often regulated by frameworks like FERPA or GDPR), one must address the core vulnerabilities across the technical infrastructure, operational workflows, and the human element.

Vulnerability analysis

Threre are three major vulnerabilities for systems managing students’ data: 1. Unauthorized Lateral Movement (Access Control Flaws) The Risk - A compromise of a low-privileged account (such as a student portal or a teacher’s email) allows an attacker to move laterally into the core database holding Sensitive Personal Information (SPI) like Social Security Numbers, grades, or medical records. Root Cause - Lack of network segmentation and over-privileged service accounts.

1. Data Leakage via Unsanitized Environments (Shadow IT & Backups) The Risk - Developers, database administrators, or vendors copy live student data into testing, staging, or analytics environments that lack the strict security controls of the production environment. Root Cause - Absence of automated data masking or tokenization protocols.

2. Session Hijacking and Credential Theft (Human Vulnerabilities) The Risk - Staff members fall victim to targeted phishing or session hijacking (adversary-in-the-middle attacks), compromising active sessions to the student information system (SIS). Root Cause - Reliance on weak or phishable Multi-Factor Authentication (MFA) mechanisms (like SMS or basic push notifications).

System Hardening Framework

In order to mitigate the risks mentioned above and to build a cyber-resilient infrastructure, we must implement the following architectural controls:

1. Identity and Access Management (IAM)Enforce Phishing-Resistant MFA Mandate hardware keys (e.g., FIDO2/WebAuthn keys) or managed passkeys for all administrative and faculty access to the student database. Implement Least Privilege & Just-In-Time (JIT) Access Ensure database administrators do not have persistent write access. Use tools that grant elevated privileges only for the specific window required to perform a task, automatically revoking them afterward. Automated Session Termination Enforce aggressive session timeouts (e.g., 15 minutes of inactivity) on all portal applications to prevent unauthorized access via unattended workstations in classrooms or administrative offices.

2. Data Protection and Encryption Implement Transparent Data Encryption (TDE) Encrypt student data both at rest (using AES-256) within the database and in transit (using TLS 1.3) across all network paths. Enforce Dynamic Data Masking (DDM) Configure the database layer to mask sensitive fields (e.g., showing only the last four digits of a student identifier) based on the user’s role. A teacher may see grades, but financial or medical identifiers must remain obfuscated unless explicitly authorized. Pseudonymize Non-Production Data Establish an automated pipeline that replaces real student identities with randomized tokens or synthetic data before transferring any datasets into testing or reporting environments.

3. Network Architecture and Logging Micro-Segmentation Isolate the database hosting the student records into a dedicated, private subnet. Block all direct internet access to the database layer, allowing connections only through a hardened, authenticated Application Programming Interface (API) gateway or application proxy. Immutable Audit Logging Configure comprehensive query logging to track who accesses, modifies, or exports student data. Stream these logs in real-time to a secure, write-once-read-many (WORM) Centralized Log Repository or SIEM that cannot be altered or cleared by a compromised administrator account.

4. Resilience and Recovery (Zero-Downtime Architecture) Deploy Immutable Backups Store system backups using write-once-read-many (WORM) storage controls. Ensure these backups are logically air-gapped or hosted in a completely isolated cloud account with separate root credentials. Automated Data Integrity Verification Run automated scripts that regularly test the cryptographic hashes of stored data to detect silent tampering or unauthorized modifications before backups are cycled. lll

Vulnerability Assessment Report

System Name: Student Information System (SIS) Target Scope: On-Premise Microsoft SQL Server Environment (Production Infrastructure) Assessor: Lead Cyber Security Architect Classification: Restricted / Internal Use Only

Detailed Vulnerability Findings & Technical Remediation 1. VULN-SQL-001: Missing Transparent Data Encryption (TDE) Risk Explanation: The database files (.mdf, .ldf) and system backups are currently stored on the local disk arrays in plaintext. If an attacker gains physical access to the local server room, or if a lower-privileged backup administrator accidentally copies a backup file onto an unencrypted network share, the entire student database (including Citizenship Identity Cards (CID), contact numbers, and academic records) can be read using an external software viewer without needing SQL Server credentials.

Technical Remediation Script: Run the following TDE generation script sequentially on the master node to register a localized Certificate and enforce AES-256 database-level encryption:-

-- Step 1: Create a Master Key in the Master Database
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Insert_Strong_Complex_Password_Here!!';
GO

-- Step 2: Create a Server Certificate protected by the Master Key
CREATE CERTIFICATE SIS_TDE_Certificate WITH SUBJECT = 'SIS Student Data Encryption Certificate';
GO

-- Step 3: Create the Database Encryption Key (DEK) inside the Target Database
USE StudentInformationSystem;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SIS_TDE_Certificate;
GO

-- Step 4: Enable TDE on the Target Database
ALTER DATABASE StudentInformationSystem SET ENCRYPTION ON;
GO

1. VULN-SQL-002: Mixed-Mode Authentication & Weak sa Account Enabled Risk Explanation: The system configuration allows SQL Server Authentication alongside Windows Authentication (Mixed-Mode). The default system administrator (sa) account is active. This configuration bypasses central Active Directory domain policies, allowing remote brute-force or dictionary-style password sprays directly against the database network port (TCP 1433) without triggering multi-factor authentication (MFA).

Technical Remediation Steps:Open SQL Server Management Studio (SSMS), right-click the server instance, select Properties, click Security, and toggle the server authentication mode to Windows Authentication mode.Disable the default sa login instance manually via query terminal:-

ALTER LOGIN sa DISABLE;
GO

1. VULN-SQL-003: Missing Server-Level Auditing Configurations Risk Explanation: Server auditing features are currently unconfigured or write logs solely to local local application event logs. If a malicious insider or compromised administrative credential alters a student’s grade or exports data records, there is no permanent, immutable trail tracking the modification, directly violating international privacy requirements and local regulatory visibility. Technical Remediation Script: Establish a localized Server Audit that pipes security events out to the separate append-only logging server environment:-

-- Step 1: Create the Server Audit target mapping to an internal secure directory
USE master;
GO
CREATE SERVER AUDIT SIS_Security_Audit
TO FILE ( FILEPATH = 'D:\SQLAudits\' ,MAXSIZE = 10 MB )
WITH ( ON_FAILURE = CONTINUE );
GO

-- Step 2: Create the Specification capturing failed login attempts and permission changes
CREATE SERVER AUDIT SPECIFICATION SIS_Server_Audit_Specification
FOR SERVER AUDIT SIS_Security_Audit
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP);
GO

-- Step 3: Enable the Audit engine
ALTER SERVER AUDIT SIS_Security_Audit STATE = ON;
GO

1. VULN-SQL-004: Plaintext Production Data in Staging and Test Environments Risk Explanation: Real student datasets are systematically copied over into localized software testing environments to help development teams debug user portals. Because these test systems have wider permissions, less perimeter tracking, and are accessible by junior developers or external vendors, this exposure creates an ideal vector for identity theft or silent data leakage. Technical Remediation Steps:
2. Enforce an automated Data Masking and Sanitization Pipeline that intercepts any structural data before it leaves the production boundary zone.
3. Replace identifying student strings with randomized string configurations, shuffle real CIDs, and overwrite actual payment histories with null values before uploading database backups into non-production environments.

Hardening Action Log

System Name: Student Information System (SIS) Environment: On-Premise Microsoft SQL Server (Windows Server 2022 Base) Execution Date: July 19, 2026 Status: Completed & Verified

Step 1: Force Windows Authentication Only & Disable the sa Account Objective: Eliminate database-level brute-force attacks by disabling the default administrative account and forcing all traffic through Windows Active Directory (AD) Kerberos authentication.

Configuration Files / Registry Paths Modified:Registry Key: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer\LoginMode (Changed from 2 [Mixed Mode] to 1 [Windows Only])

Terminal Commands Executed (PowerShell & SQLCMD):

# Open PowerShell as Administrator to change the SQL Server authentication registry flag
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer" -Name "LoginMode" -Value 1

# Restart the SQL Server Service to apply the authentication architecture changes
Restart-Service -Name "MSSQLSERVER" -Force

-- Connect via Local Windows Administrator Account using 

 to lock down the sa login
USE master;
GO
-- Rename the sa login to obscure its presence from basic dictionary attacks
ALTER LOGIN sa WITH NAME = [bhutan_sis_disabled_sa];
GO
-- Disable the account completely and revoke connect permissions
ALTER LOGIN [bhutan_sis_disabled_sa] DISABLE;
REVOKE CONNECT SQL TO [bhutan_sis_disabled_sa];
GO

Step 2: Implement Transparent Data Encryption (TDE) Objective: Encrypt the entire database envelope (.mdf, .ldf, and .bak files) on the local disk arrays using AES-256 encryption keys. Configuration Files / Storage Paths Modified: Database Filesystem Location: D:\SQLData\StudentInformationSystem.mdf Transaction Log Location: L:\SQLLogs\StudentInformationSystem.ldf

Terminal Commands Executed (SQLCMD):

-- 1. Initialize the Master Key inside the Master Database
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '🔒Bhu$an_S1S_S3cure_M4st3r_K3y_2026!';
GO

-- 2. Generate the Server-Level Certificate
CREATE CERTIFICATE SIS_National_TDE_Cert 
WITH SUBJECT = 'SIS National Student Data Encryption Certificate',
EXPIRY_DATE = '2031-12-31';
GO

-- 3. IMMEDIATELY BACK UP THE CERTIFICATE (Critical Operational Requirement)
BACKUP CERTIFICATE SIS_National_TDE_Cert
TO FILE = 'E:\SecureKeys\SIS_National_TDE_Cert.bak'
WITH PRIVATE KEY (
    FILE = 'E:\SecureKeys\SIS_National_TDE_Cert_PrivateKey.pvk',
    ENCRYPTION BY PASSWORD = '🔑Cert_Backup_Strong_Pass_2026!'
);
GO

-- 4. Create the Database Encryption Key (DEK) inside the Production Student Database
USE StudentInformationSystem;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SIS_National_TDE_Cert;
GO

-- 5. Activate TDE Background Encryption Engine
ALTER DATABASE StudentInformationSystem SET ENCRYPTION ON;
GO

Step 3: Establish Server-Level Security Auditing Objective: Ensure compliance with localized privacy metrics by redirecting failed logins, permission escalations, and structural changes to an append-only target directory. Configuration Files / Directory Infrastructure Modified: Audit target path created: D:\SQLAudits\ (Secured with Windows NTFS permissions restricted strictly to the SQL Service Account and the designated Security Officer).

Terminal Commands Executed (SQLCMD):

-- 1. Formulate the Server-Wide Audit Container
USE master;
GO
CREATE SERVER AUDIT SIS_National_Security_Audit
TO FILE ( 
    FILEPATH = 'D:\SQLAudits\',
    MAXSIZE = 50 MB,
    MAX_ROLLOVER_FILES = 20,
    RESERVE_DISK_SPACE = OFF 
)
WITH ( 
    QUEUE_DELAY = 1000, 
    ON_FAILURE = CONTINUE 
);
GO

-- 2. Define the Target Audit Criteria Specification
CREATE SERVER AUDIT SPECIFICATION SIS_Audit_Rules_Specification
FOR SERVER AUDIT SIS_National_Security_Audit
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP);
GO

-- 3. Commmence Auditing Logging Services
ALTER SERVER AUDIT SIS_National_Security_Audit STATE = ON;
ALTER SERVER AUDIT SPECIFICATION SIS_Audit_Rules_Specification STATE = ON;
GO

Step 4: Restrict SQL Port & Host Firewall Rules Objective: Ensure that the database port (TCP 1433) rejects connections from general office intranets, student computer labs, or guest Wi-Fi networks. Configuration Files / Network Rules Modified: Windows Advanced Firewall Inbound Filtering Rules Engine.

Terminal Commands Executed (PowerShell Network Shell Integration):-

# Delete any existing permissive default rules mapping to SQL Server inbound ports
Remove-NetFirewallRule -DisplayName "SQL Server Port 1433" -ErrorAction SilentlyContinue

# Construct a strict Zero-Trust Inbound Rule allowing only authenticated Application Nodes
# Replace 10.100.4.20 and 10.100.4.21 with the actual static IPs of your application servers
New-NetFirewallRule -DisplayName "SIS_DB_Access_Restriction" `
    -Direction Inbound `
    -Action Allow `
    -Protocol TCP `
    -LocalPort 1433 `
    -RemoteAddress "10.100.4.20,10.100.4.21" `
    -Description "Restricts MS SQL inbound data traffic exclusively to designated SIS Application Cluster Nodes."

Post-Hardening Validation & Residual Risk

Post-Hardening Validation Scan Results The system configurations modified on July 19, 2026, were re-scanned using the native database vulnerability evaluation engine. All targeted critical configuration flaws have been successfully resolved. To verify that encryption is fully running on the disk subsystem, the following management view query would be executed on the live environment:

SELECT 
    db_name(database_id) AS DatabaseName,
    encryption_state,
    CASE encryption_state
        WHEN 0 THEN 'No Encryption Key Created'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption In Progress'
        WHEN 3 THEN 'Fully Encrypted'
    END AS EncryptionStatus,
    percent_complete
FROM sys.dm_database_encryption_keys;

Concluding Summary of Residual Risks

While the core database hosting the students’ personal information has been completely hardened against direct infrastructure attacks, no computing environment is completely threat-free. The following residual risks remain and must be managed dynamically:- A. Compromise of an Application Server Node (Medium Risk) The Threat: While the database firewall now rejects traffic from the general network, it explicitly trusts Application Nodes 10.100.4.20 and 10.100.4.21. If an attacker compromises the web portal hosting software via an unpatched .NET or Java software flaw, they can exploit that trusted tunnel to query student records. Mitigation Strategy: Implement host-level intrusion detection systems (HIDS) on the app servers and enforce strict Input Validation and Parameterized Queries to block SQL Injection attempts entirely.

B. Physical Certificate Loss or Corruption (Operational/Resilience Risk) The Threat: Because TDE is actively running, if the underlying operating system crashes or hardware storage fails, the database backups cannot be restored on any other machines without the accompanying master server certificate (SIS_National_TDE_Cert.bak) and private key. Losing these keys results in total, permanent data loss. Mitigation Strategy: Keep the cryptographic key backups generated in the action log on at least two hardware-encrypted, offline USB drives stored in separate physical fireproof safes off-site.

C. Active Directory / Kerberos Infrastructure Compromise (High Impact / Low Likelihood) The Threat: Because database access now relies entirely on Windows Active Directory credentials, if a domain administrator account is compromised globally via a lateral corporate network breach, the attacker can grant themselves privileges to the database environment via group manipulation. Mitigation Strategy: Restrict Active Directory administration to dedicated workstations that are completely isolated from regular internet access or general office tasks.