Fast Start for Microsoft Azure – SQL Server IaaS Workshop - PowerPoint Presentation

Slide1

Fast Start for Microsoft Azure – SQL Server IaaS WorkshopSlide2

How to View This Presentation

Switch to Notes Page view:

Click View on the ribbon and select Notes PageUse page up or page down to navigateZoom in or out as neededIn the Notes Page view you can:Read any supporting text, now or after the deliveryAdd your own notesTake the presentation files home with you

2Slide3

Section 5:

SQL Server Security

Lesson 1: OverviewSecurity Best Practices3Slide4

Attack Types

4

Password crackingSpoofing

Credential theft

Network eavesdropping

Brute force attacks

Dictionary attacksSlide5

Azure IaaS Virtual Machine Security Recommendations

5Slide6

Azure IaaS Virtual Machine Security Recommendations

6Slide7

Lesson Knowledge Check

Question: How can dictionary attacks be prevented?

Answer: Restrict and protect accounts Give administrators dedicated admin accountsQuestion: How can credential theft be prevented? Answer:Mark privileged accounts as “sensitive and cannot be delegated” in AD DS Do not surf the web from a data center Do not check email from a data centerSlide8

Section 5:

SQL Server Security

Lesson 2: SQL Server FeatureSecurity Features8Slide9

SQL Server Audit

SQL Server Audit is a mechanism that allows you setup logging and tracking of certain security events in order to have a digital audit of the desired and required events

SQL Server Audit – think of this as the destination and behavior of what you’re auditingServer Audit Specification – instance-level actionsDatabase Audit Specification – database-level actions9Slide10

How does SQL Server use Certificates?

Certificate

server - SQL Server can be used to issue certificates to users within the server for authentication and encryption purposesService Broker - Service Broker can use secure, authenticated communication between two services in a conversation. Code authentication – SQL Server can use a certificate in a database to add a digital signature to a database object such as a stored procedure or function. This is a common way to guarantee the authenticity of a code module.Data encryption – SQL Server can use a certificate to encrypt or decrypt data. However, be aware that certificate-based cryptography incurs greater performance overhead than cryptography with a symmetric key.

10Slide11

Data Encryption with SQL Server

Concept of Defense in Depth

SQL Server offers encryption at the cell level and at file level (data at rest) by using TDECell level encryption is implemented as a series of built-ins and a key management hierarchyIt is a manual process that requires a re-architecture of the application to call the encryption and decryption functionsIt also requires the schema to be modified to store the data as varbinary and then re-cast back to the appropriate data typeCannot use any of the automatic query optimization techniques11Slide12

Transparent Data Encryption

Performs all of the cryptographic operations at the database level

Removes any need for application developers to create custom code to encrypt and decrypt data Data is encrypted as it is written to disk, and decrypted as it is read from disk As SQL Server manages encryptionAnd decryption transparently, there isno need for application changes12Slide13

Encryption hierarchy – enabling TDE

Encryption Hierarchy

Service Master Key Database Master Key Certificates Database Encryption Key (DEK)13Slide14

Transparent Data Encryption, how it works

Entire database is encrypted

Protects data at rest, files, backups are unusable without the certificateWorks at storage I/O level (encryption at rest)Encryption happens before writing to disk, and performed by background threadsPage protection (Checksum/torn page) is applied after encryptionPage protection (e.g. checksums) is checked before decryptionDatabase pages are decrypted when read into memoryTempdb is also encrypted by defaultTo check the status of encryption use the encryption_state column in DMV sys.dm_database_encryption_keys Backups are also encrypted for TDE enabled database.14Slide15

An unauthorized user obtains the backup tapes for a database

A discontent employee copies the database files and takes them to a competitor

A lost or stolen laptop contains SQL Server database with sensitive data

Scenarios

15

Without the required password or HSM to decrypt the DEK, the database cannot be opened.Slide16

Transparent data encryption with EKM

