Forum Discussion
Clarification on Access Control for SQL Endpoints in Fabric Workspaces
Hi everyone,
I’m trying to better understand the current access control model for SQL endpoints in Microsoft Fabric. Based on my experience, it seems that:
- Any user with a Viewer role in a workspace automatically gains read access to all SQL endpoints within that workspace.
- Permissions set via Manage Permissions on the SQL endpoint cannot override or restrict the inherited access from workspace roles.
- Users must be added to the workspace (with at least a Viewer role) to authenticate and access a SQL endpoint, even if they have explicit permissions set via Manage Permissions for the endpoint.
This effectively means that if I want to limit access to a specific dataset or SQL endpoint to only a subset of users, I would need to create a dedicated workspace for that resource, even if it means duplicating workspaces for similar datasets.
Questions:
- Is this the intended behavior, or am I missing a configuration that allows for more granular access control?
- Are there any plans to introduce a feature that allows for finer-grained permissions at the SQL endpoint level without relying entirely on workspace roles?
- Alternatively, would it be possible to implement an access role that grants authentication and data access to specific resources (like SQL endpoints) without also granting view or edit permissions for other resources in the workspace?
I’m finding it challenging to manage access without segmenting resources into separate workspaces, which feels cumbersome and counterintuitive.
Looking forward to hearing your thoughts and insights!
Best regards,
Jens
Hi SwedishJAS you don't have to add any permissions at the workspace level if you need granular permissions for a SQL Endpoint. To run through a scenario, if I had a user "User One" and I just wanted them to have read access on all tables in a particular SQL Endpoint I would
- Go to the Lakehouse/Warehouse and select Manage Permissions
- Select Add User and find the user/group e.g. User One
- At this stage I can select Read All SQL Endpoint Data
I could just leave this setting and click OK, then User One has access to the SQL Endoint I have granted. I don't need to add User One at the workspace level.
If I want even more granular control I can:
- Go to the Lakehouse/Warehouse and select Manage Permissions
- Select Add User and find the user/group e.g. User One
- Do not select any options in the dialog box (this provides access called CONNECT)
- Then I can go into the SQL Endpoint and issue granular permissions like GRANT SELECT ON <object> TO User/Group
Hope this helps
Can you point me towards documentation/videos that state that users need to be added to Workspaces? I have confirmed that adding users to a workspace is not required, I have tested this myself with both SSMS and browsing the OneLake catalog.
What version of SSMS are you using?
Hi SwedishJAS ,
I tried to reproduce your exact scenario and tried around various combinations of workspace roles and permissions to check how to connect to SQL endpoint without granting access to Workspace.When i gave the user Read and ReadAll permission without giving any workspace role was able to connect to SSMS without any issue, faces the same issue as you while connecting with only Read permission.
Hope this helps youDid I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Thanks and Regards
6 Replies
Hi SwedishJAS you don't have to add any permissions at the workspace level if you need granular permissions for a SQL Endpoint. To run through a scenario, if I had a user "User One" and I just wanted them to have read access on all tables in a particular SQL Endpoint I would
- Go to the Lakehouse/Warehouse and select Manage Permissions
- Select Add User and find the user/group e.g. User One
- At this stage I can select Read All SQL Endpoint Data
I could just leave this setting and click OK, then User One has access to the SQL Endoint I have granted. I don't need to add User One at the workspace level.
If I want even more granular control I can:
- Go to the Lakehouse/Warehouse and select Manage Permissions
- Select Add User and find the user/group e.g. User One
- Do not select any options in the dialog box (this provides access called CONNECT)
- Then I can go into the SQL Endpoint and issue granular permissions like GRANT SELECT ON <object> TO User/Group
Hope this helps
Hi AndyDDC,
Thank you for your detailed explanation and walkthrough! Your solution was actually my first approach since it really makes sense that users can be added to the SQL Endpoint without being part of the workspace permissions.
However, I ran into the following issue when attempting to connect to the SQL Endpoint using SQL Server Management Studio (SSMS):
Error message:
sqlTITLE: Connect to Server------------------------------ Cannot connect to xnb6epbstkvehph4bskzardro4-gzozj7rlzjyunl3uzqzmtneqwm.datawarehouse.fabric.microsoft.com. ------------------------------ ADDITIONAL INFORMATION: Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-errorAfter investigating further, I found that the only way to resolve this issue was to add the user to the workspace. To double-check, I reviewed the documentation, YouTube videos, and discussions, and these sources confirmed that adding the user to the workspace is required.
My question to you is: Have you verified that it actually works for you in practice to add users to the SQL Endpoint without including them in the workspace? Or did you, like me, make the assumption that it should work based on the design?
Thanks again for sharing your solution! It would be very interesting to hear if you've successfully managed to get this working without workspace permissions.
Hi SwedishJAS ,
I tried to reproduce your exact scenario and tried around various combinations of workspace roles and permissions to check how to connect to SQL endpoint without granting access to Workspace.When i gave the user Read and ReadAll permission without giving any workspace role was able to connect to SSMS without any issue, faces the same issue as you while connecting with only Read permission.
Hope this helps youDid I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Thanks and RegardsI've just had this exact issue and it was due to SSMS trying to connect to the <default> database. If you've just given access to a single lakehouse/warehouse then there isn't a default.
The solution in SSMS is: before clicking Connect, go into Options and under Connection Properties type in the name of your lakehouse.
Can you point me towards documentation/videos that state that users need to be added to Workspaces? I have confirmed that adding users to a workspace is not required, I have tested this myself with both SSMS and browsing the OneLake catalog.
What version of SSMS are you using?
- A big thank you to both of you—I’ll give it another shot next time. This time, I worked around the issue by creating a dedicated workspace for a SQL endpoint. I’m glad that a dedicated Fabric capacity is no longer required. 🙂
I trust your explanations, and that does seem like the most reasonable scenario. I couldn’t find the description I initially interpreted as confirming the worse situation—it might have been incorrect or outdated.
Thanks again for your quick and detailed responses!
/Jens
