Fabric Warehouse Connection in Excel

Hi Everyone,

I’m trying to provide Excel users with access to my Fabric Warehouse. I have granted them item-level permissions (Read and ReadData) and also provided table-level access using:

GRANT SELECT ON [SchemaName].[TableName] TO [UserOrGroup];

However, when I test the connection from Excel, the warehouse tables are not visible, and the connection fails. I also attempted to connect via SSMS, where authentication succeeds, but I receive an error stating that the database was not found or that I do not have sufficient permissions.

I would prefer not to grant workspace-level permissions (such as Viewer) just to enable connectivity to the warehouse.

Could anyone suggest a better approach or solution to enable secure access for these users?

data warehouse

warehouse

Lodha_Jaydeep
4 months ago
Hi Suyog1,
Idially you are not doing anything wrong. Warehouse discovery from Excel typically required the workspace role (viewer or high) that contains the dedicated warehouse which need to connect. Table and warehouse level access are not efficient to list warehouses.

You can try following practical approches:

1) Dedicated workspace and grant viewer access to the users, This doesn't expose the other artifacts.
2) Do not connect in excel this way try SQL Server DB and use the DB name which user should be able to connect.
3) Create semantic model on top of the warehouse and use Enalyze in Excel (You can use RLS if you have domain data hide/show restrictions)

As of now there is no supported way to discovery of the warehouse directly without having workspace level access.

8 Replies

Lodha_Jaydeep
Solution Sage
4 months ago
Hi Suyog1,

Here are two isses you are facing right?
1) After granting access to the users, warehouse tables are not visible in the excel, Users/Group only can see the tables only for which you have granted access or they have already access.
2) For you, error is geeting for the permission issue/DB not found.
How you are trying to connect the DB?
Try this way, Please confrom if you are using same way.
Open SSSM and
It will pop-up to Authnticate please complete sign-in and it will show you the DBs in the warehouse server.

And also, be make sure you have the Warehouse/Table level access to connect. You can provide read access also if the other users need to connect the endpoint.

Please consider as an accepeted solution if helps, or give some kudos.
- Suyog1
  Frequent Visitor
  4 months ago
  Hi Lodha_Jaydeep,
  
  I am mainly trying to connect via excel rather than ssms as this will be useful for the business users in future.
  I am trying to connect to a Fabric Warehouse from Excel, but I’m encountering an issue. The warehouse and its underlying tables are not visible unless I grant workspace-level access (Viewer or higher). Without this level of access, the warehouse I require does not appear, and I am only able to see warehouses from workspaces where I already have access.
  I have already provided Read/ReadData permissions at both the item (warehouse) level and the table level, but this does not make the warehouse discoverable in Excel.
  However, I would prefer not to grant workspace-level access, as it exposes all endpoints and data within that workspace.
  Please let me know if there is anything that i am doing wrong.
  
  Also, When i Try your approach to connect to ssms with user which doesnot have workspace level permission but has item level permission it shows below error.
  Also, If i provide the user workspace level access even a viewer, i can easily access the warehouse from excel and ssms without any issues.
  - Lodha_Jaydeep
    Solution Sage
    4 months ago
    Hi Suyog1,
    Idially you are not doing anything wrong. Warehouse discovery from Excel typically required the workspace role (viewer or high) that contains the dedicated warehouse which need to connect. Table and warehouse level access are not efficient to list warehouses.
    
    You can try following practical approches:
    
    1) Dedicated workspace and grant viewer access to the users, This doesn't expose the other artifacts.
    2) Do not connect in excel this way try SQL Server DB and use the DB name which user should be able to connect.
    3) Create semantic model on top of the warehouse and use Enalyze in Excel (You can use RLS if you have domain data hide/show restrictions)
    
    As of now there is no supported way to discovery of the warehouse directly without having workspace level access.
tayloramy
Super User
4 months ago
Hi Suyog1,

On top of granting SQL permissions, you also need to share the item with the user. This is a step that a lot of people forget about.
- Suyog1
  Frequent Visitor
  4 months ago
  Hi tayloramy
  
  I tried sharing the item with the user also but it seems to access fabric warehouse through Excel or SSMS, we need to provide workspace level access as well. I tried various ways but could not succeed on it.
  
  Please let me know if you have further ideas or could further help. Thank you.
V-yubandi-msft
Community Support
3 months ago
Hi Suyog1 ,

If you get a chance, please review the latest responses shared by Lodha_Jaydeep . They have correctly pointed out the key points, so kindly check and let us know if you need any additional details.

Thank you all for your valuable support Lodha_Jaydeep .

Regards,

Yugandhar.