What’s new in SQL Server and Azure SQL Database Security

1. What’s new in SQL Server and Azure SQL Database Security • Andreas Wolter • andreas.wolter@microsoft.com • Program Manager SQL Server & Azure SQL Database Security

2. Content • Advanced Data Security • Data Discovery & Classification • ThreatProtection • Vulnerability Assessment • Always EncryptedwithEnclaves • Authentication • Roadmap

3. Enterprise Grade Security that is Easy-to Use Customer Data Encryption-in-flight (Transport Layer Security TLS) Encryption-at-rest (Transparent Data Encryption TDE) Service- or User-managed keys, Backup encryption Encryption-in-use (Always Encrypted) Data Masking (dynamic) Data Discovery and Classification Data Protection SQL Permissions Row-level security Column-level security Access Control SQL Authentication Azure Active Directory Authentication (w/ MFA) Authentication Virtual Networks SQL Firewall (server- and database-level) Network Security Data Classification Advanced Threat Protection Auditing Vulnerability Assessment Threat Protection

4. Data Protection&Threat Protection

5. Advanced Data SecurityUnified  package of SQL security intelligent capabilities • Data Classification • Vulnerability Assessment • Advanced Threat Protection

6. Public Preview SQL Data ClassificationDiscover, classify, protect and track access to sensitive data • Automatic discovery of columns with sensitive data • Add persistent sensitive data labels • Audit and detect access to the sensitive data • Manage labelsfor your entire Azure tenant using Azure Security Center

7. Data Discovery & Classification Demo

8. General Availability SQL Vulnerability AssessmentDiscover, track, and remediate security misconfigurations • Identify securitymisconfigurations • Actionable remediation steps • Securitybaseline tuned to your environment • Manual/periodic scans • Coherent reportsforauditors

9. General Availability Advanced Threat Protection Detect unusual and harmful attempts to breach your database. • Detects potential SQLinjectionattacks • Detects unusualaccess& dataexfiltrationactivities • Actionable alerts to investigate & remediate • View alerts for your entire Azure tenant using Azure Security Center Azure SQL Database Apps Attacker (2) Possible threat to access / breach data Audit Log Threat Detection (1) Turn on Threat Detection (3) Real-time actionable alerts Developer User

10. General Availability Advanced Threat Protection Suite Potential SQL injection attacks • SQLi attempt - An application generated a faulty SQL statement, which may indicate a potential vulnerability of the application to SQL injection. • SQLi attack - Potential exploitation of application code vulnerability to SQL Injection, which may indicate a SQL Injection attack. Anomalous access patterns • Someone has logged from an unusual location - change in the access pattern from an unusual geographical location • An unfamiliar principal successfully logged- - change in the access pattern using an unusual SQL user. • Someone is attempting to brute force SQL credentials abnormally high number of failed logins with different credentials. • Someone has logged from a potentially harmful application • Anomalous queries patterns • Data exfiltration by volume - someone has extracted anomalous amounts of data in an hour or using a single query • Data exfiltration by location - someone has backup database to an unusual storage location, • Unsecure commands - Someone has executed unsecure commands (e.g. xp_cmdshell…)

11. SQL Auditing in Log Analytics and Event Hubs Gain insight into database audit log • Configurable via audit policy • SQL audit logs can reside in • Azure Storage account • Azure Log Analytics • Azure Event Hubs • Rich set of tools for • Investigating security alerts • Trackingaccess to sensitive data Audit Log Azure SQL Database (1) Turn on SQL Auditing (2) Analyze audit log Developer

12. Advanced Data Security Demo

13. Gain security insights via Log Analytics and Power BI dashboards

14. SQL ADS Roadmap Centralized Management Azure Security Center manages ADS across entire tenant. Central policy management + central dashboard • Full Hybrid support • Support of all ADS capabilities (incl Threat Detection) on SQL Anywhere – PaaS, IaaS, on-premises • Compliance Scenarios • Support specific mapping to compliance regulations, dedicated reports • Additional Data Services • ADS for Storage, Cosmos DB in addition to Managed Instance, PostgreSQL, MySQL

15. Always Encrypted C: \ Current GA version in SQL Server 2016/17 and Azure SQL DB SQL • Protects sensitive data in use from high-privileged yet unauthorized SQL users both on-premises and in the cloud Enhanced Client Driver plaintext ciphertext • Encryption Transparency • Client driver transparently encrypts query parameters and decrypts encrypted results • Queries on Encrypted Data • Support for equality comparison, including join, group by and distinct operators via deterministic encryption • Client side Encryption • Client-side encryption of sensitive data using keys that are never given to the database system

16. Always Encrypted - Challenges • Reduced functionality of queries on encrypted columns • Encrypted columns only allow equality comparisons Data needs to be moved out of the database for initial encryption and key rotation This process can be time-consuming and prone to network errors

17. Always Encrypted using Secure Enclaves Confidential computing brings secure enclaves Trusted execution environments protecting data in use SQL Server Engine delegates operations on encrypted data to the enclave, where the data can be safely decrypted and processed. Rich computations on encrypted data! In-place encryption and key management, without moving data out of the database CTP • Protects sensitive data in use while enablingrich computations and in-place encryption Enhanced Client Driver plaintext ciphertext plaintext SQL enclave

18. Confidential Computing using Enclaves • Enclave – an isolated region of memory • Provides a trusted execution environment • Data stored inside the enclave cannot be accessed outside of the enclave​ • Code running inside enclave must be signed and cannot be modified​ • Secure isolation powered by • Hardware, e.g. Intel Software Guard Extension (SGX), OR • Hypervisor, e.g. Virtualization Based Security​ in Windows Server 2019, Windows 10, v. 1809 Code App App Data Operating System Hypervisor Hardware

