A Databases Designer's Favourite Security Features - Day of Data Winnipeg

Karen discusses her favourite security features in SQL Server and Azure SQL DB

Transcript

3. DAY OF DATA SEPT 19 WINNIPEG 2026 Thank You to Our Sponsors Day of Data Winnipeg 2026 is made possible by the generous support of: GLOBAL SPONSOR GOLD BRONZE FACILITY Winnipeg Modern Data Users Group meetup.com/winnipeg-moderndata-users-group DAY OF DATA WINNIPEG 2026 meetup.com/winnipeg-modern-data-users-group
4. Karen Lopez Microsoft MVP, Data Platform Microsoft Certified Trainer, vExpert Data management expert, space enthusiast, and #TeamData evangelist www.datamodel.com @datachick
5. 01 05 02 06 03 07 04 08 Intro & Motivations Always Encrypted Data Classification Ledger Tables and DBs Dynamic Data Masking Monitoring & Vulnerability Assessments Row Level Security Takeaways
6. Karen’s Thoughts on Design “Every design decision comes down to cost, benefit and risk.” - Karen Lopez 6
7. Why this topic? Because We Love Our Data
8. POLL: Who Are You?
9. Recent data breaches/harms not related to ransomware? 10
10. 10 Top Web Security Risks 2021 11
11. 10 Top Web Security Risks https://owasp.org/www-project-top-ten/ 12
17. Security – Always Encrypted
18. Two Options for AE Standard With Secure Enclaves 19
19. 20 Security - Always Encrypted
20. 21 Security - Always Encrypted
21. Security – Always Encrypted Enabled at column level 22 Protects data at rest *AND* in memory Uses Column Master Key (client) and Column Encryption Key (server)
22. Foreign keys must match encryption types Client code needs to support AE (currently this means .NET 4.x or above) SECURITY – ALWAYS ENCRYPTED 23
23. Always Encrypted
24. Always Encrypted with Secure Enclaves Virtualization-based Security (VBS) Enclaves Attestation Enclave Enabled Keys 25 https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves Randomized Only
25. Always Encrypted with Secure Enclaves In Place Encryption Confidential Queries 26 https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves Indexing
26. Why would a DB Designer love it? Always Encrypted, yeah. 28 Allows designers to not only specify which columns need to be protected, but how. Parameters are encrypted as well Built into the engine, easier for everyone
27. Bobbie Lookup, CSR 30
30. Spreadsheet Data Sample ID First Name Last Name Gov ID Num Title DOB Marital Status Gender Hire Date Vacation Hours 5 Karen Lopez 69-5256908 Design Engineer 1946-10-29 S F 2002-02-06 5 34
31. Dynamic Data Masking (DDM) Introduced in 2016 Function-based Privacy-driven No query changes 35
32. Dynamic Data Masking Column level Data at rest is not masked Meant to complement other methods Performed at the end of a database query right before data returned Performance impact small 36
35. DDM Functions Function Mask Default Based on Datatype Example String Numbers Date & Times Binary Others XXXX 0 01.01.1900 00:00:00.0000000 0 Depends on datatype Email First character of email, then Xs, then .com Always .com Kxxx@xxxx.com Datetime Mask by date and time components Partial First and last values, with characters in the middle kxxxn Random For numeric types, with a range 12 Regex REGEX_REPLACE Kxxx@infoadvisors.com 39
36. default() Mask Function Syntax email() partial(prefix, padding, suffix) datetime("Y" | "M" | "D" | "h" | "m" | "s") random(start, end) 40
38. Unmasked Data Name Favourite Colour Phone Number Email BirthDate Karen Lopez Purple +1 416 555 1212 Karen@example.com 2300-01-01 Bob Tablescan Grey 555 555 5678 Bingo@DBA.example.net 2122-11-12 Amit Azure Blue +21 023 555 1234 Zed@azure.example.org 9898-01-31 Freddie Fabric Green +384 64 3 444 Fred@analyst.example.com 2023-11-15 42
39. Masked Data – With Defaults Name Favourite Colour Masked Phone Number Masked Email Masked BirthDate Karen Lopez Purple XXXX KXXX@XXX.com 1900-01-01 Bob Tablescan Grey XXXX BXXX@XXX.com 1900-01-01 Amit Azure Blue XXXX ZXXX@XXX.com 1900-01-01 Freddie Fabric Green XXXX FXXX@XXX.com 1900-01-01 43
40. Masked Data – With Regex (Preview) Name Favourite Colour Masked Phone Number Masked Email Masked BirthDate* Karen Lopez Purple +1 XXXXX 1212 K*@example.com 1900-01-01 Bob Tablescan Grey XXXXX 5678 B*@DBA.example.net 1900-01-01 Amit Azure Blue +21 XXXXX 1234 Z*@azure.example.org 1900-01-01 Freddie Fabric Green +384 XXXXX 3 444 F*@analyst.example.com 1900-01-01 44
41. 3 Options for Masking PhoneNumber Original default() partial(3,'XXXXX',4) Regex +1 416 555 1212 555 555 5678 XXXX XXXX +1 XXXXX1212 555XXXXX5678 +1 XXXXX 1212 XXXXX 5678 +21 023 555 1234 +384 64 3 444 XXXX XXXX +21XXXXX1234 +38XXXXX3 444 +21 XXXXX 1234 +384 XXXXX 3 444 45
42. Dynamic Data Masking ALTER TABLE Masked.Employee_Masked ALTER COLUMN PhoneNumber ADD MASKED WITH ( FUNCTION = 'REGEXP_REPLACE( "((\+\d{1,3})\s*)?(.*)(\d\s*\d\s*\d\s*\d)$", "\1 XXXXX \4" )' ); GO 46
43. Dynamic Data Masking 01 02 03 Data in database is not changed Ad-hoc queries *can* expose data Is not security 48
44. Works with RLS Dynamic Data Masking Masks need to be designed Ideal for sharing data with partners using export Does not block updates 50
45. Cannot mask an encrypted column Dynamic Data Masking Cannot be configured on computed column If computed column depends on a mask, then mask is returned Using SELECT INTO or INSERT INTO results in masked data being inserted into target 51
48. Why would a Data Designer love DDM? Allows central, reusable design for standard masking Offers more reliable masking and reusable masking Removes thoughts about “we can do that later” 54
51. Security – Row Level Security
52. Filtering result sets (predicate-based access) Predicates applied when reading data Can be used to block write access User defined policies tied to inline table functions
53. Row Level Security 01 02 03 04 No indication that results have been filtered If all rows are filtered than NULL set returned For block predicates, an error returned Works even if you are dbo or db_owner role
54. Predicates 60
55. Predicates 61
56. Why would a DB Designer love it? Allows a designer to do this sort of data protection IN THE DATABASE, not just relying on code. 62 Many, many pieces of code.
59. SSMS Data Classification – SQL Server 65
60. 66 Data Classification Results
61. Data Classification Recommendations 67
63. Azure SQL DB 69
65. Information Types 71
66. Why a DB Designer Loves Classification Standardized Work for data & compliance pros Enforceable Future-proofing 72
69. More trustworthy Why use a LEDGER table? More protection from DBA/SysAdmin tampering Don’t need or want full blockchain functionality Want to store data off a full blockchain for better performance 75
70. Ledger Databases Database Digests Key Features Azure LEDGER Tables Ledger Tables Updatable Append only Immutable storage for transaction recording Ledger Verification 76
71. Updatable Ledger Tables 77
74. Who did what when? 80
75. Append Only Ledger Tables 81
76. APPEND ONLY LEDGER TABLES Write and Read only, no updates No history table Ledger View 82
78. METEORITE LANDINGS 84
79. METEORITE LANDINGS VIEW 85
80. Updatable Ledger Table Ledger View Database Digest Ledger History Append-only Ledger Table Ledger View 86
81. Changing existing table to a Ledger Table Alter? No. Migrate 87 Create new Ledger Table Copy Data to New Table Clean up previous Table
83. AZURE PORTAL Ensures all tables in the database are Ledger Tables 89
84. TSQL LEDGER DATABASE CREATE DATABASE Database01 ( EDITION = 'GeneralPurpose’, SERVICE_OBJECTIVE='GP_Gen5_2’, MAXSIZE = 2 GB ) WITH LEDGER = ON; 90
85. LEDGER DIGESTS Holds the database hashes Show the state of the data Stored outside the database server Separation of duties Immutable storage & policies 91
86. LEDGER DIGESTS JSON format Latest block in the database ledger appended Storage or Confidential Storage sqldbledgerdigests sys.sp_generate_database_ledger_digest Automatic or Manual {"database_name":"SpaceThreats","block_id":0,"hash":"0xBEF648C7AD9464A2B58E337B060 4BA94944A7F8D6A92306F02D0E08542DACDB2","last_transaction_commit_time":"2022-1018T18:25:39.9566667","digest_time":"2022-10-18T18:25:43.1473462"} 92
87. Digest Verification 01 02 03 Checks the hashes in the digests Reports based on where it is told the real digest is Dependent on separation of duties + access controls on storage 93
88. Verifying Databases 94
90. Why a DB Designer Loves a LEDGER Table? More trustworthy More protection from DBA/SysAdmin tampering Don’t need or want full blockchain functionality 96
91. VULNERABILITY ASSESSMENTS Let the robots do what they are good at doing 97
92. IN SSMS – BUT ONLY VERSION 18 OR LOWER 98
93. VULNERABILITY ASSESSMENT – SSMS 18.X OR LOWER 99
95. Microsoft Defender
96. How? Secure scores Recommendations Alerting 102
97. Vvulnerabilities
98. Remediating Recommendations
99. Key Takeaways 105 Data classifications are required Can’t secure data we don’t understand Security nearest the data In DB performance Data pros know data Can’t trust everyone anyone Developer productivity Managing Risk Importance of Laziness
100. SpaceAppsChallenge.org
101. DAY OF DATA SEPT 19 WINNIPEG 2026 Thank You to Our Sponsors Day of Data Winnipeg 2026 is made possible by the generous support of: GLOBAL SPONSOR GOLD BRONZE FACILITY Winnipeg Modern Data Users Group meetup.com/winnipeg-moderndata-users-group DAY OF DATA WINNIPEG 2026 meetup.com/winnipeg-modern-data-users-group
102. THANK YOU Go out and be great…and Design Secure Databases Every Design Decision must be based on Cost, Benefit and Risk @DATACHICK Datachick@mstdn.ca /in/karenlopez 108