SOLVED

Reader only access unable to login synapse workspace or SSMS into the database

Copper Contributor

Hello, 

 

Just wondering 

 

My colleagues were placed under the default role provided by Microsoft as a 'reader' under the synapse workspace. 

nickywong_1-1709909440883.png

 

 

But we do not understand why they are unable to signin the workspace or SSMS of synapse. 

sample.png

Would you be able to rectify it for us please? 

8 Replies
best response confirmed by nickywong (Copper Contributor)
Solution

@nickywong 

 

This may be a good place to start (notably steps 6 - 10)

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control#step... 

 

Also make sure they have the correct permissions to the right directories within the data lake if they need to query external tables or views using serverless or lake dbs.

Hello@lcolbert

 

Thank you for getting back to me, 

I literally put the assignment group of my colleagues to connect the data base under the attached. However, she is still unable to access as a reader only.

 

nickywong_0-1710175352479.png

 

Here is the comment that she is unable to access the datawarehouse under the attached. what would make the credential did not work?

nickywong_1-1710175592578.png

Many thanks again 

 

Nick

 

 

So you have a security group created and you've added the group as a user in your database with a role like db_datareader. You've assigned users to that group, and when they try to log in using AAD in SSMS they still can't connect?
Hello I actually created the AAD seperately from db_datareader based on the result I showed on the SSMS. but my colleague still cannot connect to SSMS because of the credential problem, do you think it is the Network or Firewall problem since it has to be login via Virtual machine
It could be. Can you follow the user's access from where they are on the network to the dedicated/serverless sql endpoint you're trying to use in order to log in?
Oh I tested them with my colleague again, apparently she could login via web.azuresynapse.net to view all the data. But still not under SQL management studio.


with the error login failed for user '<token-idenified principal>' Microsoft SQL server, ErrorL 18456)

@lcolbert Sorry, based on my error as above, when the user could go to the datawarehouse via browser web.azuresynapse.net but not under SSMS. Do you think it has something to deal withe the AAD or firewall etc.? 

 

Kind regards

 

Nick 

1 best response

Accepted Solutions
best response confirmed by nickywong (Copper Contributor)
Solution

@nickywong 

 

This may be a good place to start (notably steps 6 - 10)

https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-set-up-access-control#step... 

 

Also make sure they have the correct permissions to the right directories within the data lake if they need to query external tables or views using serverless or lake dbs.

View solution in original post