Install an EKM provider

Configure SQL Server to use the EKM providerCreate a Credential used to encrypt the databaseCreate an Asymmetric KeyCreate a Credential for use by the Database EngineCreate the Database Encryption Key16Slide17

Demonstration: TDESlide18

Contained Databases

Database that is isolated from other databases and from the instance of SQL Server that hosts the database

Helps user to isolate their database from the instance in 4 ways:Much of the metadata that describes a database is maintained in the databaseAll metadata are defined using the same collationUser authentication can be performed by the databaseThe SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment informationPartial Containment was implemented to resolve two of the most common dependencies, logins and collationEnable at Instance level first: sp_configure

'contained database authentication'

,

1

Enable at Database level:

ALTER

DATABASE

<name>

SET

CONTAINMENT

=

PARTIAL

18Slide19

Contained

Databases – Security Best Practices

Thoroughly check permissions on the contained database. Giving the ALTER ANY USER permissions can grant access to the database without SQL Administrator knowledgeDatabase owners have the ALTER ANY USER permission, audit users periodically on this databaseDisable the guest account as contained database users can access other databases if guest is enabledAvoid creating users that duplicates a login as it could cause a denial of service for the SQL loginConsider using Windows Authentication when possible to take advantage of Kerberos and password policiesPasswords in contained databases are stored within the database. Anyone with access can perform a dictionary attack. Consider restricting access to the database or using Window Authentication

19Slide20

Lesson Knowledge Check

Question: Which SQL Server feature helps in encryption/decryption without creating/modifying any code in the application ?

Answer: TDEQuestion: What are the steps to enable database containment for a given database? Answer: Enable at Instance level first: sp_configure 'contained database authentication', 1

Enable at Database level:

ALTER

DATABASE

<name>

SET

CONTAINMENT

=

PARTIAL

20Slide21

Section 5:

SQL Server Security

Lesson 2: SQL Server FeatureSQL 2016 Security Features21Slide22

Prevents Data Disclosure

Client-side encryption of sensitive data using keys that are

never given to the database system.

Queries on Encrypted Data

Support for equality comparison, incl. join, group by and distinct operators.

Application Transparency

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.

Benefits of Always EncryptedSlide23

dbo.Patients

Jane Doe

Name

243-24-9812

SSN

USA

Country

Jim Gray

198-33-0987

USA

John Smith

123-82-1095

USA

dbo.Patients

Jane Doe

Name

1x7fg655se2e

SSN

USA

Jim Gray

0x7ff654ae6d

USA

John Smith

0y8fj754ea2c

USA

Country

Result Set

Jim Gray

Name

Jane Doe

Name

1x7fg655se2e

SSN

USA

Country

Jim Gray

0x7ff654ae6d

USA

John Smith

0y8fj754ea2c

USA

dbo.Patients

SQL Server

Query

Trusted

Apps

SELECT Name FROM Patients WHERE SSN=@SSN

@SSN='198-33-0987'

Result Set

Jim Gray

Name

SELECT Name FROM Patients WHERE SSN=@SSN

@SSN=

0x7ff654ae6d

Column

Encryption

Key

Enhanced

ADO.NET

Library

Column

Master

Key

Client side

Always Encrypted

Help protect data at rest and in motion, on-premises & cloud

ciphertextSlide24

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 plain text valueTypes of Encryption for Always EncryptedRandomized encryptionEncrypt('123-45-6789') = 0x17cfd50aRepeat: Encrypt('123-45-6789') = 0x9b1fcf32Allows for transparent retrieval of encrypted data but NO operationsMore secureDeterministic 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 bySlide25

Security

Officer

1. Generate

CEKs and Master Key

2. Encrypt CEK

3. Store Master Key Securely

4. Upload Encrypted CEK to DB

CMK Store:

Certificate Store

HSM

Azure Key Vault

…

Encrypted

CEK

Column Encryption Key

(CEK)

