Running Azure SQL DBs Securely Running Azure Securely

Running Azure SQL DBs Securely Running Azure Securely series SQL Saturday #877 Sat 14 -Sep-2019 Burlington, MA Bill Wilder HELLO my name is Bill Wilder Unless otherwise noted, slide deck contents copyright (c) 2011 -2019, Bill Wilder, @codingoutloud

MS Cybersecurity chief technology officer author co-author 2 courses adjunct faculty 10 x recipient founder

Other Security Sessions Today • We Need To Talk Security by Joe Gavin @ 1: 30 PM • Organizational Security & Compliance with SQL 2012 through 2016/2017 by George Walters @ 2: 45 PM • How to audit and prevent unwanted user actions by John Miner @ 4: 00 PM

Running Azure Securely is a big topic • Today will cover some of the biggest points • Will not cover every detail related to security • Or even every topic • Focus is on concepts and ”big picture” understanding • Demo at the end

“[Cloud security] is a shared responsibility between the customer and the cloud vendor. ” Mark Russinovich, Microsoft Azure CTO https: //www. rsaconference. com/writable/presentations/file_upload/exp-w 01_assume-breach-an-inside-look-at-cloud-service-provider-security. pdf

CIA for the Cloud Established operational and engineering measures that can be situationally applied to protect cloud-based software systems from unauthorized: • Access – breach of confidentiality • Modification – breach of integrity • Destruction or Denial of Access – breach of availability Image © 2017 Elizabeth O’Connor. Used with permission. www. elizabethboconnor. com

Global Enterprise Cloud Platform 54 Azure regions

1 “Region” has N “Data Centers” @codingoutloud

* Preview Services http: //azureplatform. azurewebsites. net/

• SQL Database • Physical construct Physical ($) • Anchored in single region @codingoutloud Logical constructs • Azure Account owns or accesses… • Azure Subscription contains… • Azure Resource Group contains… • SQL Database Server* contains…

Considerations in Protecting SQL DBs Choose Provider Limit Access Audit Activity Ensure Continuity Monitor Threats

Mindset: Defense in Depth (and Breadth)

Choose Provider

Choose Provider – who do you trust? • Reputation: global company w/ lots at stake • Investment: $1 B/year (company) • Platform as a Service: the patching problem • Physical Security: guards, cameras, biometrics • Compliance: they need the UX designers (!) https: //www. microsoft. com/enus/trustcenter/complianceofferings

Limit Access

Limit Access – Layers of Defense Limit Network Limit Logins • VNET • Firewall • AAD (MFA) • AKV • Managed identity* Limit Table Access Limit Data Visibility • SQL Server permissions • CRUD • Masking • Column Encryption

Limiting Visibility Options • Always Encrypted – client side solution + AKV • CLE = Column Level Encryption – Azure feature, AKV, tied to user (“isomorphic”) • Data Masking – Azure feature, tied to user • (TDE does not limit visibility for users)

Limit Access – Defend Control Plane Limit Logins Limit RBAC • AAD (MFA), risky logins • Azure Portal, CLI, Power. Shell, REST API • AKV • Beware password resets, ownership changes, data exports, access to AKV, …

Limit Access – Routine Encryption Encrypt Channel Encrypt as File • Configure* to only allow TDS over encrypted channel • TDE (transparent data encryption) • HYOK option

Who are threat actors? e o lan P l ntro D d. C n e ef

Baseline Policies – locking-down logins • Modern threats are substantially around Identity • Defenses: MFA and detecting risky logins • AAD = Azure Active Directory • AAD powers Office 365, Azure Control Plane, … • Use these baseline policies: https: //docs. microsoft. com/en-us/azure/activedirectory/conditional-access/concept-baselineprotection

Permission the humans • No Windows auth: not supported* • Standard “SQL Server” users: what you might expect, but is a DB concept, not beyond • Old school • AAD users: MFA, enterprise identity, better monitoring plane • No DB password involved • Database Admins, Reporting team • Depends on the database • Standard SQL permissions prevail within the database (alice@example. com can access table X, but not Y)

Limit Access – non-human access • DB connection string for an application? • Can use AAD identity as the principal permissioned to access the database • Standard “SQL Server” users: what you might expect, but is a DB concept, not beyond • Azure AAD users: MFA, enterprise identity, better monitoring plane • Managed identities can do the AKV dance

Audit Activity

Audit Activity – What’s Happening • Logins • Turn on SQL Auditing - spools out to Blob Storage • Who logged in when? What did they do? • Azure SQL DB vs. Azure SQL MI • Queries • Security is through SQL DB • Control Plane vs. Data Plane • Data Plane: SQL Audit Logging • Control Plane: Azure Logging

Who logged into SQL Database? • https: //blog. codingoutloud. com/2019/03/12/who-logged-into-my-azure-sql-database/

Ensure Continuity

Ensure Continuity – BC/DR Prep • BC = Business Continuity • DR = Disaster Recovery • Backups in the cloud (retention up to 10 years) • Disaster Recovery to geo-replicas (can be 1: N) • RPO = Recovery Point Objective (can be seconds) • RTO = Recovery Time Objective (can be 0) • PITR* = Point-in-time Restore (varies by edition) • Local (regional) hardware issues can be transparent

Monitor Threats

Monitor Threats – Respond to Attacks • Azure Security Center – static and dynamic • ATP = Advanced Threat Protection - $ PER NODE

Azure Portal • Creating a SQL Database • Managed Instance, SQL DB, SQL Server • VNET • Configuring a Database • Enable Auditing for user activity like logins • Review Control Plane activity • Firewall on server DEMO • Azure Security Center • Config issues • Threats

(Derived from slide from Mark Russinovich’s talk at RSA 2015) Top Azure Risks Leading to Tenant Breach Risk Mitigation from Toolbox Internet Exposed RDP or SSH Endpoints VM Missing Security Patches Web Application Vulnerability Network ACLs or Host-based Firewall; Strong passwords; VPN or SSH Tunnels Keep Automatic Updates Enabled Securing Azure Web Applications; ASC; Vulnerability scan/penetration test; WAF MFA Weak Admin Credentials Azure SQL Firewall; VNET; ATP Unrestricted SQL Endpoint Manage Access to Storage Resources Storage Key Disclosure Insufficient Security Monitoring Azure Security and Log Management; ASC

Questions? Find this slide deck here Bill Wilder @codingoutloud@gmail. com blog. codingoutloud. com linkedin. com/in/billwilder See you at Boston Azure bostonazure. org