Whats new in SQL Server and Azure SQL
- Slides: 32
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) 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
Advanced Data Security ü Data ü Vulnerability ü Advanced Threat
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
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 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 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 Database ü Rich set of tools for (1) Turn on SQL Auditing (2) Analyze audit log • Investigating access
Advanced Data Security Demo
Gain security insights via Log Analytics and Power BI dashboards
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 - Challenges
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 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? • 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
Always Encrypted with Secure Enclaves plaintext Enclave
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
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 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. 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
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 • 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
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 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
- Azure secure enclave
- マイクロソフト データベース
- Ssis-314
- Azue sql server stretch monitoring
- Csp azure plan
- Azure sql data warehouse smp
- Azure server side encryption
- Multifactor authentication server
- Azure cdc
- Azure sql database benchmark
- Azure sql data warehouse dwu
- Mpp architecture azure
- Azure vertical scaling
- Azure vm sla
- Azure sql edge raspberry pi
- Sql azure reporting
- Visual fox pro 6
- Sqlhammer
- Azure sql scalability
- Azure sql high availability
- Using r in azure
- Azure cosmos db: sql api deep dive online courses
- Azure database security
- Azure sql graph
- Azure sql dtu limits
- Azure clour
- Sql server high availability and disaster recovery
- Sql server high availability and disaster recovery
- Ssms tips and tricks
- Sql server internals and architecture
- Difference between sql and plsql
- Iometer vmware
- Ms sql security best practices