An Azure relational database service.
Hey Rajiv 199707,
Thank you for asking your question on the Microsoft Q&A portal and I appreciate the response from Alex Burlachenko and would like to add more details to it.
Rajiv, seems like you're deploying a Premium-tier Azure SQL Database for an EHR system handling PHI like medical histories and billing data, with T-SQL-based operations and Azure Etnra authentication, and need technology-focused guidance on enabling server/database-level auditing for events like logins and schema changes, integrating with Log Analytics for KQL-based querying, and setting up Advance thread protection (ATP) for ML-driven threat detection (e.g., SQL injection or exfiltration anomalies) to achieve HIPAA compliance.
In a healthcare EHR scenario, auditing must log granular events (e.g., a doctor's T-SQL SELECT on a patient's history table at 03:25 PM IST on September 18, 2025, or an UPDATE via stored procedure during a treatment plan revision) to an Azure Storage account or Log Analytics for immutable, queryable trails using KQL (e.g., AuditLogs | where TimeGenerated > ago(7d) | where ActionName == "LOGIN"). Threat detection employs ATP's behavioral analytics to flag risks like SQL injection in dynamic queries (e.g., unparameterized inputs leading to UNION-based attacks) or exfiltration (e.g., exporting >1GB of PHI via BCP utility outside peak hours), with alerts routed to Microsoft Sentinel for SIEM correlation, ensuring auditability for HIPAA's Security Rule requirements on access controls and incident response.
Recommendations for the approach:
Begin by enabling auditing at the server level for broad coverage: In the Azure Portal, navigate to your SQL Server resource > Auditing > Enable server auditing > Select a Log Analytics workspace or Azure Storage account (e.g., "storageAccountName" with geo-redundant storage and CMK via Azure Key Vault) > Customize audit actions/groups (e.g., include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP for PHI table modifications).
For database-specific auditing, go to the SQL Database > Auditing > Enable database auditing > Align with server settings and add filters like excluding non-PHI tables. Integrate with Azure Monitor by linking the Log Analytics workspace under Monitoring > Diagnostic settings > Send audit logs to Logs. For threat detection, under the SQL Database > Security > Microsoft Defender for SQL (formerly ATP) > Enable > Configure detection types (e.g., anomalous logins, vulnerable activities like weak cipher usage) > Set alert emails to ******@healthcare.org and webhook to Sentinel > Enable vulnerability assessments for periodic T-SQL code scans.
This ensures logs can be queries via KQL in Log Analytics (e.g., visualizing login spikes) and threats trigger automated playbooks for isolation.
Best Practices:
- Use server-level auditing as the foundation, with database overrides for granularity, to avoid log duplication while capturing cross-database events like failed PolyBase imports.
- Implement log retention policies (e.g., 365 days in Log Analytics) and automate KQL-based dashboards in Azure Workbooks for compliance reporting, such as weekly PHI access summaries.
- For threat detection, enable just-in-time assessments and integrate with Azure AD sign-in logs for holistic anomaly detection (e.g., correlating unusual logins with ATP alerts).
- Encrypt all logs with Azure Storage's SSE with CMK and restrict access via RBAC (e.g., Log Analytics Reader role for auditors only).
- Conduct quarterly simulations, like injecting a mock SQL injection payload via a test app, to validate alert latency (<5 minutes) and response workflows.
Reference Links:
https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview https://learn.microsoft.com/en-us/azure/azure-sql/database/threat-detection-configure
Please "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.
Thanks, and Happy to Help
Pratyush