Transcript Overview and Roadmap for Microsoft SQL Server Security

Always
Encrypted
Enhancements
to Crypto
TDE for SQL DB,
CLE for SQL DB
TDE Perf
Encryption
Row-level
Security
Dynamic Data
Masking
Auditing
Enhancements
to SQL Audit
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, incl. 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.
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




0x17cfd50a
0x9b1fcf32



0x85a55d3f
0x85a55d3f

1. Generate CEKs and Master Key
2. Encrypt CEK
Security
Officer
3. Store Master Key Securely
4. Upload Encrypted CEK to DB
Column
Encryption
Key
(CEK)
Column
Master Key
(CMK)
Encrypted
CEK
CMK Store:
• Certificate Store
• HSM
• Azure Key Vault
• …
Encrypted
CEK
Database
CMK
Fine-grained
Access Control
Application
Transparency
Centralized
Security Logic
Keeping multi-tenant
databases secure by limiting
access by other users who
share the same tables.
RLS works transparently at
query time, no app changes
needed.
Compatible with RLS in other
leading products.
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.





CREATE SECURITY POLICY mySecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(wing, startTime, endTime)
ON dbo.patients
On by choice
Protects database and all of its backups,
transaction logs and tempdb
Customer A
Customer B
“2-click” User Experience
Alternatively: 2 T-SQL statements
Customer B
Azure SQL DB manages your keys (aka service
managed TDE)
Improved Encryption Performance
Customer A
Using INTEL’s AES-NI Hardware Acceleration
SQL DB
Management
Service
Available on v12 servers, all SQL
DB’s editions
Regulatory
Compliance
Sensitive Data
Protection

 https://msdn.microsoft.com/en-us/library/dn765131.aspx

 https://msdn.microsoft.com/en-us/bb510589.aspx


 http://blogs.msdn.com/b/sqlsecurity/


 http://sqlserverlst.codeplex.com/
http://myignite.microsoft.com