Security in 2016 Beyond Kevin Howell Microsoft Data

Security in 2016 & Beyond Kevin Howell Microsoft Data Platform Technical Solutions Professional September 2016

Intros About Me - Working with SQL Server since ver 6. 5 (really 7. 0) - DBA at local insurance company for 15 years - Now Data Platform TSP @ Microsoft® About You - Security Experts/Need of Security Experts - Goals/Expectations for next 2 hours - Specific Questions to Answer Follow-Ups - Presentation available @ http: //www. sqlpsi. com - Email me @ kevhowell@outlook. com

Agenda • Security Discussion • SQL 2016 Security Features Dynamic Data Masking • Row Level Security • Always Encrypted • Stretch Database (time allowing) • • Azure Security • Advanced Threat Detection

SQL Server 2016: Everything built-in 6 years in a row least vulnerable 80 built-in Industry leader built-in 70 least vulnerable 60 80 49 50 SQL Server 60 4943 50 34 SQL Server 40 29 30 34 2220 20 0 SQL Server 43 40 30 6 0 2 SQL Server 18 15 4 1 1 20 0 3 0 4 Oracle 5 5 My. SQL 10 SQL Server 3 3 #1 #2 20 22 $120 Oracle is #5 #3 6 15 Tableau Oracle Self-service BI per user TPC-H 5 In-memory across all workloads 0 2 22 Microsoft 18 4 1 1 $480 22 29 SAP HANA 6 0 0 R + in-memory $2, 230 69 70 10 A fraction of the cost #1 performance 6 years in a row built-in 69 3 at massive scale 3 0 3 4 5 6 Consistent experience from on-premises to cloud Oracle My. SQL SAP HANA The above graphics were published by Gartner, Inc. as part of a larger research document and should be evaluated in the context of the entire document. The Gartner document is available upon request from Microsoft. Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner's research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose. National Institute of Standards and Technology Comprehensive Vulnerability Database update 10/2015 TPC-H 10 TB non-clustered results as of 04/06/15, 5/04/15, 4/15/14 and 11/25/13, respectively. http: //www. tpc. org/tpch/results/tpch_perf_results. asp? resulttype=noncluster 4

SQL Server 2016 Improvements Performance Operational analytics Insights on operational data; Works with in-memory OLTP and disk-based OLTP In-memory OLTP enhancements Greater T-SQL surface area, terabytes of memory supported, and greater number of parallel CPUs Query data store Security Dynamic data masking Real-time obfuscation of data to prevent unauthorized access Row-level security Apply fine-grained access control to table rows Always encrypted Sensitive data remains encrypted at all times with ability to query Other enhancements Monitor and optimize query plans Audit success/failure of database operations Native JSON TDE support for storage of In. Memory OLTP tables Expanded support for JSON data Temporal database support Query data as points in time Enhanced auditing for OLTP with ability to track history of record changes Availability Enhanced Always. On Three synchronous replicas for auto failover across domains Round robin load balancing of replicas Automatic failover based on database health DTC for transactional integrity across database instances with Always. On Support for SSIS with Always. On Scalability Enhanced database caching Cache data with automatic, multiple Temp. DB files per instance in multi-core environments

Dynamic Data Masking

Dynamic Data Masking Prevent the abuse of sensitive data by hiding it from users • Configuration made easy in the new Azure portal • Policy-driven at the table and column level, for a defined set of users • Data masking applied in real-time to query results based on policy • Multiple masking functions available (e. g. full, partial) for various sensitive data categories (credit card numbers, SSN, etc. ) Security Table. Credit. Card. No 4465 -6571 -7868 -5796 4468 -7746 -3848 -1978 4484 -5434 -6858 -6550 SQL Database SQL Server 2016 Real-time data masking; partial masking

Dynamic Data Masking Walkthrough 2) App user data selects from Employee table 1) Dynamic Security officer defines dynamic data masking policydata in T-SQL sensitive 3) masking policy obfuscates the sensitive in theover query results data in Employee table ALTER TABLE [Employee] ALTER COLUMN [Social. Security. Number] ADD MASKED WITH (FUNCTION = ‘partial(0, ”XXX-XX-”, 4)’) ALTER TABLE [Employee] ALTER COLUMN [Email] ADD MASKED WITH (FUNCTION = ‘EMAIL()’) ALTER TABLE [Employee] ALTER COLUMN [Salary] ADD MASKED WITH (FUNCTION = ‘RANDOM(1, 20000)’) GRANT UNMASK to admin 1 SELECT [Name], [Social. Security. Number], [Email], [Salary] FROM [Employee]