Column

Master Key

(CMK)

Key Provisioning

CMK

Database

Encrypted CEKSlide26

Param

Encryption Type/ Algorithm

Encrypted CEK Value

CMK Store Provider Name

CMK Path

@Name

Non-DET/ AES 256

CERTIFICATE_STORE

Current User/ My/f2260…

EXEC

sp_execute_sql

N'SELECT * FROM Customers WHERE SSN = @SSN'

,

@

params

=

N'@SSN VARCHAR(11)'

,

@SSN

=

0x7ff654ae6d

Param

Encryption Type/ Algorithm

Encrypted CEK Value

CMK Store Provider Name

CMK Path

@SSN

DET/ AES 256

CERTIFICATE_STORE

Current User/ My/f2260…

Enhanced ADO.NET

Plaintext CEK

Cache

exec

sp_describe_parameter_encryption

@params

=

N'@SSN VARCHAR(11)'

,

@tsql

=

N'SELECT * FROM Customers WHERE SSN = @SSN'

Result set (

ciphertext

)

Name

Jim Gray

Result set (plaintext)

using

(

SqlCommand

cmd =

new

SqlCommand

(

"SELECT Name FROM Customers WHERE SSN = @SSN“

, conn))

{

cmd.Parameters.Add(

new

SqlParameter

(

"@SSN"

,

SqlDbType

.VarChar, 11).Value =

"111-22-3333"

);

SqlDataReader

reader =

cmd.ExecuteReader();

Client - Trusted

SQL Server - Untrusted

Encryption metadata

Name

0x19ca706fbd9

Encryption metadata

CMK Store

ExampleSlide27

Select columns to be encrypted

Analyze schema and application queries to detect conflicts (build time)

Set up the keys: master & CEK

Static schema analysis tool (SSDT only)

UI for selecting columns (no automated data classification)

Key setup tool to automate selecting CMK, generating and encrypting CEK and uploading key metadata to the database

Setup (SSMS or SSDT)

User Experience: SSMS or SSDT (Visual Studio)Slide28

Existing App – Setup

User Experience: SSMS or SSDT (Visual Studio)

UI for selecting columns (no automated data classification)

Select candidate columns to be encrypted

Analyze schema and application queries to detect conflicts and identify optimal encryption settings

Set up the keys

Encrypt selected columns while migrating the database to a target server (e.g. in Azure SQL Database

Key Setup tool to streamline selecting CMK, generating and encrypting CEK and uploading key metadata to the database

Encryption tool creating new (encrypted) columns, copying data from old (plain text) columns, swapping columns and re-creating dependencies

Select desired encryption settings for selected columns

UI for configuring encryption settings on selected columns (accepting/editing recommendations from the analysis tool)

Schema/workload analysis tool analyzing the schema and profiler logsSlide29

Data remains encrypted

during query

Summary: Always encrypted

Protect data at rest and in motion, on-premises & cloud

Capability

ADO.Net

client library provides transparent client-side encryption, while SQL Server executes T-SQL queries on encrypted data

Benefits

Sensitive data remains encrypted and

queryable

at all times on-premises & cloud.

Unauthorized users never have access to data or keys

No application changes

Apps

TCE-enabled

ADO .NET library

SQL Server

Encrypted

query

Columnar

key

No app changes

Master

keySlide30

Demonstration: Always EncryptedSlide31

Fine-grained access control over specific rows in a database table

Help prevent unauthorized access when multiple users share the same tables, or to implement connection filtering in multitenant applications

Administer via SQL Server Management Studio or SQL Server Data Tools

Enforcement logic inside the database and schema bound to the table.

Protect data privacy by ensuring the right access across rows

Status: Public preview

SQL Database

Customer 1

Customer 2

Customer 3

Row-level securitySlide32

Fine-grained access control

Keeping multi-tenant databases secure by limiting access by other users who share the same tables.

Application transparency

RLS works transparently at query time, no app changes needed.

Compatible with RLS in other leading products.

Centralized security logic

Enforcement logic resides inside database and is schema-bound to the table it protects providing greater security. Reduced application maintenance and complexity.

Store data intended for many consumers in a single database/table while at the same time restricting row-level read & write access based on users’ execution context.

Benefits of row-level securitySlide33

CREATE SECURITY POLICY

mySecurityPolicy

ADD FILTER PREDICATE dbo.fn_securitypredicate(wing, startTime, endTime) ON

dbo.patients

Predicate function

User-defined

inline table-valued function (

iTVF

) implementing

security logic

Can

be arbitrarily complicated, containing joins with other tables

Security predicateApplies

a predicate function to a particular table (SEMIJOIN APPLY)Two types: filter predicates and blocking predicatesSecurity policyCollection of security predicates for managing security across multiple tables

RLS ConceptsSlide34

Security

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

)

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

)

