Modern Data Security for the Enterprises

– SQL Server & Azure SQL Database

Notes:

If you experience audio issues during the webinar, you can dial in through telephone details provided to you in

your registration confirmation email.

Please feel free to post questions in the questions dialog & we will try to answer as many as we can at the end.

Recording of this session will be shared in next 24-48 hours.

You can also write to us at marketing@winwire.com for any clarifications or information.

Session Speaker

Sergei Kolpakov

Sr. Principal Consultant

Siddhartha Gaur

Technical Architect

Agenda

2. Modern Approach - Technology + Processes

3. Demo

4. Q&A

1. Data Security Threats

Data Security

2016 saw some of the largest data breaches ever, and while companies continue to fight the good

fight against hackers and identity theft, Data Security has been one of the major concern for

organizations across the globe.

What are data security threats?OutsiderInsider

Data Security – How to ensure?

Data security refers to protective digital privacy measures that are applied to prevent

unauthorized access to computers, databases and websites. Data security also protects data from

corruption. Data security is an essential aspect of IT for organizations of every size and type.

Data security is also known as information security (IS)

• Obfuscating Data using

key-based cryptography,

or obscuring data with

alternate text.

• Ensure data is only legible

to the intended audience

Data Encryption

• Management of Logins and

Roles to restrict access of

data

• Prevent unauthorized

persons from obtaining

sensitive information

Access Control

• Detailed logging of failed

authentication attempts for

use in access auditing, as well

as raise alerts on anomalous

activity which may indicate a

security threat

Proactive Monitoring

Enterprise Data Security

Protect your organization, data and people

Access Control

Authorization – What all could you do?

Identification – Are you allowed?

Authentication – Who are you?

Access Control - Firewall

Protects network and its resources from malicious

external users

Secure confidential information from those who do

not have “explicit” access to it

Firewall settings enable administrators to determine

conditions for which a connection to the server

instance is allowed

Windows authentication in SQL Server provides

centralized access control with Active Directory

SSL/TLS secures connections to SQL Server

Access Control - Active Directory Authentication

Secure access to on-premises and cloud applications,

including Microsoft online services like Office 365 and

many non-Microsoft SaaS applications

Extend to Azure Active Directory on cloud for

simplified user access

User attributes along with roles and access

permissions are automatically synchronized to cloud

directory

Every organization resource request is validated to

ensures only authenticated users connects to that

resource

Avoid using SQL Authentication

Access Control - Separation of Roles

Not every authenticated user should access everything. Only

authorized users should get access to any resource/data

Role-based access control (RBAC) is an approach to

restricting system access to authorized users.

Permissions are associated with roles, and users are assigned

to appropriate roles

Roles are created for the various job functions in an

organization and users are assigned roles based on their

responsibilities and qualifications

Users can be easily reassigned from one role to another

Access Control - Permission

Granular access permissions for the organization's

repositories

Admin must ensure that minimum required permissions are

given to any role/user to allow it complete the required tasks.

No less and No More

Read, Write and Execute - Ensure right user have right set of

permissions, to avoid any malicious or accidental threat to

data security

Regular audit of permissions must be done

Read

ExecuteWrite

Access Control - Row-Level Security

RLS enables storing data for many users in a single

database and table while ensuring user sees only

her/his data

Access is restricted to row-level, and based on a

user's identity, role, and/ or execution context

Access logic is centralized

Reduced risk of error in application code

Row-Level Security

• Fine-grained access control

• Application Transparency

• Centralized security logic

Access Control - Row-Level Security

How to implement RLS

DemoRow-Level Security

Access Control - Dynamic Data Masking

Protects against unauthorized disclosure of

sensitive data in the application

Protect personally identifiable information

Regulatory Compliance

Expose sensitive data only on a need-to-know

basis

In absence of this typically Custom obfuscation

in application, views or third party solutions are

used to address this need

DemoDynamic Data Masking

Encryption - Transparent Data Encryption (TDE)