DEMO Dynamic Data Masking

4 DDM Functions Default – masks entire data cell, based on data type Syntax: MASKED WITH (FUNCTION = default()‘) Email – shows 1 st letter of email & “. com” for end Syntax: MASKED WITH (FUNCTION = ‘email()‘) Random – use on numeric data to show random # in range Syntax: MASKED WITH (FUNCTION = 'random([start range], [end range])' Example (Month): MASKED WITH (FUNCTION = 'random(1, 12)') Custom – expose prefix & suffix, with custom padding for strings Syntax: MASKED WITH (FUNCTION = 'partial(prefix, [padding], suffix)' Example (SSN): MASKED WITH (FUNCTION = 'partial(0, "XXX-XX-", 4)')

Row Level Security

Row-Level Security (RLS) Protect data privacy by ensuring the right access across rows Customer 1 Fine-grained access control over specific rows in a database table Customer 2 • Help prevent unauthorized access when multiple users share the same tables, or to implement connection filtering in multitenant applications SQL Database • Administer via SQL Server Management Studio or SQL Server Data Tools • Enforcement logic inside the database and schema is bound to the table • Security Customer 3

Common Row-Level Security Use Cases Traditional RLS Workloads Custom business logic to determine which rows each user can SELECT, INSERT, UPDATE, and DELETE based on their role, department, and security level Target sectors: Finance, insurance, healthcare, energy, and government Multitenant Databases Ensuring tenants can only access their own rows of data in a shared database, with enforcement logic in the database rather than in the app tier For example: multitenant shards with elastic database tools in SQL Database Reporting, Analytics, and Data Warehousing Different users access same database through various reporting tools, and work with different subsets of data based on their identity/role Security

RLS in three steps Two Three App user. Policy Security (e. g. , transparently nurse) selectsrewrites from Patients query table to apply filter predicate Nurse Database Security Policy Patients Application SELECT * FROM Patients Policy Manager Filter Predicate: INNER JOIN… CREATE FUNCTION dbo. fn_securitypredicate(@wing int) RETURNS TABLE WITH SCHEMABINDING AS return SELECT 1 as [fn_securitypredicate_result] FROM SELECT * FROM Patients Staff. Duties d INNER JOIN Employees e SEMIJOIN APPLY dbo. fn_securitypredicate(patients. Wing); ON (d. Emp. Id = e. Emp. Id) WHERE e. User. SID = SUSER_SID() AND @wing = d. Wing; SELECT Patients. * FROM Patients, CREATE SECURITY POLICY dbo. Sec. Pol Staff. Duties d INNER JOIN Employees e ON (d. Emp. Id = e. Emp. Id) ADD FILTER PREDICATE dbo. fn_securitypredicate(Wing) ON Patients WHERE e. User. SID = SUSER_SID() AND Patients. wing = d. Wing; WITH (STATE = ON) Security

DEMO Row Level Security

