End to End Security and Encryption in SQL

End to End Security and Encryption in SQL Server Steve Jones Editor, SQLServer. Central Evangelist, Redgate Software

Agenda • • • Bio SQL Server Security Enhancements Encryption Overview Always Encrypted Row Level Security (RLS) Column Level Encryption Transparent Data Encryption (TDE) Dynamic Data Masking (DDM) Summary

26 years SQL Server data experience DBA, developer, manager, writer, speaker in a variety of companies and industries Founder, SQLServer. Central And current editor, with the goal of helping you learn to be a better data professional every day Steve Jones Evangelist, Redgate Software Editor, SQLServer. Central /in/way 0 utwest 10 years Microsoft Data Platform MVP I am honored to be recognized by Microsoft for the last decade as an MVP @way 0 utwest www. voiceofthedba. com

SQL Server 2017 Security Enhancements • CLR Strict Security implemented by default

SQL Server 2016 Security Enhancements • • • Default endpoint encryption changed (RC 4 -> AES) Dynamic Data Masking Always Encrypted Row Level Security Credentials can be added at the database level TDE supports Intel AES-NI

Azure SQL Server Security Enhancements • • TDE Enabled by default TDE Customer Managed Keys Available Threat Detection available Vulnerability Assessment available

What is encryption? encryption is the process of transforming information (referred to as plaintext) using an algorithm (called a cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key. The result of the process is encrypted information (in cryptography, referred to as ciphertext). - Wikipedia

Encryption works with Functions and Keys plaintext ciphertext The quick brown fox jumped over the lazy dog. Encryption Function 0 x 0 ae 783 b 218 d 18 encryption key 0 x 00059 E 2 EC 7419 F 590 E 79 D 7 F 1 B 774 BFE 601000000 DB 80 B 8 A C 1 B 295 E 367 FEAC 63 C 4 BD 7 B 8 F 8 FACD 0151 B 57 DF 97 FF 2 BBA 1 E D 9626 B 0316043 C 62387 BB 8 E 5 D 4 A 17 B 33 C 48 A 554 F 2 A 9 B 2862 6 BB 250 A 153 FEEF 2 BFEBCF 92 E CF 6 C 421 D 47 C 84 BF 93074 E 54 E F 85 C 85 B 1 C

X. 509 Certificate Symmetric Key The quick brown fox jumped over the lazy dog.

Always Encrypted

Server Client CMK ADO. NET Select name, cc From Cust Where cc = '12345' Have AE Query 228 ba 8 e CEK trusted Cust. ID Name CC Email 1 Steve 7 de 8 a 76 sjones 2 Andy de 527 e 7 a awarren untrusted

Server Client CMK ADO. NET Select name, cc From Cust Where cc = '12345' 228 ba 8 e Return Encrypted CEK 228 ba 8 e Cust. ID Name CC Email 1 Steve 7 de 8 a 76 sjones 2 Andy de 527 e 7 a awarren Symmetric Key trusted untrusted

Server Client CMK ADO. NET Select name, cc From Cust Where cc = '12345' Symmetric Key Name CC Steve 5555 4444 trusted Select name, cc CC Name From Cust Steve 7 de 8 a 76 Where cc = '7 de 8 a 76' 228 ba 8 e CEK Cust. ID Name CC Email 1 Steve 7 de 8 a 76 sjones 2 Andy de 527 e 7 a awarren untrusted

Demo Always Encrypted

Always Encrypted Limitations § § § § Strings require _BIN 2 collation Limited datatypes Only equality comparisons (no <, >, like) No statistics on encrypted columns Max two Column Master Keys can be used No Defaults on encrypted columns No replication More

Row-Level Security

Row Level Security (RLS) • Allow rows of data to be screened based on user characteristics • Independent of other SQL Server security. • Available in SQL Server 2016+ and Azure SQL Database • The screening is done with a security predicate that examines the “user chracteristics” and returns a 1 for visible rows • A security policy links a predicate to a particular table – Filter predicates apply to reads – Block predicates apply to writes

Order. Header table User Bob (Salesperson. ID 1) Order. ID Order. Amount Salesperson. ID 1001 5000. 00 1 1002 1250. 25 2 1003 922. 13 2 1004 125. 00 1 1005 4200. 99 3 1006 1652. 89 2 User sally (Salesperson. ID 2)

Order. Header table User Bob (Salesperson. ID 1) Issue query Select * from Order. Header Order. ID Order. Amount Salesperson. ID 1001 5000. 00 1 1002 1250. 25 2 1003 922. 13 2 1004 125. 00 1 1005 4200. 99 3 1006 1652. 89 2 Results Order. ID Order. Amount Salesperson. ID 1001 5000. 00 1 1004 125. 00 1 User sally (Salesperson. ID 2)

Order. Header table User Bob (Salesperson. ID 1 Issue query Select * from Order. Header Order. ID Order. Amount Salesperson. ID 1001 5000. 00 1 1002 1250. 25 2 1003 922. 13 2 1004 125. 00 1 1005 4200. 99 3 1006 1652. 89 2 User sally (Salesperson. ID 2 Issue query Select * from Order. Header Results Order. ID Order. Amount Salesperson. ID 1001 5000. 00 1 1004 125. 00 1 Results Order. ID Order. Amount Salesperson. ID 1002 1250. 25 2 1003 922. 13 2 1006 1652. 89 2

Demo Row Level Security

Row Level Security Limitations • • No Filestream No Polybase Data Leakage – From stats , CDC, queries More

Column Level Encryption

Column Level Encryption • • • This is available in SQL Server 2005+ Uses symmetric or asymmetric keys to protect data Encryption is really by field, not column. Encryption operations occur in SQL Server Temporary keys may be used

Demo Column Level Encryption

Column Level Encryption – Limitations • • • Quite a few algorithms are old Data is not necessarily protected from the DBA (can be. A little) Requires CPU resources on the server. Encrypted data does not compress. (compress, then encrypt) Symmetric keys are deterministic Requires code changes

Transparent Data Encryption • Protects data at rest • Encrypts data and log files (mdf, ndf, ldf) • In SQL Server 2016 support for Intel AES-NI almost eliminates CPU impact • Backup files encrypted • Tempdb encrypted • Enterprise Edition only.

Demo Transparent Data Encryption

Transparent Data Encryption Limitations § § § Replication data is not encrypted Filestream data is not encrypted BPE files are not encrypted More Overhead (usually < 5%, workload dependent)

Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) • • • No changes to data or storage DDM defines how data appears when queried. Does not require changes to application code This is a NOT ENCRYPTION This is an application programming convenience feature NOT SECURITY

Demo Dynamic Data Masking

Dynamic Data Masking - Limitations • Does not work with Always Encrypted columns • UNMASK is by database, not by table or column • This is an all or nothing feature - data is masked for all rows, no exceptions • The query plan, statistics, etc. do not mask data • Attacks against the data are possible with adhoc queries

Summary • SQL Server 2016+ includes a variety of encryption (and data protection) functions for server and client • TDE protects data at rest • Always Encrypted is for cases where the client is trusted, but not the server • RLS is independent of other security mechanisms, but not perfect • DDM is a security convenience feature • Column Level encryption protects the data on the server.

The End • Thank you for coming • Questions? • Ask at www. sqlservercentral. com/forums www. voiceofthedba. com sjones@sqlservercentral. com @way 0 utwest /in/way 0 utwest

References • DDM – Dynamic Data Masking (BOL) - https: //msdn. microsoft. com/enus/library/mt 130841. aspx? f=255&MSPPError=-2147217396 – https: //blogs. technet. microsoft. com/dataplatforminsider/2016/01/2 5/use-dynamic-data-masking-to-obfuscate-your-sensitive-data/ – A Very Quick Post on SQL Server 2016 Dynamic Data Masking https: //www. simple-talk. com/blogs/2015/06/10/a-very-quick-poston-sql-server-2016 -dynamic-data-masking/

References • Column Level Encryption – Decryptby. Key - https: //msdn. microsoft. com/en-us/library/ms 181860. aspx • Row Level Security – MSDN - https: //msdn. microsoft. com/library/dn 765131. aspx – Channel 9 - https: //channel 9. msdn. com/Shows/Data-Exposed/SQL-Server 2016 -Row-Level-Security • Always Encrypted – BOL - https: //msdn. microsoft. com/en-us/library/mt 163865. aspx – Channel 9 - https: //channel 9. msdn. com/Shows/Data-Exposed/Getting. Started-with-Always-Encrypted-with-SSMS? ocid=relatedentry