SQL Server 2016 New Security Features Gianluca Sartori

SQL Server 2016 New Security Features Gianluca Sartori @spaghettidba #SQLSAT 454

Sponsors #SQLSAT 454

Gianluca Sartori § Independent SQL Server consultant § SQL Server MVP, MCTS, MCITP, MCT § Works with SQL Server since version 7 § DBA @ Scuderia Ferrari § Blog: § Twitter: #SQLSAT 454 spaghettidba. com @spaghettidba

Agenda § § Security Boundaries Always Encrypted Row Level Security Dynamic Data Masking #SQLSAT 454

Why New Security Features? § SQL Server has plenty security features § TDE § Protects database files and backups at rest § Cell-Level Encryption § Encrypts single values in database tables § SSL § Protects data on the network #SQLSAT 454

Security Boundaries – Open Apps Database Manager User SSMS Developer DBA Software Vendor #SQLSAT 454 Unauthorized Users

Security Boundaries – Non Sensitive Apps Software Vendor Database Manager User Copy SSMS DBA Copy Developer #SQLSAT 454 Unauthorized Users

Security Boundaries – Sensitive Apps Software Vendor Database Manager User Copy SSMS DBA Copy Developer #SQLSAT 454 Unauthorized Users

ALWAYS ENCRYPTED #SQLSAT 454

Always Encrypted – Key Features Prevents Data Disclosure Queries on Encrypted Data Application Transparency End-to-end encryption of individual columns in a table with keys that are never given to the database system. Support for equality comparison, incl. join, group by and distinct operators. Minimal application changes via server and client library enhancements. #SQLSAT 454

Always Encrypted § Sensitive data is encrypted at column level § Data is protected from high-privileged users § DBAs § System Admins § Hackers § Cloud providers § Third-parties § Data is stored securely outside security boundaries § The database never sees unencrypted data #SQLSAT 454

Always Encrypted – How it works App SELECT Name FROM Patients WHERE SSN=@SSN Column Master Key Query SELECT Name FROM Patients WHERE SSN=@SSN=0 x 7 ff 654 ae 6 d SQL Server Enhanced Native Client ADO. NET 4. 6 Library @SSN='198 -33 -0987' dbo. Patients Result Set Name Jim Gray Application - Trusted #SQLSAT 454 Column Encryption Key Name SSN Country Jane Doe 1 x 7 fg 655 se 2 e USA Jim Gray 0 x 7 ff 654 ae 6 d USA John Smith 0 y 8 fj 754 ea 2 c USA SQL Server - Untrusted

Encryption Types § Deterministic Encryption Same plaintext value Same encrypted value Supports indexing, equality comparison, JOINs, DISTINCT § Randomized Encryption Same plaintext value Different encrypted value Supports retrieval of encrypted data No SQL operations supported #SQLSAT 454

Working with Always Encrypted DEMO #SQLSAT 454

TDE vs Always Encrypted Column level Client encryption Server doesn’t know encryption keys TDE Database level Server encryption Server knows encryption keys Data in memory is encrypted Data in memory is in plaintext Data travels the network in encrypted plaintext #SQLSAT 454

Custom encryption vs Always Encrypted Custom Encryption Slight application changes Needs obtrusive changes Disallows saving plaintext data Plaintext data can be saved by accident Allows indexing of cyphertext * * depending on encryption algorithm #SQLSAT 454

Always Encrypted - Limitations § § Deterministic encryption needs _BIN 2 collation Not all datatypes supported Partial support for triggers Unsupported features: § § § Full-text search Replication Change Data Capture In-Memory OLTP Stretch Database #SQLSAT 454

What changes for Applications? § Connection. String must include new key: Column Encryption Setting=enabled; § Ad-hoc queries not supported SELECT Some. Column FROM Some. Table WHERE Encryped. Column = 'Some. Value'; § Needs correctly parameterized queries SELECT Some. Column FROM Some. Table WHERE Encryped. Column = @param; #SQLSAT 454

