One Platform, Many Security Models: Fabric Databases

A review of the differences between SQL Server, Azure SQL DB, and Fabric databases' security and privacy feature.

Transcript

1. Multiple Security Models What Changes in Fabric Databases? Karen Lopez, InfoAdvisors Nov 2022 https://difinityconf/ 2026
2. Karen Lopez, FCIPS Microsoft MVP, Data Platform Microsoft Certified Trainer, vExpert Data management expert, space enthusiast, and #TeamData evangelist www.datamodel.com @datachick on BlueSky
3. Karen’s Thoughts on Design “Every design decision comes down to cost, benefit and risk.” - Karen Lopez
4. Why this topic? Because We Love Our Data
5. POLL: Who Are You?
6. Recent data breaches?
7. 10 Top Web Security Risks 2021
8. 10 Top Web Security Risks https://owasp.org/www-project-top-ten/
11. Bobbie Lookup, DBA
12. WHAT’S BASICALLY THE SAME, MOST OF THE TIME? Identity Access Control Data Protection Microsoft Entra ID authentication Least privilege MFA and Conditional Access Role-based access control Row-Level Security (RLS) Managed identities and service principals* Group-based security Separation of duties Column/object-level restrictions Sensitive data protection These carry straight over. The skills you already have still apply.
13. WHAT’S BASICALLY THE SAME, MOST OF THE TIME? Encryption Governance Network Security Encryption in transit (TLS) Data classification Private connectivity Sensitivity labels Controlled data access paths Encryption at rest Secure service-toservice communication Microsoft Purview integration Compliance and regulatory support Reduced public exposure These carry straight over. The skills you already have still apply.
15. Security – Always Encrypted
16. Two Options for AE Standard With Secure Enclaves
17. Security - Always Encrypted
18. Security – Always Encrypted Enabled at column level Protects data at rest *AND* in memory Uses Column Master Key (client) and Column Encryption Key (server)
19. Always Encrypted Feature Always Encrypted (incl. enclaves) Fabric SQL Database No Fabric Data Warehouse No Fabric Lakehouse (SQL Endpoint) No Fabric Data Lake (OneLake) No Azure SQL Database Yes Mirrored Azure SQL DB No SQL Server (on-prem) Yes
20. Dynamic Data Masking Really more of a privacy feature than a security one, in Karen’s opinion
22. Security – Dynamic Data Masking
23. Data Masking Examples XXXX XXXX XXXX 1234 kxxxxxx@ixxxxx.com $99,9999 June, 99, 9999 KXXXXX Lopez
24. Security – Dynamic Data Masking
25. Dynamic Data Masking Column level--Data in the database, at rest, has no protection. Meant to complement other methods Performed at the end of a database query right before data returned Performance impact small
26. Dynamic Data Masking 5 functions available • Default • Email • Custom String • Random • Datetime
27. DDM Functions Function Mask Default Based on Datatype Example String – XXX Numbers – 000000 Date & Times - 01.01.2000 00:00:00.0000000 Binary – Single Byte 0 XXXX 0 01.01.2000 00:00:00.0000000 0 Email First character of email, then Xs, then .com Always .com Kxxx@xxxx.com Custom First and last values, with Xs in the middle kxxxn Random For numeric types, with a range 12 Datetime For datatime datatypes, plus date 6/27/1900
28. Dynamic Data Masking 01 02 03 Data in database is not changed Ad-hoc queries *can* expose data Does not aim to prevent users from exposing pieces of sensitive data
29. Dynamic Data Masking Cannot mask an encrypted column (AE) Cannot be configured on computed column But 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 (also for import/export)
30. Granular permissions Specific columns Table Database Schema
31. Dynamic Data Masking Feature Dynamic Data Masking (DDM) Fabric SQL Database Yes Fabric Data Warehouse Yes Fabric Lakehouse (SQL Endpoint) No Fabric Data Lake (OneLake) No) Azure SQL Database Mirrored Azure SQL DB SQL Server (on-prem) Yes Yes, but applied after mirroring Yes
32. Why would a DB Designer love it? Allows central, reusable design for standard masking Offers more reliable masking and more usable masking Removes whining assurances “we can do that later”
34. SALES table East manager sees East region West region East region West region East region West region SECURITY POLICY Predicate runs on every query West manager sees
35. ROW LEVEL SECURITY 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
36. Predicates
37. Predicates
38. RLS IN FABRIC SQL DATABASE Same T-SQL Inline TVF predicate CREATE SECURITY POLICY Filter hides rows, block fails writes Entra is the identity Mind the OneLake path No SQL logins Policies stop at the SQL engine Workspace roles gate the item Use OneLake RLS roles there No admin exemption Isolate policies in a schema Same predicates you already use but different identity model
39. 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. Many, many pieces of code.
41. SSMS Data Classification – SQL Server
42. Data Classification Results
43. Data Classification Recommendations
44. Azure SQL DB
46. In SQL Server: property CLASSIFICATION In Purview: DLP and Policies SENSITIVITY LABELS
47. Why a DB Designer Loves Classification Standardized Work for data & compliance pros Enforceable Future-proofing
49. 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
50. Ledger Databases Database Digests Key Features Azure LEDGER Tables Ledger Tables Updatable Append only Immutable storage for transaction recording Ledger Verification
51. Updatable Ledger Tables
54. Who did what when?
55. Append Only Ledger Tables
56. Updatable Ledger Table Ledger View Database Digest Ledger History Append-only Ledger Table Ledger View
57. TSQL LEDGER DATABASE CREATE DATABASE Database01 ( EDITION = 'GeneralPurpose’, SERVICE_OBJECTIVE='GP_Gen5_2’, MAXSIZE = 2 GB ) WITH LEDGER = ON;
58. LEDGER DIGESTS Holds the database hashes Show the state of the data Stored outside the database server Separation of duties Immutable storage & policies
59. Why a DB Designer Loves a LEDGER Table? More trustworthy More protection from DBA/SysAdmin tampering Don’t need or want full blockchain functionality
61. In SSMS – but only version 18 or LOWER
62. Vulnerability Assessment – SSMS 18.x or lower
63. Microsoft Defender for Cloud - Databases
64. How? Secure scores Recommendations Alerting
65. Vulnerabilities
66. Remediating Recommendations
67. VULNERABILITY & DEFENDER IN FABRIC What you get instead Close the gap yourself Use firewalls Entra identity, workspace roles Audit logs Monitor identities & access Sensitivity labels DLP Tenant admin settings Nothing in Defender for Cloud Script your own config checks Review permissions on a cadence The scanner does not come with you, but here’s what you can do
68. THE SAME SECURED DATABASE, NOW ACROSS FABRIC Microsoft Fabric Bobbie Same skills, same instincts SQL Database Fabric SQL Database Fabric Warehouse Fabric SQL Endpoint Identity, access, encryption, auditing Many of the controls you already apply to SQL databases still work with Fabric.
69. Fabric SQL Database Fabric Data Warehouse Always Encrypted (incl. enclaves) No No No No Yes No Yes Row-Level Security (RLS) Yes Yes Yes Yes Yes Yes Yes Dynamic Data Masking (DDM) Yes Yes No No Yes Yes Yes Ledger Tables No No No No Yes No Yes Feature Fabric Lakehouse (SQL Endpoint) Fabric Data Lake (OneLake) Azure SQL Database Mirrored Azure SQL DB SQL Server (on-prem)
70. Bobbie Lookup, Power DBA
71. Key Takeaways Data classifications are required Can’t secure data we don’t understand Security nearest the data DB performance Data pros know data Can’t trust everyone anyone Developer productivity Managing Risk Importance of Laziness
72. One more time… Every Design Decision must be based on Cost, Benefit and Risk @DATACHICK Datachick@mstdn.ca /in/karenlopez