Fabric Warehouse as source in MS Access

Hi JVDA98​ 

For most external applications that can navigate the fabric workspace structure, viewer permissions are required. This is true in Excel and in the browser in SSMS as well. 

Though for connect access, all you should need to do is share the data store item with the user, and then grant granular permissions. 

Which data store are you using? A Lakehouse, Warehouse, or SQL Database? 

Hi JVDA98​,

The fact that it fails before you can pick a warehouse is the clue. Without a workspace role, the user can't browse the list of databases on the endpoint, so a DSN that connects to the server first (default database) fails. With only item-level access, the connection has to go straight to the warehouse by name.

Set the database explicitly in the DSN / connection string:

Driver={ODBC Driver 18 for SQL Server};
Server=<your-endpoint>.datawarehouse.fabric.microsoft.com;
Database=<WarehouseName>;
Authentication=ActiveDirectoryInteractive;
Encrypt=yes;

In the ODBC DSN wizard, that's the "Change the default database to" option. Type the warehouse name instead of relying on the dropdown, which needs browse rights.

For permissions, share the warehouse item with the user (or better, an Entra group) without ticking the extra boxes. That gives CONNECT only. Then grant data access in T-SQL:

-- inside the warehouse
GRANT SELECT ON SCHEMA::dbo TO [AccessApp_Users];   -- Entra group
-- or tighter, per object
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO [AccessApp_Users];

This keeps users out of the workspace entirely, and the Access app only sees what the schema grants allow. Using a group means onboarding is just group membership, with no Fabric changes.

I'd steer away from a service principal here. It would mean storing a secret in or next to the Access front end, and you'd lose per-user auditing in the warehouse.

So my overall approach would be:

Share warehouse item with Entra group (no extra checkboxes) -> CONNECT
GRANT SELECT/INSERT/UPDATE on schema or tables to that group
ODBC DSN -> Database = <WarehouseName> explicitly (don't browse)
Relink Access tables against the new DSN

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi JVDA98​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya

Hi JVDA98​,

I would not give the Access users Viewer on the whole workspace just to make the ODBC connection work.

Microsoft’s Warehouse permission guidance states that sharing the Warehouse with no extra options gives the user the Warehouse-level Read permission. That permission is enough to connect to the SQL endpoint, but it does not automatically let them query every table.

From there, you can grant only the schema or tables that the Access application needs with T-SQL, for example:

GRANT SELECT ON SCHEMA::dbo TO [AccessAppUsers];

or grant permissions to specific tables instead.

The other important part is the ODBC connection itself. I would set the Warehouse database explicitly in the DSN or connection string instead of relying on the user browsing for it. If Access tries to enumerate Fabric workspaces/databases first, that can make it look as though broader workspace permissions are required.

So I would use this pattern:

share only the Warehouse item
→ no extra “read all data” permission
→ grant required SELECT permissions with T-SQL
→ configure the ODBC DSN with the exact SQL endpoint and Warehouse database name

That keeps the users out of the rest of the workspace while still allowing the Access application to connect and query only the data it needs.

Microsoft’s Fabric Warehouse security guidance confirms that Warehouse connectivity depends on the Fabric Read permission, while granular SQL permissions control what the user can access after connecting.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi JVDA98​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya

Hi JVDA98

The replies above cover the DSN database name and the GRANTs, so here's the one thing they leave out. Learn says item permissions can take up to two hours to propagate, and that the user should sign in again afterwards. If you removed Viewer and tested straight away with only Read plus ReadData, the failure may just be that delay.

If it still fails after that, keep the database name set in the DSN, as the replies say. Without it you land in master, and a wrong name gives "Login failed for user ... the database was not found, or you have insufficient permissions to connect to it." Which exact error text does Access show?

On a service principal: the SQL connection string accepts one, but Learn says sharing a warehouse directly with one isn't supported. It would need a workspace role, which is what you're trying to avoid.