Always Encrypted for Existing Data § Existing columns must be encrypted client side § Easiest way: Import / Export wizard Ad-hoc wizard In SSMS 2016? #SQLSAT 454

Performance Impact #SQLSAT 454

Space Usage Impact #SQLSAT 454

Q&A Questions? #SQLSAT 454

DYNAMIC DATA MASKING #SQLSAT 454

Dynamic Data Masking – Key Features Limits Sensitive Data Exposure Useful for Compliance Application Transparency Sensitive data is masked. Administrators designate how much of the sensitive data to reveal. Helps adhering to privacy standards imposed by regulation authorities. No application changes. Existing queries keep working. #SQLSAT 454

Dynamic Data Masking Unmasked Data SSN DATA 062 -56 -4651 Database Privileged User Masked Data SSN XXX-XX-XXXX Non-Privileged User #SQLSAT 454 dbo. Employees Name SSN Salary Jane Doe 062 -56 -4651 2. 500 Jim Gray 915 -12 -9845 2. 350 John Smith 354 -21 -9184 1. 500

Dynamic Data Masking § Obfuscates data using 3 masking functions § Default: depends on data type § Email: a. XXXX. com § Partial: prefix. XXXXXXsuffix § Data is stored unmasked § Masking happens on resultset formation § GRANT UNMASK to disclose data § Works in Azure SQL Database (preview) #SQLSAT 454

Dynamic Data Masking - Limitations § Not all datatypes supported § Not intended as a complete protection feature for sensitive data § Ad-Hoc queries disclose data. Ex: WHERE Salary > 2000 § INSERT…SELECT does not preserve masking § Some quirks § Not suitable for handing out copies of the database to software vendors or third-parties #SQLSAT 454

Working with Dynamic Data Masking DEMO #SQLSAT 454

Q&A Questions? #SQLSAT 454

ROW-LEVEL SECURITY #SQLSAT 454

Row Level Security – Key Features Fine-grained access control Centralized Security Logic Application Transparency In multi-tenant databases, limits access by other users who share the same tables. Predicate-based access control logic resides inside the database and is schema-bound to the tables it protects. No application changes. Existing queries keep working. #SQLSAT 454

Row-Level Security EMEA Salesperson Manager LATAM Salesperson APAC Salesperson #SQLSAT 454 dbo. Customer Name Area Budget Evil Inc. EMEA 2. 500 Wealthy Corp. LATAM 2. 350 Greedy Corp. APAC 1. 500

Row-Level Security - Concepts § Predicate function User-defined inline i. TVF implementing access control logic. Can be arbitrarily complicated § Security predicate Applies a predicate function to a particular table (APPLY) Two types: filter predicates and blocking predicates § Security policy Collection of security predicates Manages security across multiple tables #SQLSAT 454

Row-Level Security – How it works dbo. Customer Name Area Budget Evil Inc. EMEA 2. 500 Wealthy Corp. LATAM 2. 350 Greedy Corp. APAC 1. 500 Security Policy SELECT * FROM Customer EMEA Salesperson #SQLSAT 454 SELECT * FROM Customer APPLY itvf_security. Predicate() DBA

Working with Row-Level Security DEMO #SQLSAT 454

Row-Level Security - Limitations § SCHEMABINDING: all tables in the predicate function must reside in the database § Performance impact: queries are rewritten § When authenticating the application, CONTEXT_INFO() can be used to filter on real user § Not really secure if users can run ad-hoc queries § Don’t lock out the DBA! #SQLSAT 454

Q&A Questions? #SQLSAT 454

Resources Always Encrypted on MSDN Getting Started With Always Encrypted Performance impact of Always Encrypted Dynamic Data Masking on MSDN Using Dynamic Data Masking Row-Level Security on MSDN Introduction to Row-Level Security Limitations #SQLSAT 454

Evaluations § Don’t forget to compile evaluations form here § http: //speakerscore. com/sqlsat 454 #SQLSAT 454

#sqlsat 454 THANKS! #SQLSAT 454