Platform Security Report

Executive Summary

CRITICAL Findings

1. Connection String Exposed in Linked Service

• ID: SEC-001
• Severity: CRITICAL (95)
• Confidence: high
• Rule: EXPOSED_CONNECTION_STRING
• Location: artifacts/linkedService/SynapseDedicatedPoolLS.json:17

Evidence:

type=plain_text location=typeProperties.connectionString secure=False

Description: The linked service 'SynapseDedicatedPoolLS' contains a plaintext connection string in typeProperties.connectionString. This exposes database credentials and connection details in an insecure manner.

Explanation: Connection strings typically contain sensitive authentication information including server addresses, database names, usernames, and passwords. Storing these in plaintext violates security best practices and increases the risk of credential exposure.

Remediation: Move the connection string to Azure Key Vault and reference it using @Microsoft.KeyVault(SecretUri=...) syntax. Update the linked service to use Key Vault reference instead of plaintext credentials.

• Effort: medium

2. Connection String Exposed in Workspace Default SQL Server

• ID: SEC-002
• Severity: CRITICAL (95)
• Confidence: high
• Rule: EXPOSED_CONNECTION_STRING
• Location: mybigdata/linkedService/mybigdatademows-WorkspaceDefaultSqlServer.json:6

Evidence:

type=plain_text location=typeProperties.connectionString secure=False

Description: The workspace default SQL server linked service 'mybigdatademows-WorkspaceDefaultSqlServer' contains a plaintext connection string in typeProperties.connectionString. This exposes database credentials without proper secret management.

Explanation: The workspace default SQL server connection string contains authentication credentials that should be protected. Plaintext storage increases the attack surface and violates the principle of defense in depth.

Remediation: Move the connection string to Azure Key Vault and reference it using @Microsoft.KeyVault(SecretUri=...) syntax. Configure the linked service to retrieve credentials securely from Key Vault.

• Effort: medium

HIGH Findings

3. Script Activity with Elevated Privileges

• ID: SEC-005
• Severity: HIGH (75)
• Confidence: medium
• Rule: MISSING_LEAST_PRIVILEGE
• Location: artifacts/pipeline/FHIR_Pipeline4Observation_Spark_OC.json:187

Evidence:

activity_script — Script: Create Tables

Description: The pipeline 'FHIR_Pipeline4Observation_Spark_OC' contains a Script activity 'Create Tables' that executes DDL operations. This activity uses the linked service 'SynapseDedicatedPoolLS' which may have elevated privileges beyond what is necessary for normal pipeline operations.

Explanation: Script activities that create tables require elevated database permissions. If the same credentials are used for data processing activities, this violates the principle of least privilege and increases the blast radius of a potential compromise.

Remediation: Review the permissions granted to the linked service and pipeline identity. Implement least-privilege access by creating separate service principals or managed identities for DDL operations versus DML operations. Consider using Azure RBAC roles to limit table creation permissions.

• Effort: medium

4. Script Activity with Elevated Privileges

• ID: SEC-006
• Severity: HIGH (75)
• Confidence: medium
• Rule: MISSING_LEAST_PRIVILEGE
• Location: artifacts/pipeline/FHIR_Pipeline4Patient_DataFlow_OC.json:552

Evidence:

activity_script — Script: Create Tables

Description: The pipeline 'FHIR_Pipeline4Patient_DataFlow_OC' contains a Script activity 'Create Tables' that executes DDL operations using the linked service 'SynapseDedicatedPoolLS'. This may grant excessive permissions to the pipeline execution context.

Explanation: Combining DDL and DML operations under the same security context violates least-privilege principles. Patient data pipelines should use minimal permissions necessary for data processing, with table creation handled by separate, more privileged identities.

Remediation: Implement role separation by using different service principals for DDL and DML operations. Create a dedicated managed identity with limited permissions for table creation activities. Apply Azure RBAC policies to restrict DDL operations to specific identities.

• Effort: medium

5. Script Activity with Elevated Privileges

• ID: SEC-007
• Severity: HIGH (75)
• Confidence: medium
• Rule: MISSING_LEAST_PRIVILEGE
• Location: artifacts/pipeline/FHIR_Pipeline4Claim_Spark_OC.json:373

Evidence:

activity_script — Script: Create Tables

Description: The pipeline 'FHIR_Pipeline4Claim_Spark_OC' contains a Script activity 'Create Tables' that performs DDL operations using 'SynapseDedicatedPoolLS'. This grants the pipeline execution context elevated database permissions that may not be necessary for routine data processing.

