Learn how to secure Microsoft Fabric Warehouses end-to-end. Explore private links, conditional access, workspace roles, item-level permissions, and granular T-SQL controls for object, row, and column-level security. Plus, see dynamic data masking and OneLake security for SQL endpoints in action.
End-to-End Security for Data Warehousing in Microsoft Fabric Shabnam Watson Microsoft Data Platform MVP Principal Consultant, ABI Cube Shabnam Watson Data Consultant, Speaker, author, blogger, Microsoft Data Platform MVP Azure Data & AI, Power BI & Fabric www.shabnamwatson.com /ShabnamWatson https://www.youtube.com/@ShabnamWatson Microsoft Fabric AI OneLake Purview Access Control Fabric Tenant Workspace Item Microsoft Entra ID Object/Column/Row Network Security Fabric Tenant Workspace Item Inbound Public IP or limited? Outbound Object/Column/Row Any IP or limited? Warehouse Secured with Entra ID Network isolation workspace roles SQL-level permissions EntraID Fabric Tenant Microsoft Entra ID Identity Management in Microsoft Fabric User Authentication: Microsoft Entra ID. SSO Integration: Users log in with their Microsoft 365 credentials. Token-Based Access: Entra ID issues OAuth 2.0 / OpenID Connect tokens to authorize actions. Conditional Access in Entra ID Policy Engine*: Enforces context-aware access controls. • Who: User or group identity. • Where: Location/IP restrictions. • What: Device compliance (e.g., managed device). • Risk: Sign-in risk level (e.g., unfamiliar behavior). • Session Controls: Limit access duration or require re-authentication.
Requires Microsoft Entra ID P1 licenses or Microsoft 365 Business Premium licenses What is Conditional Access in Microsoft Entra ID? - Microsoft Entra ID | Microsoft Learn Conditional Access Policy Warehouse Secured with Entra ID Network isolation workspace roles SQL-level permissions Network Security Fabric Tenant Inbound Outbound Any IP or limited? Any IP or limited? Network Security • Inbound Security Protects internal data from external threats. • Outbound Security Ensures secure sharing of data outside workspace boundaries. Network Security: All about connections Outbound Connection Inbound Connection Data Fabric Data Inbound Security • Protects internal data from external threats • Tenant and workspace levels • Secure access to Fabric items from trusted networks • Blocks unauthorized inbound traffic Big Picture Virtual Network Workspace A Restricted inbound access VM with no public IP Public Internet Workspace B Allow all access Tenant Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Public Internet Workspace B Allow all access Workspace Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Public Internet Workspace B Allow all access Workspace Level Pattern Admins/Developers VNET Workspace A Warehouse Private Data Access Workspace B Report/Model Business Users Public Internet Workspace level wins over Tenant level Tenant Public Access Workspace Public Access Private Link in Effect Allowed Blocked Workspace Blocked Allowed Tenant Blocked Blocked Workspace Use tenant and workspace private links - Microsoft Fabric | Microsoft Learn Workspace Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Workspace B Allow all access Workspace Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Private Link for workspace A Workspace B Allow all access Workspace Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Private Endpoint Private Link for workspace A Workspace B Allow all access Workspace Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Private Endpoint Private Link for workspace A Workspace B Allow all access Workspace Level Control Virtual Network Workspace A Restricted inbound access VM with no public IP Private Endpoint Private Link for workspace A Public Internet Workspace B Allow all access Steps to Limit Inbound Connectivity Requirements Create a Private Link Service Create a VM/VNet Private Endpoint in the VNet Check access from VNet Deny public access to the workspace Before you get started • Make sure you have enough permissions! • Make sure you have an Azure (tenant or lower) admin ready to work with you! Tenant-level Private Link Block Public Internet Access to tenant Workspace-level Private Links Private Link JSON { "schema": "http://schema.management.azure.com/schemas/2015-0101/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": {}, "resources": [ { "type": "Microsoft.Fabric/privateLinkServicesForFabric", "apiVersion": "2024-06-01", "name": “TheSeventhLink", "location": "global", "properties": { "tenantId": “039487-98233-…", "workspaceId": “4783adv-…" } } ] } Private Link JSON tenant-id workspace-id Tenant Level Private Link { } "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#", "contentVersion": "1.0.0.0", "parameters": {}, "resources": [ { "type":"Microsoft.PowerBI/privateLinkServicesForPowerBI", "apiVersion": "2020-06-01", "name" : "", "location": "global", "properties" : { "tenantId": "" } } ] Private Endpoint • Private IP Address Connects services to your virtual network using a private IP. • Brings Services into Your VNet Treats external services as part of your internal network. Disabling Public Access from Workspace Disabling Public Access with Code Supported Item Types • Lakehouse, SQL Endpoint, Shortcut • Notebook • Pipeline, Copy Job, Dataflows Gen2 (CI/CD) • Warehouse • Mirrored database • Eventstream, Eventhouse Outbound Security • Enforces workspace outbound access protection • Routes outbound traffic through managed private endpoints to a virtual network. • Prevents unsecured connections from public IPs • Enables protection per workspace Outbound Access Protection https://learn.microsoft.com/enus/fabric/security/workspace-outbound-accessprotection-overview Outbound Access Protection https://learn.microsoft.com/enus/fabric/security/workspace-outbound-accessprotection-overview Outbound Access Protection Workspace Managed Identity Secure Access to External Data Enables Copy Into and Shortcuts without using user credentials Works with ADLS Securely Supports public, firewall-restricted, and block public access storage Warehouse Secured with Entra ID Network isolation workspace roles SQL-level permissions Workspace Roles Development Team Collaboration Admin Member Contributor Viewer Workspace: Manage Access Workspace Roles Workspace: Admin, Member, Contributor, Viewer Microsoft Entra ID Workspace Roles Capability Update and delete the workspace. Add or remove people, including other admins. Add members or others with lower permissions. Allow others to reshare items. Create or modify warehouse items. Admin Membe Contributo Viewer r r Reshare allows Contributors and Viewers to share. https://learn.microsoft.com/en-us/fabric/fundamentals/roles-workspaces Workspace Roles Capability Connect to SQL analytics endpoint of Lakehouse or Warehouse Read data with T-SQL through TDS endpoint (ReadData). Read data through OneLake APIs and Spark (ReadAll). Admin Membe Contributo Viewer r r https://learn.microsoft.com/en-us/fabric/fundamentals/roles-workspaces Sharing with users outside workspace • Use case: • Not immediate development team • SQL Analysts • Report Developers • QA testers https://learn.microsoft.com/en-us/fabric/fundamentals/roles-workspaces Warehouse Item level Permissions Connect ReadData (SQL) ReadAll (Files) Monitor Audit Reshare SQL Endpoint Item level Permissions Connect ReadData All SQL data ReadAll All OneLake Workspace Roles Capability Connect to SQL analytics endpoint of Lakehouse or Warehouse Read data with T-SQL through TDS endpoint (ReadData). Read data through OneLake APIs and Spark (ReadAll). Admin Membe Contributo Viewer r r https://learn.microsoft.com/en-us/fabric/fundamentals/roles-workspaces Warehouse Secured with Entra ID Network isolation workspace roles SQL-level permissions SQL Level Permissions Connect & ReadData Viewer / non-workspace member with Connect and ReadData SQL Level Permissions: Object Level Security (OLS) Connect & fine grain control Viewer / non-workspace member SQL-Level Permissions Connect & ReadData Connect • Grant Update • Deny Select • Grant Select • Grant Update SQL-Level Permissions Grant Update, Insert .. Grant Select On Geography On Geography To User1@....com To User3@....com DENY • Deny Select on dbo.Medallion to TestUser1@....com • Deny has precedence over ReadData even for workspace viewers REVOKE Remove a previously granted permission. Revoke Select On MyTable To [UserA]; CONTROL Gives a principal (user or role) full access to a securable Grant Control on Table to User Use sparingly Does not overwrite Deny SQL Level Permissions: Column Level Security (CLS) Connect & fine grain control Viewer / non-workspace member SQL-Level Permissions Connect & ReadData Connect • Grant Update • Deny Select • Grant Select • Grant Update SQL-Level Permissions Deny Select On Customer(BirthDate) To [User2@...com] Grant Select On Customer(Name, Phone) To [User3@....com] SQL Level Permissions: Column Level Security (RLS) Connect & fine grain control Viewer / non-workspace member Row Level Security Different users see different rows Row Level Security Schema Function Security Policy Row Level Security: Schema CREATE SCHEMA Security; Row Level Security: Function Create Function Security.RLSFunction (@SalesRep AS nvarchar(50)) Returns Table With SCHEMABINDING AS Return Select 1 as RLSFunction_result Where @SalesRep = USER_NAME() Or USER_NAME() = admin@....com'; Row Level Security: Security Policy Create SECURITY POLICY SalesRLS Add Filter PREDICATE [Security].RLSFunction On dbo.Orders With (State = On); Row Level Security: Warning SELECT 1/(Salary – 100,000) FROM EmployeeSalaries Where Name = ‘Bob Jones’ It is possible to leak some data. Row-Level Security in Fabric Data Warehousing - Microsoft Fabric | Microsoft Learn OLS, RLS, CLS Can we get even more fine grain? What if we want User 1 to only see the last 4 characters of a column? Dynamic Data Masking (DDS) With DDS, users see different values in the same column. COLUMN MASKED WITH (FUNCTION = '……………………') default(), random(18, 100) partial(0,"XXXXXXXXXXX",4) email() Dynamic Data Masking Example CREATE TABLE dbo.CustomerMasked ( CustomerID INT, FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1,"-",2)') , LastName VARCHAR(100) MASKED WITH (FUNCTION = 'default()') , Age INT MASKED WITH (FUNCTION = 'random(18, 100)'), BirthDate DATE MASKED WITH (FUNCTION = 'default()') , CreditCardNumber VARCHAR(16) MASKED WITH (FUNCTION = 'partial(0,"XXXXXXXXXXX",4)'), PhoneNumber VARCHAR(15) MASKED WITH (FUNCTION = 'partial(0,"XXXXXXXXXXX",4)') , EmailAddress VARCHAR(100) MASKED WITH (FUNCTION = 'email()') ); Dynamic Data Masking Example Dynamic Data Masking: Warning SELECT ID, Name, Salary FROM EmployeeSalaries Where Salary > 99,999 and Salary <100,001 Data is masked but is still accessible in the where clause. It is possible to infer some data. Dynamic data masking in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn Warning! Removing a user from a workspace role / sharing items does not remove the SQL Logins! OPENROWSET SELECT * FROM OPENROWSET( BULK 'https://onelake.dfs.fabric.microsoft.com///Files/file', FORMAT = 'PARQUET’or CSV ); OPENROWSET SELECT * FROM OPENROWSET( BULK 'https://onelake.dfs.fabric.microsoft.com/Workspace-ID/LakehouseID/Files/customers.parquet', FORMAT = 'PARQUET' ); PARQUET is case sensitive. Audit Logs What it Tracks: Data access, schema & permission changes, and sign-ins Where it Lives: Stored in OneLake, queried with T-SQL How It Works: Select specific actions to log (SELECT, INSERT, EXECUTE) Security Value: Supports compliance and detects suspicious activity Setup Required: Not enabled by default; requires Audit permission Best Practices Control Access: Use Conditional Access and Private Links Limit Exposure: Restrict outbound connectivity Least Privilege: Grant only required workspace and SQL permissions Layered Protection: Apply RLS, CLS, and object-level security Monitor & Audit: Track activity with audit logs Shabnam Watson Data Consultant, Speaker, author, blogger, Microsoft Data Platform MVP Azure Data & AI, Power BI & Fabric www.shabnamwatson.com /ShabnamWatson https://www.youtube.com/@ShabnamWatson @shbWatson Sound off. The mic is all yours. Influence the product roadmap. Join the Fabric User Panel Join the SQL User Panel Share your feedback directly with our Fabric product group and researchers. Influence our SQL roadmap and ensure it meets your real-life needs https://aka.ms/JoinFabricUserPanel https://aka.ms/JoinSQLUserPanel