Fine-grained access control over rows in a table based on one or more pre-defined filtering criteria, e.g., user’s role or clearance level in organization.

Concepts

:

Predicate function

Security policy

ExampleSlide35

Two

App user (e.g., nurse) selects from Patients table

ThreeSecurity Policy transparently rewrites query to apply filter predicate

Database

Policy Manager

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

)

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)

Filter

Predicate:

INNER

JOIN…

Security

Policy

Application

Patients

One

Policy manager creates filter predicate and security policy in T-SQL, binding the predicate to the Patients table

Nurse

SELECT * FROM Patients

SELECT * FROM Patients

SEMIJOIN APPLY

dbo.fn_securitypredicate

(

patients.Wing

);

SELECT Patients.* FROM Patients,

StaffDuties

d INNER JOIN Employees e ON (

d.EmpId

=

e.EmpId

)

WHERE

e.UserSID

= SUSER_SID() AND

Patients.wing

=

d.Wing

;

RLS in Three StepsSlide36

--The following syntax creates a security policy with a filter predicate for the Customer table,

and leaves the security policy disabled

CREATE SECURITY POLICY [FederatedSecurityPolicy] ADD FILTER PREDICATE [rls].

[

fn_securitypredicate

]

(

[

CustomerId

]

)

ON

[dbo]

.[Customer];--Create a new schema and predicate function, which will use the application user ID stored in CONTEXT_INFO

to filter rows.

CREATE

FUNCTION

rls

.

fn_securitypredicate

(

@

AppUserId

int

)

RETURNS

TABLE

WITH

SCHEMABINDING

AS

RETURN

(

SELECT

1

AS

fn_securitypredicate_result

WHERE

DATABASE_PRINCIPAL_ID

()

=

DATABASE_PRINCIPAL_ID

(

'

dbo

'

)

--application context         

AND

CONTEXT_INFO

()

=

CONVERT

(

VARBINARY

(

128

),

@

AppUserId

);

GO

Creates a security policy for row level security.

The following examples demonstrate the use of the CREATE SECURITY POLICY syntax.

For an example of a complete security policy scenario, see

Row Level Security

.

Create Security PolicySlide37

Demonstration: Row Level Security

37Slide38

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 (e.g. Credit Card Numbers, SSN, etc.)

SQL Database

SQL Server 2016 CTP2

Table.CreditCardNo

4465-6571-7868-5796

4468-7746-3848-1978

4484-5434-6858-6550

Real-time data masking; partial masking

Status: Public preview

Dynamic Data Masking

Prevent the abuse of sensitive data by hiding it from usersSlide39

Audit success/failure of database operations

Enhanced auditing for OLTP with ability to track history of record changes

Transparent Data Encryption support for storage of In-memory OLTP Tables

Backup encryption now supported with compression

Other security enhancementsSlide40

Lesson Knowledge Check

Question: What are the types of encryption Always Encrypted supports?

Answer: Deterministic and Randomized Encryption

40Slide41

41