Protects sensitive data stored in a SQL Database

from unauthorized access

Encryption – T-SQL vs Azure to Setup TDE Certificate

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘sw9*K!j^*8u77*sso&#7*sso';

go

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';

go

USE MyDb;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

ALTER DATABASE MyDb

SET ENCRYPTION ON;

GO

BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\Program Files\Microsoft SQL

Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyDEKCertificate'

WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL

Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyDEKKey',

ENCRYPTION BY PASSWORD = '@1Qswkmswojd3i*nn77^7778Y34%#')

GO

Data disclosure

prevention

Client-side encryption of

sensitive data using keys that

are never given to the

database system

Queries on

encrypted data

Support for equality

comparison, including 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.

Security

Encryption – The need for Always Encrypted

Encryption – How it Works

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

SQL Server or SQL Database

ADO .NET

Name

Wayne Jefferson

Name

0x19ca706fbd9a

Result SetResult Set

Client

Name SSN Country

0x19ca706fbd9a 0x7ff654ae6d USA

dbo.Customers

ciphertext

"SELECT Name FROM Customers WHERE SSN = @SSN", 0x7ff654ae6d

ciphertext

"SELECT Name FROM Customers WHERE SSN = @SSN","111-22-3333"

Encrypted sensitive data and corresponding keys are never seen in plaintext in SQL Server

trust boundary

Randomized encryptionEncrypt('123-45-6789') = 0x17cfd50a

Repeat: Encrypt('123-45-6789') = 0x9b1fcf32

Allows for transparent retrieval of encrypted data but NO

operations

More secure

Deterministic encryptionEncrypt('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

Types of encryption

Randomized encryption uses a method

that encrypts data in a less predictable

manner

Deterministic encryption uses a

method that always generates the

same encrypted value for any given

plaintext value

Encryption - Types of encryption

Select columns to

be encrypted

Analyze schema

and application

queries to detect

conflicts (build

time)Set up keys:

Master and 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 database

Security

Encryption - Always Encrypted Setup (SSMS or SSDT)

DemoAlways Encrypted

Proactive

Monitoring

Monitoring – Auditing & Threat Detection in Azure

Azure Security Center will recommend that you

turn on auditing for all databases on your Azure

SQL servers if auditing is not already enabled.

Auditing can help you maintain regulatory

compliance, understand database activity, and

gain insight into discrepancies and anomalies

that could indicate business concerns or

suspected security violations.

Monitoring – Azure SQL Database Threat Detection

Just turn it ON - SQL Database Threat Detection is

incredibly easy to enable.

Real-time actionable alerts - SQL Database Threat

Detection runs multiple sets of algorithms which detect

potential vulnerabilities and SQL injection attacks, as well

as anomalous database access patterns (such as access

from an unusual location or by an unfamiliar principal).

Investigate SQL threat - Each SQL Database Threat

Detection email notification and Azure Security Center

alert includes a direct link to the SQL audit log.

DemoAuditing & Threat Detection in Azure Security Center

Data Security and Compliance Assessment Offer

Objective

• Uncover security vulnerabilities by evaluating the architecture and security

configurations for your critical assets and repositories both on-premise and

in the cloud.

• Recommend an optimized setup to enhance the protection of your data at-

rest and in-motion.

Activities

Estimated Duration

Deliverable

• Microsoft Data Platform security technical-level overview

• Review of overall security and compliance goals for your organization

• Analysis of identified data repositories and data movement functions

• Presentation of findings, recommendations, and roadmap

• WinWire’s Data Security and Compliance Assessment will typically take

1 to 2 weeks.

• A comprehensive assessment documentation with specific recommendations

and an actionable roadmap to help you achieve your data security and

compliance goals

Please contact marketing@winwire.com

for a free

Data Security and Compliance

Assessment Offer

Q & Ahttp://www.winwire.com/blog-winsights/

www.twitter.com/winwire

marketing@winwire.com

www.winwire.com