Securing our Azure SQL

Database

José Manuel Jurado Díaz

Roberto Cavalcanti
About us
Our special guests from Contoso Company
http://data-protection.safenet-inc.com/2016/03/2015-data-breaches-by-the-numbers/
Session Objectives

Other Mechanisms

Proactive monitoring

Protect Data

Application Control
Access

Control Database Access

How Connections
work
SQL Server
Platform History
 SQL Server Platform History
Hybrid Cloud
Managed database service
Lower cost
Shared

Focus on business logic

WA SQL Database - PaaS SQL Server on Azure - IaaS
Virtualized Database
100% Compatibility
Rapid self-service provisioning
SQL Server in WA VM - IaaS
Virtualized Machine
Elastic/Self-Service capabilities
Full h/w control
SQL Server Private
Virtualized Machine
Higher cost
Dedicated

Full h/w control

Roll-your-own HA/DR/scale
SQL Server
Physical Machine (raw iron)

High Control Low Control

Security Approach
Azure SQL Database
Security Approach in SQL Server

Sessions
Active Directory
Encryption
Security
Transparent Data
Data protection
Encryption
Row-level security
Analysis
Azure Active
Directory Security Always Encrypted SQL Auditing
Dynamic data
masking
Firewall TLS Threat Detection

C2 common

SQL Server Audit

Azure SQL PaaS SQL Server IaaS/On-Premise All implementations

Security Approach – Control Database Access
Port: 1433
Protocol: TCP
Encrypted

Proxy:
ADALSQL
• Protect
ADO AAD connection
.NET 4.6 • Check
firewall rules
• Port Bridge
• Hexatier/GreenSQ
Authentication L
Method. • VPN is not
supported
Security Approach – How Connections work Inside
Azure
servername.database.windows.net
TCP, Port: 1433

Redirected to: Database Ring

using Port: 11202

All connections: 11202

S S
P
S
 Security Approach – How Connections work
Outside Azure
servername.database.windows.net
TCP, Port: 1433

S S
P
S
Security Approach – Control Application Access
Dynamic Data Masking

Limit the exposure of

sensitive data by
obfuscating query results
for app users and
engineer

Row-Level Security
Centralize your
row access logic
within the
database.
Demo

Dynamic Data Masking

Row Level Security
Demo - Dynamic Data Masking
CREATE USER Peter FOR LOGIN Peter;
GRANT SELECT ON Contacto TO Peter;

CREATE TABLE Contacto

(ID int IDENTITY PRIMARY KEY,
Nombre varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
Apellido varchar(100) NOT NULL,
NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

INSERT Contacto (Nombre, Apellido, NrTlf, Email) VALUES

('Roberto', 'Torres', '91551234567', '[email protected]'),
('Juan', 'Galvin', '95551234568', '[email protected]'),
('José', 'Garcia', '95551234569', '[email protected]');

EXECUTE AS USER = 'Peter';

SELECT * FROM Contacto;
REVERT;
Demo - Row Level Security
CREATE TABLE Protegido CREATE FUNCTION SecPred(@userId int)
(ID int IDENTITY PRIMARY KEY, RETURNS TABLE
Nombre varchar(100) MASKED WITH (FUNCTION = WITH SCHEMABINDING
'partial(1,"XXXXXXX",0)') NULL, AS
Apellido varchar(100) NOT NULL, RETURN SELECT 1 as valor WHERE @userId = user_id()
NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
UserID int ); CREATE SECURITY POLICY [secpol] ADD FILTER PREDICATE
[dbo].[SecPred]([UserId]) on [dbo].[Protegido]
select * from sys.database_principals
GRANT SELECT ON protegido TO Peter;
INSERT Protegido (Nombre, Apellido, NrTlf, Email, UserId) VALUES GRANT SELECT ON protegido TO Ken;
('Roberto', 'Torres', '91551234567', '[email protected]', 5),
('Juan', 'Galvin', '95551234568', '[email protected]', 5),
('José', 'Garcia', '95551234569', '[email protected]',1);

EXECUTE AS USER = 'Kent'; EXECUTE AS USER = 'Peter';

SELECT * FROM protegido; SELECT * FROM protegido;
REVERT; REVERT;
Security Approach – Protect the data
Encryption at rest:
Transparent Data Encryption (TDE)

Protect data on SQL database physical storage from

unauthorized access.

Encryption in
motion:
Transport Layer
Security (TLS)
"SELECT Name FROM
Customers WHERE SSN =
@SSN", "SELECT Name FROM
Customers WHERE SSN
Name Encryption in use (client):
0x7ff654ae6d
ADO
= @SSN",
DB Jim Gray Always Encrypted (AE)
"111-22-3333"
.NET
Name
Protects the highly sensitive data in-use from
0x19ca706fbd9a
high privilege SQL users.
Demo

Always Encrypted
Demo - Always Encrypted
Pfx CREATE COLUMN MASTER KEY DEFINITION [MyMKey]
Certificate WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH=N'CurrentUser/My/0055B6F4C592DCAD70B7F3C24CE4499C6D58E24B')

CREATE COLUMN ENCRYPTION KEY [ColKey]

WITH VALUES
( COLUMN MASTER KEY DEFINITION = [MyMKey], ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E0074007500730065)

CREATE TABLE [dbo].[Patients](

[PatientId] [int] IDENTITY(1,1),
[SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = [ColKey]) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[StreetAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[ZipCode] [int] NULL,
[State] [nvarchar](50) NULL,
[BirthDate] [datetime2] NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )

DECLARE @SSN nchar(11) = N'795-73-9834'

declare @Name [nvarchar](50) = 'Jose'
insert into [Patients] ([SSN],[FirstName]) values( @SSN,@Name)
Security Approach – Proactive monitoring
SQL Auditing

Logged Database events

Threat Detection
Detects suspicious database activities indicating
possible malicious intent to access, breach or
exploit data in the database

Analyzing
 Security Approach – How Connections work
Connection is redirected
SQL Auditing
via TDS protocol servername.database.windows.net
TCP, Port: 1433
TDS <3.2 – JDBC

SQL Auditing Enabled

S S
P
servername.database.secure.windows.net
S TCP, Port: 1433
Security Approach - SQL Auditing
Audit Records Example
Security Approach - SQL Auditing
Setup
Security Approach - SQL Auditing
Data Captured Visualization

24
 Security Approach - SQL Auditing
Threat Detection Example
Threat Detection detects anomalous database activities indicating
potential security threats to the database.
Other Security
Mechanisms
Other Security Mechanisms
Compliance Disaster Recovery
• 10 years for Long terms retention for PaaS
• Storage and the databases geo-replicated.
• FedRAMP - Government
• ISO
• HIPPA - Medical
• PCI - Payment High availability
• EU Model Clauses - Personal • 3 copies of your data PaaS and up to 16 copies of geo-
• UK G-Cloud - Public Sector replicated read-only.

Penetration Test
Replication
• Transactional Replication unidirectional
Microsoft Trust Center OnPremise -> IaaS
OnPremise, Iaas y Azure
• Data Sync*
Securing our Azure SQL
Database

Questions?
[email protected]
@jmjuradodiaz

[email protected]
@rocavalc
Securing our Azure SQL
Database

Thanks for attending