Microsoft Fabric Warehouse - Configure Access and Permissions

By: Koen Verbeeck   |   Updated: 2024-07-02   |   Comments   |   Related: > Microsoft Fabric

Problem

We are starting a new analytics project in Microsoft Fabric, and our data will land in a warehouse. This is the first time we're using Fabric, and we are wondering about the different options for sharing access to a warehouse we developed in a workspace.

Solution

Microsoft Fabric is a centralized, Software-as-a-Service (SaaS) data analytics platform. One of the workloads in Fabric is the warehouse, where you can store, transform, and query data in an environment very similar to a SQL Server database. It's not exactly the same because, behind the scenes, all tables are stored as delta tables (which use Parquet files as the storage format). For more information about the warehouse, check this tip: What are Warehouses in Microsoft Fabric?

For this tip, we'll look at how to share access to a warehouse so a user can query the data inside it. We will focus on just giving access. (Granular permissions and inbound/outbound security, such as VPN gateways, Azure Private Link, etc., are out of scope for this tip. Please refer to the documentation for more information on those subjects.)

How to Give a User Access to a Fabric Warehouse

Let's suppose we have a user named Alice, who we want to give access to a warehouse. Fabric only supports authentication through Azure Entra ID (formerly known as Azure Active Directory), so we need to create this user in Azure Entra ID:

When this user logs into Fabric for the first time, they might see the following message:

It's important to note that the user doesn't have a Power BI Pro license, which isn't needed to work with Fabric items (except for Power BI items).

Initially, new users will only have access to their own workspace called My workspace:

Workspace Roles

To give Alice access to an existing warehouse, we can assign workspace roles. When logged in as a workspace admin, go to the workspace and click on Manage access.

In the sidebar, click on Add people or groups.

Enter the name or email of the user you want to assign to a role.

You can select which role you want to assign to the user. These are the available workspace roles:

• Viewer – the user can view all content but can't modify any of it. This is the default role.
• Contributor – the user can view and modify all content.
• Member – the user can view, modify, and share all content. A member can also add other members.
• Admin – all the above and the ability to manage permissions. An admin can also delete the workspace.

The following table – taken from the Microsoft documentation – gives a high-level overview of the differences between the roles:

For a more detailed description of the roles, check out Roles in workspaces in Microsoft Fabric. When Alice is added to the Viewer role, she can now access the workspace and query all objects.

From the warehouse context menu (click on the ellipsis next to the name), the user can get the connection string to the warehouse and connect using other tools such as SQL Server Management Studio (SSMS).

She can use her email to log in (keep in mind SQL authentication is not supported):

Alice can see all warehouses (and SQL analytical endpoints) in the workspace and query their tables:

However, she only has read-only access. Modifying data is not permitted.

Assigning Alice a Viewer role on the workspace was perhaps a bit too broad, as all objects can be viewed and queried. Let's find out if there's an option to grant access only to a single warehouse.

Let's remove Alice from the Viewer role (using the same Manage access pane as before). If Alice was still logged into Fabric, it seems she can view the different objects in the workspace but not access them.

The workspace is no longer accessible only after Alice has signed out and logged in again.

Share the Warehouse with a User

Sharing is a better option than assigning the user to a workspace role if you want to give a user access to only a single item in a workspace. As an admin or member, go to the warehouse in the workspace, open the context menu, and click Share.

In the pop-up, add Alice as the recipient.

Three permissions can be assigned:

• Read all data using SQL: This is the ReadData permission, equivalent to the db_datareader role in SQL Server.
• Read all OneLake data: This is the ReadAll permission, and it used to be called "read all data using Apache Spark." With this permission, the user can read the underlying Parquet files of the warehouse tables stored in OneLake.
• Build reports on the default semantic model: This is the Build permission, which allows the user to build Power BI reports on the default model of the warehouse. This permission is selected by default.

If all permissions are deselected, the user only gets the Read permission, which is the ability to connect to the warehouse, similar to the CONNECT permission in SQL Server. When the warehouse is shared, the user will get an email with a notification (if this option wasn't deselected from the menu):

The link provides access to the OneLake hub, where you can find the connection string to the warehouse and some quick actions:

You can also find a link to the default semantic model:

It's possible you'll get some pop-up to start a free Pro trial along the way. Because the warehouse itself is directly shared, Alice can't see the workspace in the list:

The only way to find the warehouse is through the OneLake data hub. When opening the warehouse, Alice can view and query the tables (if at least the ReadData permission is assigned):

However, modifying the data is not possible:

Using the connection string (found in the OneLake hub), Alice can connect to the warehouse using other tools like SSMS. Don't forget to enter the database name in the connection properties:

If you don't, you'll get an error stating the login failed:

After logging into the warehouse service, Alice can only see warehouses that have been shared with her:

When Alice was assigned the workspace Viewer role, she could see all warehouses and SQL Analytical endpoints, but now she can only see one warehouse.

If, at some point, we want to revoke the permissions shared with Alice or extend them, we can do this in the Manage Permissions pane, which can be found through the context menu:

Here, we can find a list of all users who have access to the warehouse and their roles and permissions:

By clicking on the ellipsis next to a user, we can either remove permissions, add missing permissions, or remove access altogether:

Alice can read all the data on all the tables of the warehouse.

In the next tip, we'll dive deeper into the security options of the warehouse to further restrict access by using row-level security and column-level security.

Next Steps

• If you want to get started with Fabric, you can try a free trial.
• An overview of all Fabric tips can be found in this overview.
• Stay tuned for more tips on security in Microsoft Fabric!

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips