Most Microsoft Fabric Warehouse deployments fail at security not because the platform lacks controls, but because those controls are never fully written. Teams configure workspace roles and Entra ID, then consider the job done. One ReadAll grant on an analyst account, or a semantic model shipping without Object-Level Security, renders the entire SQL-layer defense irrelevant.
This playbook covers what happens inside the warehouse query editor. The conceptual foundation (Entra ID, network design, workspace roles, item permissions) is in the companion guide, Data Warehouse Security in Microsoft Fabric . This article picks up at the SQL endpoint and works through six implementation layers with copy-pasteable T-SQL and a verification harness for each.
In this article, we cover schema-level GRANT/DENY, database roles, row-level security, column-level security, Dynamic Data Masking , object-level security on semantic models, Purview audit controls, an idempotent deployment wrapper, and a pre-launch security checklist.
Key Takeaways Schema-level GRANT/DENY is the default-deny foundation. Everything else builds on top of it. Skipping this step means every other control is incomplete. RLS predicate functions must use SCHEMABINDING and live in a schema end users cannot SELECT from, or analysts can reverse-engineer the filter logic from sys.sql_modules. CLS via DENY SELECT survives SELECT *. The error message leaks the column name, which matters when the column name itself is sensitive. DDM is not encryption. A user with aggregation rights can still infer masked numeric values, so always pair DDM on salary-type columns with a CLS DENY for the same role. OneLake security is now generally available and enforces RLS and CLS across Spark notebooks and Direct Lake semantic models, not just the SQL endpoint.
Before You Start: What This Playbook Assumes Layers 1 through 4 (identity, network, workspace roles, and item permissions) are configured outside the warehouse query editor, in the Fabric Admin Portal and workspace settings. Those are covered in the companion guide Data Warehouse Security in Microsoft Fabric .
One thing to confirm before running anything below: no analyst account holds ReadAll on the warehouse item. ReadAll bypasses every T-SQL policy in this guide by reading the underlying OneLake Parquet files directly. RLS, CLS, and DDM all become irrelevant via that path.
Scope ReadAll to engineering roles only, then proceed. IBM’s 2025 Cost of a Data Breach Report puts the average global breach at $4.44 million. Post-incident reviews consistently trace root causes to missing SQL-layer controls, not sophisticated attacks.
Not sure which controls apply to your access patterns? Kanerika’s team maps your actual access paths to the right T-SQL controls and identifies the gaps before they become incidents.
Schedule a Demo
Picking the Right T-SQL Control Data Control Selection Framework:
Scenario Right Control Why Secondary Control Different users must see different rows RLS Filters rows transparently without schema changes CLS if specific columns also need restriction Certain columns must be invisible to a role CLS via DENY SELECT Hard block at SQL layer; survives SELECT * OLS if the column also surfaces in a semantic model Support staff need partial field visibility DDM Obscures at query time; underlying data preserved CLS for fields that should be entirely blocked Fields must be hidden from Power BI field list OLS via Tabular Editor Controls semantic model visibility independently CLS at warehouse layer for defense-in-depth Vendors need access to one table only Object-level GRANT Scoped at object level; no schema-wide exposure Item-level ReadData with no workspace role Multiple roles need different column subsets Views per persona More composable than stacking multiple DENYs CLS as backstop on underlying tables Regulated data must stay protected on export Purview Sensitivity Labels Travel with the artifact outside the warehouse DDM + CLS at warehouse layer Complex row and column restrictions for concurrent roles RLS + CLS combined RLS filters rows; CLS restricts columns within filtered rows OLS on downstream semantic models
No single control covers all scenarios. The recurring pattern in well-secured Fabric Warehouse environments is RLS and CLS at the SQL layer combined with OLS on semantic models, with DDM bridging cases where partial visibility is operationally correct.
Layer A: Schema-Scoped GRANT/DENY The default permission state inside a freshly provisioned Fabric Warehouse is closed for non-owners but messy in practice. Most teams discover that someone with workspace Contributor implicitly holds db_owner, which silently overrides every DENY written below. Audit that first with SELECT * FROM sys.database_principals WHERE type IN ('S','E','X'); and remove any unexpected owners.
-- 1. Create one schema per data domain.
CREATE SCHEMA sales AUTHORIZATION dbo;
CREATE SCHEMA finance AUTHORIZATION dbo;
CREATE SCHEMA hr AUTHORIZATION dbo;
CREATE SCHEMA security AUTHORIZATION dbo; -- used by RLS, never readable
-- 2. Revoke the implicit public SELECT that ships on every Fabric warehouse.
REVOKE SELECT ON SCHEMA::sales FROM public;
REVOKE SELECT ON SCHEMA::finance FROM public;
REVOKE SELECT ON SCHEMA::hr FROM public;
-- 3. Hard-deny the security schema to public.
DENY SELECT, VIEW DEFINITION ON SCHEMA::security TO public;VIEW DEFINITION matters. Without it, a curious analyst with SELECT denied can still inspect the predicate body via sys.sql_modules and infer the filter logic. This is the most common RLS-bypass vector in security audits.
Layer B: Database Roles per Persona Database roles are the join point between Entra principals and the schema grants in Layer A. Define one role per data persona, then grant once on the schema rather than once per table.
CREATE ROLE regional_analyst;
CREATE ROLE finance_analyst;
CREATE ROLE data_steward;
GRANT SELECT ON SCHEMA::sales TO regional_analyst;
DENY SELECT ON SCHEMA::finance TO regional_analyst;
DENY SELECT ON SCHEMA::hr TO regional_analyst;
GRANT SELECT ON SCHEMA::sales TO finance_analyst;
GRANT SELECT ON SCHEMA::finance TO finance_analyst;
DENY SELECT ON SCHEMA::hr TO finance_analyst;
GRANT SELECT ON SCHEMA::sales TO data_steward;
GRANT SELECT ON SCHEMA::finance TO data_steward;
GRANT SELECT ON SCHEMA::hr TO data_steward;
-- Bind Entra principals to roles (UPN must match exactly, including domain casing).
EXEC sp_addrolemember 'regional_analyst', '[email protected] ';
EXEC sp_addrolemember 'finance_analyst', '[email protected] ';
EXEC sp_addrolemember 'data_steward', 'data_governance_group';One footgun worth knowing: GRANT and DENY on a schema do not retroactively apply to tables created by other principals in that schema. If a Contributor creates a table in their own implicit schema, the grant does not cover it. The mitigation is to enforce CREATE TABLE through a single deployment service principal that owns every schema.
Layer C: Row-Level Security RLS in Fabric Warehouse uses an inline table-valued function as a predicate, attached to a target table via CREATE SECURITY POLICY. The predicate function must be SCHEMABINDING, must live in a schema that end-user roles cannot read, and must use USER_NAME() rather than SUSER_NAME(). The latter returns the Entra principal’s object ID in Fabric, not the UPN, which silently breaks most predicate joins.
1. Mapping Table and Predicate Function Create the region-to-user mapping inside the locked-down security schema, then write the predicate function that references it.
Mapping table: CREATE FUNCTION security.fn_region_predicate(@region AS NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS result
WHERE
IS_MEMBER('db_owner') = 1
OR EXISTS (
SELECT 1 FROM security.region_access
WHERE user_name = USER_NAME()
AND (region = @region OR region IS NULL)
2. Apply the Security Policy CREATE SECURITY POLICY sales_region_policy
ADD FILTER PREDICATE security.fn_region_predicate(region)
ON dbo.sales
WITH (STATE = ON);
-- Lock down the security schema so analysts cannot read the predicate body.
DENY SELECT, VIEW DEFINITION ON SCHEMA::security TO regional_analyst;
DENY SELECT, VIEW DEFINITION ON SCHEMA::security TO finance_analyst;
If VIEW DEFINITION is omitted, analysts can query security.region_access directly and call the predicate to check whether a specific region value passes the filter.
3. Verification Harness Always run this before promoting the policy to production.
SELECT COUNT(*) AS dbo_visible_rows FROM dbo.sales;
EXECUTE AS USER = '[email protected] ';
SELECT COUNT(*) AS east_visible_rows FROM dbo.sales;
SELECT DISTINCT region FROM dbo.sales; -- should return only 'East'
REVERT;
EXECUTE AS USER = '[email protected] ';
SELECT COUNT(*) AS west_visible_rows FROM dbo.sales;
SELECT DISTINCT region FROM dbo.sales; -- should return only 'West'
REVERT;
EXECUTE AS USER = '[email protected] ';
SELECT COUNT(*) AS manager_visible_rows FROM dbo.sales;
SELECT DISTINCT region FROM dbo.sales; -- NULL mapping = all regions
REVERT;
EXECUTE AS USER = '[email protected] ';
SELECT object_definition(OBJECT_ID('security.fn_region_predicate'));
-- Expected: NULL (DENY VIEW DEFINITION is working)
REVERT;
If an analyst sees zero rows, the most common cause is a UPN casing mismatch. Fix it with UPDATE security.region_access SET user_name = LOWER(user_name); and add LOWER(USER_NAME()) on the lookup side of the predicate.
4. Disable, Alter, Re-Enable Policies cannot be altered while ON. Forgetting the bracket around ALTER FUNCTION is the most common cause of “the predicate change didn’t take effect” tickets.
ALTER SECURITY POLICY sales_region_policy WITH (STATE = OFF);
ALTER FUNCTION security.fn_region_predicate(@region NVARCHAR(50))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT 1 AS result
WHERE IS_MEMBER('db_owner') = 1
OR IS_MEMBER('data_steward') = 1
OR EXISTS (
SELECT 1 FROM security.region_access
WHERE LOWER(user_name) = LOWER(USER_NAME())
AND (region = @region OR region IS NULL)
);
ALTER SECURITY POLICY sales_region_policy WITH (STATE = ON);One nuance worth carrying forward: OneLake security is now Generally Available and enforces the same RLS and CLS rules across Spark notebooks and Direct Lake semantic models. Admin, Member, and Contributor workspace users still bypass OneLake security RLS rules. For workspace-role context, see the foundational guide , and for OneLake-side enforcement see OneLake Security in Microsoft Fabric .
Layer D: Column-Level Security CLS is the column-scoped form of DENY SELECT. The behavior most teams want to verify: the deny applies even when the query uses SELECT *, and the error message tells the user which column was denied.
DENY SELECT ON dbo.customers (ssn, credit_card_number, salary)
TO regional_analyst;
Verify with the same EXECUTE AS USER / REVERT pattern from Layer C. SELECT ssn FROM dbo.customers as the analyst should return a permission-denied error. So should SELECT *. SELECT customer_id, customer_name, region should succeed.
If the column name itself is sensitive, wrap the safe columns in a view and grant on the view instead.
CREATE VIEW dbo.v_customers_safe AS
SELECT customer_id, customer_name, region, country, signup_date
FROM dbo.customers;
GRANT SELECT ON dbo.v_customers_safe TO regional_analyst;
DENY SELECT ON dbo.customers TO regional_analyst;
For multi-persona cases (finance sees salary but not SSN, HR sees SSN but not salary, analysts see neither), stack views per persona rather than chaining column DENYs. The DENY approach scales poorly past two personas because each new role requires a fresh permission audit on every table.Layer E: Dynamic Data Masking DDM rewrites the projected value at query time without touching the stored row. The four built-in functions (default(), email(), partial(), random()) cover almost every real-world scenario. The rare edge cases need a view or computed column because DDM does not support user-defined functions .
ALTER TABLE dbo.customers
ALTER COLUMN email ADD MASKED WITH (FUNCTION = 'email()');
-- Shows: [email protected]
ALTER TABLE dbo.customers
ALTER COLUMN phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');
-- Shows: XXX-XXX-1234
ALTER TABLE dbo.customers
ALTER COLUMN credit_score ADD MASKED WITH (FUNCTION = 'random(300, 850)');
-- Shows: random integer in range
ALTER TABLE dbo.customers
ALTER COLUMN ssn ADD MASKED WITH (FUNCTION = 'default()');
-- Shows: XXXX for strings, 0 for integers
GRANT UNMASK ON dbo.customers TO data_steward;
DDM verification harness:
-- As analyst: should return masked output
EXECUTE AS USER = '[email protected] ';
SELECT TOP 5 email, phone, credit_score, ssn FROM dbo.customers;
-- Expected: [email protected] | XXX-XXX-1234 | random 300-850 | XXXX
REVERT;
-- As data steward: should return raw values
EXECUTE AS USER = 'data_governance_group';
SELECT TOP 5 email, phone, credit_score, ssn FROM dbo.customers;
REVERT;Two operational gotchas: DDM is not encryption, so a user with aggregation rights (SUM, AVG, COUNT DISTINCT) can still infer masked numeric values. Always pair DDM on salary-type fields with a CLS DENY for the same role.
CREATE TABLE AS SELECT and INSERT ... SELECT from a masked source into a target the user owns will write the masked literal into the target permanently. Guard against this by denying CREATE TABLE on user schemas to any role that reads masked columns.
Layer F: OLS Handoff to Power BI Semantic Models Object-Level Security lives in the semantic model, not the warehouse. This layer ends with a TMSL snippet you paste into Tabular Editor. The snippet hides specific columns from a Power BI role mapped to the same Entra group used in the warehouse role above.
{
"name": "RegionalAnalyst",
"modelPermission": "read",
"members": [
{ "memberName": "[email protected] " },
{ "memberName": "[email protected] " }
],
"tablePermissions": [
{
"name": "Customers",
"filterExpression": "[Region] = USERPRINCIPALNAME()",
"columnPermissions": [
{ "name": "SSN", "metadataPermission": "none" },
{ "name": "CreditCardNumber", "metadataPermission": "none" }
]
},
{
"name": "Employees",
"columnPermissions": [
{ "name": "SalaryBand", "metadataPermission": "none" }
]
}
]
}metadataPermission: "none" hides the column from the field list as well as from query results. Using "read" with a DAX filter leaves the column visible but blank, which leaks the schema. After deploying, run EVALUATE TOPN(5, Customers) as the analyst in DAX Studio to confirm the masked columns are gone, not blank.
5. Semantic Model RLS: Companion DAX Row-level filtering inside the semantic model is a separate DAX expression on the same role. It must match the warehouse-side RLS predicate one-for-one.
[Region] = LOOKUPVALUE(
UserRegionMapping[Region],
UserRegionMapping[UserEmail], USERPRINCIPALNAME()
)Warehouse RLS does not auto-inherit to the semantic model. This DAX is the bridge. Review Microsoft Copilot security concerns when these models will be queried by Copilot or Fabric Data Agents, since aggregation prompts can still infer masked numerics.
Layer G: Purview Audit and OPA Integration The native warehouse audit surface in Fabric is exposed through Microsoft Purview , queryable as KQL. The T-SQL side involves two things: emitting structured audit rows on sensitive operations, and providing a read-only view that lets an external policy engine read role and policy state without elevated privileges.
-- Read-only view for OPA or Purview to check policy state without elevated privileges.
CREATE VIEW security.v_policy_state AS
SELECT
p.name AS policy_name,
p.is_enabled,
OBJECT_NAME(sp.target_object_id) AS target_table,
OBJECT_NAME(sp.predicate_function_id) AS predicate_function
FROM sys.security_policies p
JOIN sys.security_predicates sp ON p.object_id = sp.object_id;
GRANT SELECT ON security.v_policy_state TO opa_reader;On the Purview side, the KQL below surfaces every analyst query that hit a Tier-3 table in the last 24 hours.
FabricWarehouseAudit
| where TimeGenerated > ago(24h)
| where Operation in ("Select", "BulkExport")
| where ObjectName has_any ("customers", "employees", "claims")
| project TimeGenerated, UserPrincipalName, ObjectName,
QueryText = substring(QueryText, 0, 200), ResultRows
| order by TimeGenerated descIf you use Open Policy Agent for cross-platform policy evaluation, point it at security.v_policy_state with the opa_reader service principal. The OPA bundle can then refuse deployment of a new Power BI semantic model whenever a referenced warehouse table has is_enabled = 0 on its RLS policy.
Monitoring and Audit Logging Audit logging is not optional. Every SQL query against Fabric Warehouse generates events capturable in Microsoft Purview’s audit log . Treating it as a day-one requirement is one of the most consistent gaps Kanerika’s assessments surface.
Default audit retention is 90 days. Organizations with E5 licensing can extend this to one year. Alert rules should be configured for unusual query volumes, off-hours access, and queries targeting sensitive tables. Configure all three before the first incident.
Dynamic Management Views provide real-time query-level monitoring.
-- Active sessions with login context + currently running query text.
SELECT s.session_id, s.login_name, r.status, r.start_time, t.text AS query_text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1;Building a baseline monitoring query (who is running what against which table right now) is a half-hour task that pays for itself the first time an unusual access pattern surfaces.
Where T-SQL Policies Are and Are Not Enforced T-SQL Policy Enforcement by Access Path:
Access Path RLS Enforced? CLS Enforced? DDM Enforced? OLS Enforced? Direct OneLake Read? SQL Endpoint (SSMS, Azure Data Studio) Yes (Viewer) Yes (Viewer) Yes (Viewer) N/A No Power BI DirectQuery Yes Yes Yes Yes (via OLS) No Power BI Import mode No No No Yes (via OLS) No Spark Notebook via OneLake Security (GA) Yes Yes No No Restricted Spark Notebook (Member/Contributor) No No No No Yes Data Pipeline Copy activityNo No No N/A Yes OneLake Shortcut traversalNo No No No Yes (external) Fabric Data Agent via SQL endpointYes Yes Yes N/A If ReadAll granted KQL / Real-Time Intelligence No (separate security) No No N/A Varies
Power BI Import mode pulls data into the semantic model at refresh time under the service principal’s credentials. Whatever that SP can see gets imported. Row and column restrictions must be applied via OLS and semantic model RLS, not warehouse SQL controls.
This matrix is the most important planning tool for any Fabric Warehouse security design. The access paths where SQL controls do not apply (Spark with elevated roles, pipelines, shortcuts) are exactly where most teams have unexamined exposure.
Real-Time Intelligence in Microsoft Fabric carries its own distinct security model separate from warehouse SQL controls.
Migrating from Synapse or SQL Server Security configurations do not migrate automatically. The table below maps what must be re-issued in the new warehouse.
Source Concept Fabric Equivalent Action Required SQL Server LoginEntra ID User or Service Principal Remap all logins to Entra identities before cutover Windows Integrated Auth Entra ID User (same UPN) Verify UPN matching; no automatic mapping SQL Database Role Fabric SQL Database RoleRecreate role and re-assign membership Linked Server No direct equivalent Redesign as pipeline, shortcut, or cross-workspace query RLS Security Policy Fabric RLS Policy Redeploy predicate function and policy Column DENY (CLS) Column DENY (identical T-SQL) Re-execute DENY statements post-schema migration DDM mask definitions DDM mask definitions Re-apply ALTER COLUMN … ADD MASKED statements Service account (SQL login) Entra Service Principal Create dedicated SP per pipeline with minimum role SQL Agent job schedule Fabric Data Pipeline Redesign as Fabric pipeline with SP auth SQL Server Audit Microsoft Purview Audit LogEnable Purview audit at workspace creation
For teams evaluating migration scope, see Microsoft Fabric migration services , SSIS to Fabric migration guidance , SSAS to Microsoft Fabric , and Databricks to Fabric migration .
Microsoft Fabric Implementation and Migration Kanerika’s Fabric practice covers workspace architecture, access control design, pipeline migration, and security validation before cutover.
Explore the service
Common Security Mistakes and How to Fix Them 1. Granting ReadAll to Analysts This bypasses all SQL-level security including RLS and DDM. Use ReadData only; reserve ReadAll for engineering roles.
2. Assigning Member or Contributor to Analysts Both workspace roles bypass RLS, CLS, and OLS policies entirely. Use Viewer plus item-level ReadData instead.
3. Leaving the SQL Endpoint Publicly Accessible The warehouse becomes reachable from anywhere on the internet. Implement private endpoints or IP allowlisting.
4. Applying RLS Without Locking the Predicate Schema Users can query predicates directly and infer row membership. Deny SELECT and VIEW DEFINITION on the security schema as shown in Layer C.
5. Skipping Semantic Model RLS Warehouse RLS does not auto-inherit to Power BI models. Configure RLS separately in every semantic model that touches the warehouse data .
6. Using Personal Credentials for Pipelines They are over-permissioned and break when the employee leaves. Switch all pipelines to dedicated service principals with minimum role assignment.
7. No DDM in Dev/Test Environments PII often gets copied unmasked from production. Apply identical masking policies to dev and test environments.
8. Ignoring OneLake Shortcut Permissions Shortcuts bypass the security models of both source and destination. Audit shortcut credentials and scope at creation time.
9. No OLS on Semantic Models Sensitive columns stay visible in field lists even when the warehouse is secured. Configure OLS in Tabular Editor for every model touching Tier 2/3 data.
10. No Audit Logging Before Go-Live Without it, there is no forensic trail for incidents or compliance audits. Enable Purview audit logging before the first production query runs.
Security Configuration Checklist Identity and Network
All automated pipelines use dedicated service principals Conditional Access Policy enforcing MFA applied to the Fabric workspace security group Private endpoints configured, or IP allowlisting in place as interim measure “Allow downloads” tenant setting restricted to approved groups “Share with entire organization” disabled in tenant settings
Workspace and Item Permissions
Analyst accounts hold Viewer workspace role, not Member or Contributor ReadData granted at item level for analysts; ReadAll audit complete Separate workspaces for engineering and analytics teams OneLake shortcuts reviewed for credential scope and ReadAll exposure OneLake security roles configured for lakehouse items analysts access via Spark
SQL and Data Layer
Default DENY applied at schema level; grants explicit and role-based RLS policies deployed with locked predicate schemas for all Tier 2/3 tables CLS applied to PII columns across sensitive tables DDM applied to fields requiring partial visibility CMK encryption enabled at workspace level for Tier 3 and regulated data workspaces OLS configured on all semantic models touching Tier 2/3 data Power BI semantic model RLS validated separately from warehouse RLS
Audit and Monitoring
Purview audit logging enabled with retention period set Alert rules configured for off-hours access and high query volumes on sensitive tables Baseline DMV monitoring query documented and scheduled Purview sensitivity labels applied to Tier 2 and Tier 3 workspace items
Transform Your Business with AI-Powered Solutions! Partner with Kanerika for Expert AI implementation Services
Book a Meeting
How Kanerika Secures Fabric Warehouse Deployments Kanerika is a Microsoft Solutions Partner for Data and AI with Analytics Specialization and Microsoft Fabric Featured Partner, covering all six layers of the Fabric security stack simultaneously across financial services, healthcare, and logistics implementations.
KANGuard is Kanerika’s continuous security monitoring product built natively on Microsoft Purview. It runs permission drift detection, ReadAll exposure scans, cross-workload path analysis, RLS predicate integrity checks, sensitivity label coverage reporting, and service principal hygiene monitoring across every Fabric workspace on a configurable schedule. When a Contributor role appears on an analyst account that should hold Viewer, KANGuard raises an alert before the next notebook run.
KANComply maps Fabric’s native security controls to specific regulatory framework clauses (HIPAA, SOX, GDPR, PCI DSS) and produces audit-ready evidence packages at the cadence auditors require, without manual assembly by the engineering team. For organizations in regulated industries, this cuts what would otherwise be a three-week manual documentation effort to two days. See data security best practices and OneLake security in Microsoft Fabric for related governance depth.
Case Study: SSIS to Microsoft Fabric Migration with Security Validation A global logistics firm running SQL Server Integration Services needed to re-platform its data pipelines onto Microsoft Fabric Data Factory. Security validation was a defined deliverable before any production cutover.
Challenges: 40+ SSIS packages using SQL logins with no Entra ID equivalents Existing RLS policies built against Windows Integrated Auth. UPNs did not match post-migration No documented service account inventory; several pipelines running under personal credentials No audit logging configured on the source system
Solutions: Kanerika ran a pre-migration security audit documenting all permission grants, role memberships, RLS policies, and service account usage SQL logins remapped to dedicated Entra service principals; personal credential pipelines redesigned with minimum-privilege SPsRLS predicates redeployed using USER_NAME() with LOWER() normalization to handle UPN casing mismatches DDM masks re-applied post-migration via the idempotent deployment wrapper; CLS DENYs re-executed on all Tier 2/3 tables
Results: 30% Improvement in Data Processing Speeds 40% Reduction in Operational Costs 25% Decrease in Manual Maintenance Efforts
Wrapping Up Microsoft Fabric Warehouse provides every control an enterprise needs to secure sensitive data. The failure mode is almost always incomplete configuration. One layer gets secured while another is left open.
Three principles guide every Kanerika Fabric security implementation: start with data classification so sensitivity tier drives control selection, test every access path including SQL endpoint, Spark, OneLake shortcuts, and Power BI semantic models, and build audit infrastructure before it is needed. Purview logging configured at day one means forensic evidence exists if something goes wrong.
For organizations already running Fabric Warehouse, a KANGuard review covering all six layers, cross-workload path analysis, and regulatory control mapping typically completes in two to three weeks and produces a prioritized remediation roadmap. Kanerika’s Microsoft Fabric consulting practice has delivered this review across implementations in financial services, healthcare, and logistics.
FAQs What does this playbook cover that the foundational Fabric Warehouse security guide does not? This playbook is the T-SQL implementation companion. The foundational guide at Data Warehouse Security in Microsoft Fabric covers the conceptual model, Entra ID, network design, workspace roles, item permissions, and risk sequencing. This piece picks up at the SQL endpoint and walks through six T-SQL layers with copy-pasteable scripts and EXECUTE AS USER verification harnesses for each.
Why does my RLS predicate function need SCHEMABINDING in Fabric Warehouse? SCHEMABINDING locks the function to the columns and tables it references, which is required for the security policy to attach. Without it, CREATE SECURITY POLICY … ADD FILTER PREDICATE returns a binding error. The schema containing the function must also be denied to end-user roles, otherwise the function body is readable via sys.sql_modules and the filter logic can be reverse-engineered.
Why does SUSER_NAME() return an object ID instead of the UPN in Fabric Warehouse? Fabric Warehouse authenticates exclusively through Entra ID, so the server-level principal is the Entra object ID, not a SQL login. Use USER_NAME() inside RLS predicates and audit columns. It returns the UPN that matches the strings used in sp_addrolemember calls and mapping tables.
How do I implement row-level security in Microsoft Fabric Warehouse? Create a mapping table inside a locked-down security schema, write an inline TVF predicate with CREATE FUNCTION … RETURNS TABLE WITH SCHEMABINDING, attach it with CREATE SECURITY POLICY, then verify with the EXECUTE AS USER harness shown in Layer C. To alter the predicate later, run ALTER SECURITY POLICY … WITH (STATE = OFF), change the function, then re-enable.
How do I prevent unauthorized column access in Microsoft Fabric Warehouse? For most cases, use DENY SELECT ON dbo.table_name (column1, column2) TO role_name. This propagates to SELECT * but the column name leaks in the error. When the column name itself is sensitive, wrap the safe columns in a view, grant on the view, and deny SELECT on the underlying table. For Power BI semantic models, use the TMSL snippet with metadataPermission: “none” to hide the column from the field list.
Do OneLake shortcuts inherit security from the source system? No. OneLake shortcuts create an independent access path governed by the credentials bound to the shortcut at creation. Any user with ReadAll on the workspace item can traverse it to the external source. Shortcuts must be reviewed as part of any security audit.
Does Microsoft Fabric Warehouse support private endpoints for network isolation? Yes. Private endpoints restrict the warehouse SQL endpoint to approved virtual networks. Tenant-level settings in the Fabric Admin Portal provide additional controls for export restrictions and external sharing.
How should security be handled when migrating from Azure Synapse or SSIS to Fabric Warehouse? Security configurations do not migrate automatically. SQL logins must be remapped to Entra ID identities, database roles must be recreated, RLS policies must be rebuilt with new predicate deployments, and pipeline service accounts must be re-provisioned as Entra service principals. A pre-migration security audit and post-migration parallel validation are needed before production cutover.
