SQL SERVER 2016 HANDSON LABS EXPLORING NEW SECURITY

SQL SERVER 2016 HANDS-ON LABS: EXPLORING NEW SECURITY FEATURES Timothy P. Mc. Aliley CISA, CISM, CISSP, ITIL V 3, MCSA, MCSE, MCT, PMP Microsoft Account Technology Strategist

A bit about me……. • 17 years in IT, mostly in IT Operations, Information Assurance • Microsoft for about 4 years, 1 in Sale Engineer Role, 3 in Premier Field Engineer Role • Previously worked for Symantec, Fanatics. com, ASM Research (Do. D Consulting Firm) • Florida State Alum, Wife – Stephanie, two daughters – Riley (13) and Harper (3)

Ok…. so why are we doing this? • Knowledge transfer • Learning though “doing” • Resource awareness: • Over 30 SQL Server Labs • https: //technet. microsoft. com/en-us/virtuallabs • Free, web-based, repeatable • Downloadable lab books • Nothing to install • Don’t want to do the labs tonight? No worries – just observe, ask questions, etc. You can do the labs anytime

Logistics • Wifi/Wireless Access – refer to the information sheet • URL – https: //technet. microsoft. com/en-us/virtuallabs • Labs – • SQL Server 2016 – Always Encrypted • SQL Server 2016 – Row-Level Security / Dynamic Data Masking • SQL Server 2016 – Organizational Security and Auditing

Summary: Always Encrypted Protect data at rest and in motion, on-premises and in the cloud Data remains encrypted during query Apps No app changes Master key Encrypted query TCE-enabled ADO. NET library SQL Server Capability ADO. Net client library provides transparent client-side encryption, while SQL Server executes T-SQL queries on encrypted data Benefits Columnar key Sensitive data remains encrypted and queryable at all times on-premises and in the cloud Unauthorized users never have access to data or keys No application changes Security

The need for row-level security 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

Dynamic data masking Prevent the abuse of sensitive data by hiding it from users Table. Credit. Card. No 4465 -6571 -7868 -5796 4468 -7746 -3848 -1978 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 4484 -5434 -6858 -6550 SQL Database SQL Server 2016 Real-time data masking; partial masking

SQL Server 2016 – Organizational Security and Auditing • Audit resilience. Ensures that audit logs are not lost in failover during temporary file and network issues. • User-defined audit. Creates events that allow applications to write custom information to the audit log. • Audit filtering. Improves filtering to simplify audit reporting.

Microsoft positioned as a leader in the Gartner Magic Quadrant for Operational Database Management Systems* { Microsoft is placed furthest in vision and ability to execute within the leaders’ quadrant *Gartner “Magic Quadrant for Operational Database Management Systems, ” by Donald Feinberg , Merv Adrian , Nick Heudecker, Adam Ronthal, October 2015 } This graphic was 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.

SQL Server is an ecosystem of products and services, both on-premises and in the cloud Operational Database Management Systems Data Warehouse and Database Management Systems Business Intelligence and Analytics Platforms x 86 Server Virtualization Cloud Infrastructure as a Service Enterprise Application Platform as a Service Public Cloud Storage Services

SQL Server 2016 Upgrade Paths • SQL Server 2016 supports upgrade from the following versions of SQL Server: • SQL Server 2008 SP 3 or later • SQL Server 2008 R 2 SP 2 or later • SQL Server 2012 SP 1 or later • SQL Server 2014 or later

SQL Server 2016 Editions

SQL Server 2016 Licensing

2016 Overview Agenda

Mission-critical performance 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 Always encrypted Enhanced Always. On Row-level security Round-robin load balancing of replicas Sensitive data remains encrypted at all times with ability to query Apply fine-grained access control to table rows Other enhancements Support for SSIS with Always. On Real-time obfuscation of data to prevent unauthorized access Native JSON TDE support for storage of In. Memory OLTP tables Query data as points in time Automatic failover based on database health DTC for transactional integrity across database instances with Always. On Audit success/failure of database operations Temporal database support Three synchronous replicas for automatic failover across domains Dynamic data masking Monitor and optimize query plans Expanded support for JSON data Availability Enhanced auditing for OLTP with ability to track history of record changes Scalability Enhanced database caching Cache data with automatic, multiple Temp. DB files per instance in multicore environments

Hyperscale cloud Hybrid solutions Stretch Database Stretch operational tables in a secure manner into Azure for cost-effective historic data availability. Works with Always Encrypted and row-level security Power BI with on-premises data New interactive query with Analysis Services. Customer data stays behind your firewall Enhanced backup to Azure Faster restore times and 50% reduction in storage. Supports larger DBs with block blobs and custom backup schedule with local staging Simplicity Easy migration of on-premises SQL Server Simple point-and-click migration to Azure Suite of advisors for upgrading to SQL Server 2016 Upgrade Advisor in the adoption of new SQL Server features Simplified Add Azure Replica Wizard Automatic listener configuration for Always. On in Azure VMs Consistency Common development, management, and identity tools Including Active Directory, Visual Studio, Hyper. V, and System Center Consistent experience from SQL Server on-premises to Microsoft Azure Iaa. S and Paa. S

Deeper insights across data Access any data Poly. Base Insights from data across SQL Server and Hadoop with the simplicity of T-SQL Enhanced SSIS Designer support for previous SSIS versions SSIS Improvements for Azure services Scale and manage Enterprise-grade Analysis Services Enhanced performance and scalability for Analysis Services Single SSDT in Visual Studio 2015 Build richer analytics solutions as part of your development projects in Visual Studio Enhanced MDS Excel add-in 15 x faster; more granular security roles; archival options for transaction logs; and reuse entities across models Powerful insights Mobile BI Business insights for your onpremises data through rich visualization on mobile devices with native apps for Windows, i. OS, and Android Enhanced Reporting Services New modern reports with rich visualizations Advanced analytics R integration Bringing predictive analytic capabilities to your relational database Expand your “R” script library with Microsoft Azure Marketplace