19. Enclave Attestation and Secure Tunnel • How do you know the SQL enclave is trustworthy? • Answer: enclave attestation • How does the enclave gets the keys to encrypt/decrypt data? • Answer: secure tunnel Attestation Service Enhanced Client Driver plaintext ciphertext plaintext SQL Enclave secure tunnel

20. Always Encrypted with Enclaves in Screenshots

21. Public Preview (SQL Server 2019) Always Encrypted with Secure Enclaves C: \ SQL • Protects sensitive data in use while preserving rich queries and providing in-place encryption Enhanced Client Driver plaintext plaintext ciphertext Enclave • In-place Encryption • The secure enclave supports initial data encryption and key rotation in-place - without moving the data out of the database • Secure computations inside an enclave • SQL Server Engine delegates operations on encrypted to a secure enclave, where the data can be safely decrypted and processed • Rich Queries • Supports pattern matching (LIKE), range queries (<, >, etc.), and indexing on encrypted columns

22. Next Steps Try it now in SQL Server 2019 Preview! Tutorial: https://aka.ms/AlwaysEncryptedEnclavesTutorial Documentation: https://aka.ms/AlwaysEncryptedwithSecureEnclaves Tutorial: Getting started with Always Encrypted with secure enclaves using SSMS Blog: https://blogs.msdn.microsoft.com/sqlsecurity/tag/always-encrypted/

23. Access Management

24. Azure Active Directory Authentication - InteractiveSupport for Multi-Factor Authentication (MFA) All the benefits of Azure Active Directory Authentication plus: • Flexible Configuration • Conditional Access for configuring domain accounts for MFA • Can impose MFA without asking domain administrator to make global change • Interactive Authentication • New INTERACTIVE mode  •     w/o  hard-coded passwords •     supporting  MFA • MSA & non-MSA accounts • Hotmail, Outlook, Live… • Google • Certificate-based authentication • Managed Service Identity (MSI)  • Supported in many Tools and Drivers • SSMS (since 17.2+) • DacFx • SQL Package (Import/Export) • SQLCMD, BCP  • SSDT (with latest VS 17 release) • Drivers: • .NET 4.7.2 and higher • ODBC 17.2 (recent release)JDBC

25. Generally Available VNET Service Endpoints • Restrict access to your SQL Server from a given VNET/subnet Extends VNET to SQL PaaS: An app layer firewall, no messing with IPs Logical SQL Servers are restricted to be accessed from specific VNET(s)/Subnet(s)

26. PUBLIC PREVIEW Beginning H2 19 Private Link – Connectivity scenarios PaaS Services Peered network App Service Environment 3 2 IaaS hosted app Peering channel 6 IaaS hosted app Private Link subnet 7 App Service Environment 5 Gateway subnet Express Route / VPN Gateway “VNET Integrated” web app 4 1 On-prem app

27. Outlook on Roadmap

28. Roadmap • Always Encrypted with secure enclaves • SQL Server 2019 RTM • Working on enabling it in SQL Azure DB • Networking & Connectivity • Private Link (public preview) • Audit logging to firewall protected storage (public preview) • SQL MI, network requirements reduction • Active Directory Authentication • Logins for Azure Server Principals - Azure AD logins (GA) • Seamless Windows user migration (public preview)

29. Under Consideration • Separation of Duties • More built-in roles coming • RBAC Integration • Integration of Azure RBAC with SQL Data plane to enable seamless permission control from Portal • Advanced Data Security • Looking for your input: aka.ms/ADSSurvey19

30. We'd love your feedback! aka.ms/SQLBits19 Andreas Wolter LinkedInTwitter: @AndreasWolter

31. Resources & References Overview of Azure SQL DB Security – https://docs.microsoft.com/azure/sql-database/sql-database-security-overview SQL Advanced Data Security –https://docs.microsoft.com/azure/sql-database/sql-database-advanced-data-security SQL Information Protection – https://docs.microsoft.com/azure/sql-database/sql-database-data-discovery-and-classification SQL Vulnerability Assessment – https://docs.microsoft.com/azure/sql-database/sql-vulnerability-assessment SQL Threat Detection – https://docs.microsoft.com/azure/sql-database/sql-database-threat-detection

32. Azure - The Trusted Cloud More certifications than any other cloud provider GLOBAL CSA STAR Attestation CSA STAR Self-Assessment CSA STAR Certification ISO 27001 ISO 27018 ISO 27017 ISO 22301 SOC 1 Type 2 SOC 2 Type 2 SOC 3 US GOV DoD DISA SRG Level 5 Moderate JAB P-ATO High JAB P-ATO DoD DISA SRG Level 2 DoD DISA SRG Level 4 Section 508 VPAT SP 800-171 FIPS 140-2 CJIS ITAR IRS 1075 INDUSTRY GxP 21 CFR Part 11 HIPAA / HITECH Act Shared Assessments PCI DSS Level 1 IG Toolkit UK HITRUST MARS-E GLBA FFIEC FISC Japan FERPA MPAA FACT UK CDSA REGIONAL Argentina PDPA India MeitY Canada Privacy Laws EU Model Clauses China DJCP China GB 18030 Spain ENS Spain DPA Singapore MTCS Australia IRAP/CCSL New Zealand GCIO ENISA IAF Privacy Shield UK G-Cloud China TRUCS Japan CS Mark Gold Germany IT Grundschutz workbook Japan My Number Act