Explanation: Claims data processing pipelines should operate with minimal permissions. Granting table creation privileges to the same identity that processes sensitive healthcare claims data increases the risk of unauthorized schema modifications or data exfiltration.

Remediation: Separate DDL and DML operations into different security contexts. Use a dedicated service principal or managed identity with db_ddladmin permissions only for table creation activities. Restrict the main pipeline identity to db_datareader and db_datawriter roles only.

• Effort: medium

MEDIUM Findings

6. Raw SQL Query Without Audit Context

• ID: SEC-003
• Severity: MEDIUM (55)
• Confidence: medium
• Rule: MISSING_AUDIT_LOGGING
• Location: artifacts/sqlscript/Spark DB Exploration Scripts.json:8

Evidence:

raw_sql — "query": "SELECT TOP (100) [Claim_id]\n,[insurance_coverage.display]\n,[insuranc

Description: The SQL script 'Spark DB Exploration Scripts.json' contains raw SQL queries that may execute without proper audit logging or query attribution. This makes it difficult to track data access patterns and investigate security incidents.

Explanation: Raw SQL queries in scripts may bypass audit logging mechanisms, making it difficult to track who accessed what data and when. This is particularly concerning for healthcare data subject to HIPAA compliance requirements.

Remediation: Ensure that all SQL queries are executed through audited channels. Enable diagnostic logging on the Synapse workspace and configure query auditing. Consider using parameterized queries and stored procedures with proper logging.

• Effort: low

7. OPENROWSET Query Without Audit Context

• ID: SEC-004
• Severity: MEDIUM (55)
• Confidence: medium
• Rule: MISSING_AUDIT_LOGGING
• Location: artifacts/sqlscript/JSON_exploration_w_Serverless_Demo_OC.json:9

Evidence:

raw_sql — "query": "SELECT   TOP 100 *\nFROM\n    OPENROWSET(\n        BULK 'https://medic

Description: The SQL script 'JSON_exploration_w_Serverless_Demo_OC.json' uses OPENROWSET to access external data without proper audit logging. This direct data access pattern may bypass security controls and audit trails.

Explanation: OPENROWSET provides direct access to external data sources and may bypass standard audit mechanisms. For healthcare data, all access should be logged and auditable to meet compliance requirements.

Remediation: Enable diagnostic logging for serverless SQL pool queries. Implement query auditing to track OPENROWSET operations. Consider using external tables with proper access controls instead of ad-hoc OPENROWSET queries.

• Effort: low

Remediation Roadmap

Immediate (0-7 days)

• SEC-001: Connection String Exposed in Linked Service — Move the connection string to Azure Key Vault and reference it using @Microsoft.KeyVault(SecretUri=...) syntax. Update the linked service to use Key Vault reference instead of plaintext credentials.
• SEC-002: Connection String Exposed in Workspace Default SQL Server — Move the connection string to Azure Key Vault and reference it using @Microsoft.KeyVault(SecretUri=...) syntax. Configure the linked service to retrieve credentials securely from Key Vault.

Short-term (1-4 weeks)

• SEC-005: Script Activity with Elevated Privileges — Review the permissions granted to the linked service and pipeline identity. Implement least-privilege access by creating separate service principals or managed identities for DDL operations versus DML operations. Consider using Azure RBAC roles to limit table creation permissions.
• SEC-006: Script Activity with Elevated Privileges — Implement role separation by using different service principals for DDL and DML operations. Create a dedicated managed identity with limited permissions for table creation activities. Apply Azure RBAC policies to restrict DDL operations to specific identities.
• SEC-007: Script Activity with Elevated Privileges — Separate DDL and DML operations into different security contexts. Use a dedicated service principal or managed identity with db_ddladmin permissions only for table creation activities. Restrict the main pipeline identity to db_datareader and db_datawriter roles only.

Long-term (1-3 months)

• SEC-003: Raw SQL Query Without Audit Context — Ensure that all SQL queries are executed through audited channels. Enable diagnostic logging on the Synapse workspace and configure query auditing. Consider using parameterized queries and stored procedures with proper logging.
• SEC-004: OPENROWSET Query Without Audit Context — Enable diagnostic logging for serverless SQL pool queries. Implement query auditing to track OPENROWSET operations. Consider using external tables with proper access controls instead of ad-hoc OPENROWSET queries.