Always Encrypted

The need for Always Encrypted Prevents data disclosure Queries on encrypted data Application transparency Client-side encryption of sensitive data using keys that are never given to the database system Support for equality comparison, including join, group by, and distinct operators Minimal application changes via server and client library enhancements Allows customers to securely store sensitive data outside of their trust boundary. Data remains protected from high-privileged, yet unauthorized users. Security

How it works Help protect data at rest and in motion, on-premises and in the cloud Encrypted sensitive data and corresponding keys are never seen in plaintext in SQL Server or SQL Database Client "SELECT Name FROM Customers WHERE SSN = @SSN", 0 x 7 ff 654 ae 6 d "SELECT Name FROM Customers WHERE SSN = @SSN", "111 -22 -3333" Result Set ciphertext ADO. NET Result Set Name Wayne Jefferson 0 x 19 ca 706 fbd 9 a dbo. Customers trust boundary Name SSN Country 0 x 19 ca 706 fbd 9 a 0 x 7 ff 654 ae 6 d USA ciphertext Security

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

Summary: Always Encrypted Protect data at rest and in motion, on-premises and in the cloud Data remains encrypted during query Apps No app changes Master key Encrypted query TCE-enabled ADO. NET library SQL Server Capability ADO. Net client library provides transparent client-side encryption, while SQL Server executes T-SQL queries on encrypted data Benefits Columnar key Sensitive data remains encrypted and queryable at all times on-premises and in the cloud Unauthorized users never have access to data or keys No application changes Security

Row-level security SQL Server 2016 SQL Database

The need for row-level security 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

Benefits of row-level security (RLS) Fine-grained access control Application transparency Centralized security logic Keeping multitenant databases secure by limiting access by other users who share the same tables RLS works transparently at query time, no app changes needed Enforcement logic resides inside database and is schema-bound to the table it protects providing greater security. Reduced application maintenance and complexity Compatible with RLS in other leading products Store data intended for many consumers in a single database/table while at the same time restricting row-level read and write access based on users’ execution context. Security

RLS concepts Performance? Inline functions get optimized to provide comparable performance to views, as if the logic were directly embedded in the original query statement Predicate function User-defined inline table-valued function (i. TVF) implementing security logic Can be arbitrarily complicated, containing joins with other tables Security predicate Binds a predicate function to a particular table, applying it for all queries Two types: filter predicates and blocking predicates (coming soon) Security policy Collection of security predicates for managing security across multiple tables CREATE SECURITY POLICY my. Security. Policy ADD FILTER PREDICATE dbo. fn_securitypredicate(wing, start. Time, end. Time) ON dbo. patients Security

Example CREATE FUNCTION dbo. fn_securitypredicate(@wing int) RETURNS TABLE WITH SCHEMABINDING AS return SELECT 1 as [fn_securitypredicate_result] FROM Staff. Duties d INNER JOIN Employees e ON (d. Emp. Id = e. Emp. Id) Fine-grained access control over rows in a table based on one or more pre-defined filtering criteria, such as user’s role or clearance level in organization WHERE e. User. SID = SUSER_SID() AND @wing = d. Wing; CREATE SECURITY POLICY dbo. Sec. Pol ADD FILTER PREDICATE dbo. fn_securitypredicate(Wing) ON Patients WITH (STATE = ON) Security Concepts: Predicate function Security policy

Common RLS 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

Summary: RLS Capability Row-level security provides fine-grained access control over rows in a table based on conditions you set up Benefits Store data for many users in the same databases and tables while limiting access by other users who share the same tables Security

Dynamic data masking SQL Server 2016 SQL Database

Dynamic data masking Prevent the abuse of sensitive data by hiding it from users Table. Credit. Card. No 4465 -6571 -7868 -5796 4468 -7746 -3848 -1978 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 4484 -5434 -6858 -6550 SQL Database SQL Server 2016 Real-time data masking; partial masking

Benefits of dynamic data masking Regulatory compliance Sensitive data protection Agility and transparency Data is masked on the fly, with underlying data in the database remaining intact. Transparent to the application and applied according to user privilege Limit access to sensitive data by defining policies to obfuscate specific database fields, without affecting the integrity of the database. Security

How it works Table. Credit. Card. No Limit sensitive data exposure by obfuscating it to non-privileged users On-the-fly obfuscation of data in query results Policy-driven on the table and column Multiple masking functions available for various sensitive data categories Flexibility to define a set of privileged logins for un-masked data access By default, database owner is unmasked See: https: //msdn. microsoft. com/en-us/library/mt 130841. aspx Security 4465 -6571 -7868 -5796 4468 -7746 -3848 -1978 4484 -5434 -6858 -6550 Azure DB Dynamic masking On-the-fly masking of sensitive data in query results

Dynamic data masking walkthrough 1) Security officer defines dynamic data masking policy in T-SQL over sensitive data in Employee table 2) Application user selects from Employee table 3) Dynamic data masking policy obfuscates the sensitive data in the query results ALTER TABLE [Employee] ALTER COLUMN [Social. Security. Number] ADD MASKED WITH (FUNCTION = ‘SSN()’) 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] Security

Summary: dynamic data masking Capability Protects against unauthorized disclosure of sensitive data in the application Benefits Enables you to set up policies at the table and column level that provide multiple masking functions Allows certain privileged logins to see the data unmasked Security

The Microsoft data platform Apps Reports Dashboards Ask Mobile Microsoft Azure Orchestration Extract, transform, load Information management {} Relational Non-relational Prediction Analytical Streaming Internal and external

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.