How to Secure a Synapse Workspace with Active Directory groups 

Azure Synapse is an enterprise analytics service that accelerates time to insight across data warehouses and big data systems. Azure Synapse brings together the best of SQL technologies used in enterprise data warehousing, Spark technologies used for big data, Pipelines for data integration and ETL/ELT, and deep integration with other Azure services such as Power BI, Cosmos DB, and Azure ML.  

But how do you create a Synapse workspace? The first important thing to note is that when you create a Synapse workspace, you want to ensure that the right users have access to the right data.  I will talk more about governance in future blog posts. This blog discusses how to obtain a Synapse SQL Pool setup and give users access to it. 

In the example below we will create a simple active directory group and create a user in a Synapse workspace associated with that active directory group. Users of that group with have read and write data to the Synapse SQL Pool 

After creating the workspace, you will notice there are a few users created by default. Notice the service principal that is used to create the workspace has access. In our case this is dev-cus-info. This is important to note because this is the first user that is given access to the database by default. 

In addition, the user that created the instance will have access through the service via the admin setting in the portal. 

If you click on the SQL Active directory admin, you can change who the admin is and this will change who has the right to the underlying database. 

In order to create a user on the database that is associated with an active directory group, we need to use SQL to create the user and another set of scripts to create roles associated with that user. 

The first set of scripts creates a user called “info-svcs-syn-users” and applies the user to the schema “dbo” which we saw in the image earlier. 

Create a new user who is part of AD user. 

create user [info-svcs-syn-users] from external provider 

GO 

GRANT CONNECT TO [info-svcs-syn-users] 

The second step we need to do is execute the script below which will create two roles on the database, giving users access to read and write data. 

EXEC sp_addrolemember ’db_datareader’, ’dev-cust-info-svcs-syn-userss’; 

EXEC sp_addrolemember ’db_datawriter’, ’dev-cust-info-svcs-syn-userss’; 

After we create the group within Synapse, we need to create that group within Azure Active Directory. 

In order to access Azure Directory, search the portal and select Azure Active Directory: 

Click on “Create New Group”. 

Click on the group to open the membership blade. 

Notice how there are no users in the group.  Click on the members icon to get the following screen. 

Select a user to add to the group. 

Notice now there is one user in the group. 

Now that we have access, let’s go back to our workspace and select a query. 

Azure Synapse helps speed up the process to get the accurate and optimized data you and your organization need from data warehouses and more. Creating a Synapse workspace will give you access to such data.  As we shown synapse in combination with active directory can be used to secure users by role based on groups that are defined.