Whats new in SQL Server and Azure SQL

  • Slides: 32
Download presentation
What’s new in SQL Server and Azure SQL Database Security Andreas Wolter andreas. wolter@microsoft.

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

Enterprise Grade Security that is Easy-to Use Data Protection Encryption-in-flight (Transport Layer Security TLS)

Enterprise Grade Security that is Easy-to Use Data Protection 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 Access Control SQL Permissions Row-level security Column-level security Authentication SQL Authentication Azure Active Directory Authentication (w/ MFA) Network Security Threat Protection Virtual Networks SQL Firewall (server- and database-level) Data Classification Advanced Threat Protection Auditing Vulnerability Assessment

Data Protection & Threat Protection

Data Protection & Threat Protection

Advanced Data Security ü Data ü Vulnerability ü Advanced Threat

Advanced Data Security ü Data ü Vulnerability ü Advanced Threat

SQL Data Classification access ü Automatic with sensitive data ü Add and sensitive data

SQL Data Classification access ü Automatic with sensitive data ü Add and sensitive data of columns sensitive data labels access to the for your entire Azure tenant using Azure Security Center

Data Discovery & Classification Demo

Data Discovery & Classification Demo

SQL Vulnerability Assessment ü Identify remediation steps environment tuned to your ü Manual/periodic ü

SQL Vulnerability Assessment ü Identify remediation steps environment tuned to your ü Manual/periodic ü Coherent

Advanced Threat Protection (2) Possible threat to access / breach data Apps Audit Log

Advanced Threat Protection (2) Possible threat to access / breach data Apps Audit Log Azure SQL Database Threat Detection (1) Turn on Threat Detection (3) Real-time actionable alerts

Advanced Threat Protection Suite • SQLi attempt - An application generated a faulty SQL

Advanced Threat Protection Suite • 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. • 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 • 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…)

SQL Auditing in Log Analytics and Event Hubs Storage account Audit Log Azure SQL

SQL Auditing in Log Analytics and Event Hubs Storage account Audit Log Azure SQL Database ü Rich set of tools for (1) Turn on SQL Auditing (2) Analyze audit log • Investigating access

Advanced Data Security Demo

Advanced Data Security Demo

Gain security insights via Log Analytics and Power BI dashboards

Gain security insights via Log Analytics and Power BI dashboards

SQL ADS Roadmap Centralized Management Full Hybrid support Azure Security Center manages ADS across

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

Always Encrypted in use

Always Encrypted in use

Always Encrypted - Challenges

Always Encrypted - Challenges

Always Encrypted using Secure Enclaves CTP Protects sensitive data in use while enabling rich

Always Encrypted using Secure Enclaves CTP Protects sensitive data in use while enabling rich computations and in-place encryption Confidential computing brings secure enclaves Enhanced Client Driver plaintext • 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 ciphertext SQL enclave

Confidential Computing using Enclaves Enclave – an isolated region of memory Provides a trusted

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 App Operating System Hypervisor Hardware Code Data

Enclave Attestation and Secure Tunnel How do you know the SQL enclave is trustworthy?

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? secure tunnel • Answer: secure tunnel Enhanced Client Driver plaintext ciphertext SQL Enclave

2 3 Always Encrypted with Enclaves in Screenshots

2 3 Always Encrypted with Enclaves in Screenshots

Always Encrypted with Secure Enclaves plaintext Enclave

Always Encrypted with Secure Enclaves plaintext Enclave

Next Steps Try it now in SQL Server 2019 Preview! Tutorial: https: //aka. ms/Always.

Next Steps Try it now in SQL Server 2019 Preview! Tutorial: https: //aka. ms/Always. Encrypted. Enclaves. Tutorial Documentation: https: //aka. ms/Always. Encryptedwith. Secure. Enclaves Tutorial: Getting started with Always Encrypted with secure enclaves using SSMS Blog: https: //blogs. msdn. microsoft. com/sqlsecurity/tag/alwaysencrypted/

Access Management

Access Management

Azure Active Directory Authentication - Interactive Support for Multi-Factor Authentication (MFA) All the benefits

Azure Active Directory Authentication - Interactive Support for Multi-Factor Authentication (MFA) All the benefits of Azure Active Directory Authentication plus: 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) Flexible Configuration Conditional Access for configuring domain accounts for MFA Can impose MFA without asking domain administrator to make global change Supported in many Tools and Drivers SSMS (since 17. 2+) Dac. Fx 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

VNET Service Endpoints Restrict access to your SQL Server from a given VNET/subnet Extends

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

Private Link – Connectivity scenarios 3 App Service Environment 6 Peering channel 2 Iaa.

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

Outlook on Roadmap

Outlook on Roadmap

Roadmap • Always Encrypted with secure enclaves • SQL Server 2019 RTM • Working

Roadmap • Always Encrypted with secure enclaves • SQL Server 2019 RTM • Working on enabling it in SQL Azure DB • Networking & Connectivity Thank You • 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)

Under Consideration • Separation of Duties • More built-in roles coming Thank You •

Under Consideration • Separation of Duties • More built-in roles coming Thank You • 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/ADSSurvey 19

We'd love your feedback! aka. ms/SQLBits 19 Andreas Wolter Linked. In Twitter: @Andreas. Wolter

We'd love your feedback! aka. ms/SQLBits 19 Andreas Wolter Linked. In Twitter: @Andreas. Wolter

Resources & References Overview of Azure SQL DB Security – https: //docs. microsoft. com/azure/sql-database-security-overview

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

GLOBAL More certifications than any other cloud provider REGIONAL INDUSTRY US GOV ISO 27001

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