Transcript SQL Server 2016 Security Features HOL Overview

SQL SERVER 2016
HANDS-ON LABS:
EXPLORING NEW
SECURITY FEATURES
Timothy P. McAliley
CISA, CISM, CISSP, ITIL V3, 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 (DoD
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
Security
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
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.CreditCardNo
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
x86 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 SP3 or later
• SQL Server 2008 R2 SP2 or later
• SQL Server 2012 SP1 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 AlwaysOn
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 AlwaysOn
Real-time obfuscation of data to
prevent unauthorized access
Native JSON
TDE support for storage of InMemory OLTP tables
Query data as points in time
Automatic failover based on
database health
DTC for transactional integrity
across database instances with
AlwaysOn
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 TempDB 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
SQL Server 2016 Upgrade Advisor in the
adoption of new SQL Server features
Simplified Add Azure
Replica Wizard
Automatic listener configuration for AlwaysOn
in Azure VMs
Consistency
Common development,
management, and identity tools
Including Active Directory, Visual Studio, HyperV, and System Center
Consistent experience from SQL
Server on-premises to Microsoft
Azure IaaS and PaaS
Deeper insights across data
Access any data
PolyBase
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 15x 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, iOS,
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
SQL Server or SQL Database
Client
"SELECT Name FROM Customers WHERE SSN = @SSN",
0x7ff654ae6d
"SELECT Name FROM Customers WHERE SSN = @SSN",
"111-22-3333"
Result Set
ciphertext
ADO .NET
Result Set
Name
Name
Wayne Jefferson
0x19ca706fbd9a
dbo.Customers
trust boundary
Name
SSN
Country
0x19ca706fbd9a
0x7ff654ae6d
USA
ciphertext
Security
Types of encryption for Always Encrypted
Randomized encryption
Encrypt('123-45-6789') = 0x17cfd50a
Repeat: Encrypt('123-45-6789') = 0x9b1fcf32
Allows for transparent retrieval of encrypted
data but NO operations
More secure
Deterministic encryption
Encrypt('123-45-6789') = 0x85a55d3f
Repeat: Encrypt('123-45-6789') = 0x85a55d3f
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
Security
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
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 (iTVF) 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 mySecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(wing, startTime, endTime)
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
StaffDuties d INNER JOIN Employees e
ON (d.EmpId = e.EmpId)
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.UserSID = SUSER_SID()
AND @wing = d.Wing;
CREATE SECURITY POLICY dbo.SecPol
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.CreditCardNo
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.CreditCardNo
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/mt130841.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 [SocialSecurityNumber]
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 admin1
SELECT [Name],
[SocialSecurityNumber],
[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
Prediction

Relational
Non-relational
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.