Row Level Security - Predicate CREATE SCHEMA <<SCHEMA>>; --BEST PRACTICE TO CREATE UNIQUE SCHEMA FOR RLS GO CREATE FUNCTION <<SCHEMA>>. <<PREDICATE FUNCTION>>(<<ACCOUNT PARAMETER>> AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS <<PREDICATE RESULT SET>> WHERE <<ACCOUNT PARAMETER>> = USER_NAME() OR USER_NAME() = <<ELEVATED USER ACCOUNT>>; EXAMPLE CREATE SCHEMA Security; GO CREATE FUNCTION Security. fn_securitypredicate(@Sales. Rep AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @Sales. Rep = USER_NAME() OR USER_NAME() = 'Manager';

Row Level Security - Policy CREATE SECURITY POLICY <<POLICY NAME>> ADD FILTER PREDICATE <<SCHEMA NAME>>. <<PREDICATE NAME>>(<<ACCOUNT PARAMETER>>) ON <<TABLE NAME>> WITH (STATE = ON); EXAMPLE CREATE SECURITY POLICY Sales. Filter ADD FILTER PREDICATE Security. fn_securitypredicate(Sales. Rep) ON dbo. Sales WITH (STATE = ON);

Always Encrypted

Protect data at rest and in motion, both on-premises and in the cloud Data remains encrypted during query Apps Encrypted query TDE-enabled ADO. NET library SQL Server Transparent client-side encryption, while SQL Server executes T-SQL queries on encrypted data Benefits No app changes Master key Capability Encrypted columnar key Æ Sensitive data remains encrypted and queryable at all times, both on-premises and in the cloud Æ Unauthorized users never have access to data or keys Æ No changes to applications are necessary Most secure database Always Encrypted: Overview

Most secure database Always Encrypted: How It Works without impacting database performance Always Encrypted Apps NEW SQL Server Trusted SELECT Name FROM Patients WHERE SSN=@SSN Client side SELECT Name FROM Patients WHERE SSN=@SSN='198 -33 -0987' Column Master Key Query @SSN=0 x 7 ff 654 ae 6 d Enhanced ADO. NET Library Result Set Name 198 -33 -0987 SSN Column Encryption Key Country Denny Usher 0 x 7 ff 654 ae 6 d USA ciphertext dbo. Patients Name SSN Country Philip Wegner 1 x 7 fg 655 se 2 e 243 -24 -9812 USA Denny Usher 198 -33 -0987 USA 0 x 7 ff 654 ae 6 d Alicia Hodge 123 -82 -1095 USA 0 y 8 fj 754 ea 2 c

Types of Encryption for Always Encrypted Randomized encryption Encrypt('123 -45 -6789') = 0 x 17 cfd 50 a Repeat: Encrypt('123 -45 -6789') = 0 x 9 b 1 fcf 32 Allows for transparent retrieval of encrypted data but NO operations More secure Deterministic encryption Encrypt('123 -45 -6789') = 0 x 85 a 55 d 3 f Repeat: Encrypt('123 -45 -6789') = 0 x 85 a 55 d 3 f Allows for transparent retrieval of encrypted data AND equality comparison E. g. in WHERE clauses and joins, distinct, group by Security Two types of encryption available Randomized encryption uses a method that encrypts data in a less predictable manner Deterministic encryption uses a method which always generates the same encrypted value for any given plaintext value

Key Provisioning 1. Generate CEKs and master key Column encryption key (CEK) Encrypted CEK 2. Encrypt CEK Security Officer 3. Store master key securely 4. Upload encrypted CEK to DB Security Column master key (CMK) CMK store: Certificate store HSM Azure Key Vault … CMK Encrypted CEK Database

DEMO Always Encrypted

Always Encrypted Notes Desktop or Web Application Changes Add “Column Encryption Setting=enabled; ” to connection string Parameters must be explicitly/exactly defined Encryption type Deterministic – Use if JOINS are needed for encrypted data. Identical data encrypted similarly Randomized – Use if JOINS are not needed. All data is encrypted uniquely Key Stores Windows Certificate Store – localized key store (on-prem use cases) Azure Key Vault – centralized key store for (web or hybrid use cases)

SQL Threat Detection

SQL Threat Detection What is it? - New security intelligence feature built into Azure SQL Database service Profiles and detect anomalous database activities Identifies potential threats to the database Delivers immediate notifications about suspicious database activities as they occur Recommends how to further investigate and mitigate threat. What does it track/prevent? - Detects potential vulnerabilities and SQL injection attacks - Tracks anomalous database access patterns Use Cases - Attempted database access by former employee - Preventing SQL Injection attacks - Anomalous access from home to production database

Resources Dynamic Data Masking On-Prem: https: //msdn. microsoft. com/en-us/library/mt 130841. aspx Azure: https: //azure. microsoft. com/en-us/documentation/articles/sqldatabase-dynamic-data-masking-get-started/ Row Level Security https: //msdn. microsoft. com/en-us/library/dn 765131. aspx Always Encrypted https: //msdn. microsoft. com/en-us/library/mt 163865. aspx https: //msdn. microsoft. com/en-us/library/mt 723359. aspx SQL Threat Detection (Azure) https: //blogs. msdn. microsoft. com/sqlsecurity/2016/08/08/sql-threatdetection-your-built-in-security-expert/

Questions