This article is contributed. See the original author and article here.

I recently worked on some tenant user scenarios, as a part of POCs, with customers on Azure Synapse Analytics (preview). These are more ‘what if’ kind scenarios scoped for Synapse studio (preview).  It is important to understand the kind of access to be given to users so that they can work on Synapse studio.

 

Background

Before we start,  please note https://web.azuresynapse.net works on AAD authentication. I have five AAD users (user1 to user4) and two AAD groups ‘pocsqladmin’ and ‘pocsynapsereader; in office365 tenant. User5 is in my Org tenant.

I am owner of workspace and have created SQL Pool – ‘synapsesqlpool’ and Spark Pool –‘pocsparkpool’.

AAD group – Pocsqladmin is contributor in workspace and active directory admin of SQL Pool. It is being added as workspace, sql and spark admin from synapse studio.  Also, this group has RBAC role in storage account. Additionally, I have added pocsqladmin as user in SQL Pool under security.

 

CREATE USER [pocsqladmin] FROM EXTERNAL PROVIDER

GO

GRANT CONTROL ON DATABASE::synapsesqlpool TO [pocsqladmin];

 

Scenarios

  1. User Senario1 –> User with contributor role is subscription

If user’s role in subscription = contributor then it gets inherited to synapse workspace as contributor. Following errors will be seen in synapse studio if no other permission is given.

tejalsd_0-1597998742445.png

 

Mitigation:

  1. Add user as workspace admin/SQL pool admin etc.
  2. Or add user to pocsqladmin AAD group.

With a and b, you can create pipeline, notebook, can use mange tab etc. But this will only give select permission on objects of SQL pool. You will receive following error, if you issue ‘Create’ command

Failed to execute query.

Error: The specified schema name “user1@org.onmicrosoft.com” either does not exist or you do not have permission to use it.

 

Add user explicitly to the SQL Pool under security with appropriate grant permission.

CREATE USER [user1@org.onmicrosoft.com] FROM EXTERNAL PROVIDER

GO

GRANT CONTROL ON DATABASE::synapsesqlpool TO [user1@org.onmicrosoft.com];

 

Please refer Database Engine Permissions poster for a list of all the permissions

 

  1. User Scenario2 –> user with reader role in subscription and member of AAD group ‘pocsqladmin’

If  user’s role in subscription = reader then it gets inherited to workspace as reader but as it is member of AAD group, he/she can to access entire workspace including manage tab, spark pool etc. Add user to SQL pool, similar to user1.

 

  1. User Scenario3 –> user with reader role in subscription and added as  
    pocsynapsereader from synapse studio.

 

If  user’s role in subscription = reader then it gets inherited to workspace as reader. User3 is part of reader group, it gets following error while browsing synapse studio.

 

SynapseAccessDenied.PNG

 

  1. User Scenario4 –> user with reader role in subscription and added as spark pool admin

If  user’s role in subscription = reader then it gets inherited to workspace as reader

Spark pool admin – with this access user will not be able to browse SQL Pool and also get following error while running notebook or browsing storage.

User4Errors.PNG

 

e.g. %%pyspark

data_path = spark.read.load(‘abfss://pocsynapsefs@pocsynapsesa.dfs.core.windows.net/Test/TestEmpInsertFromTxt.txt’, format=’text’)

data_path.show(100)

Error: 

Py4JJavaError : An error occurred while calling o127.load. : Operation failed: “This request is not authorized to perform this operation using this permission.”, 403, HEAD, https://storageaccount.dfs.core.windows.net/filesystem/Test/TestEmpInsertFromTxt.txt?upn=false&action=getStatus&timeout=90 at org.apache.hadoop.fs.azurebfs.services.AbfsRestOperation.execute(AbfsRestOperation.java:166)

 

Mitigation: Make user as storage blob data contributor to run notebooks and browse ADLS

 

  1. User Scenario5 –> User from different tenant and subscription

Synapse analytics workspace is created in Office 365 tenant and user is expecting to be able to add other users from his organization tenant.

User will not able to see users(different tenant/subscription) in drop down list of manage tab in Synapse Studio.

Run following command and check if user is added in workspace subscription.

Get-AzRoleAssignment -SignInName <email_or_userprincipalname>

 

Also, make sure to populate email field with UPN(User Principal Name), so workspace admin can identify correct account to assign rights to SQL pools.

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.