/ Insights / How to Secure an Azure Synapse Workspace with Microsoft Entra ID Groups Insights How to Secure an Azure Synapse Workspace with Microsoft Entra ID Groups May 27, 2021 Concurrency Editor’s note: Azure Active Directory (Azure AD) was officially renamed Microsoft Entra ID by Microsoft in 2023. This post was written before the rebrand and still refers to “Azure Active Directory” and “Azure AD groups” throughout — these terms are functionally identical to Microsoft Entra ID and Entra ID groups today. What is Azure Synapse Analytics? 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. Securing data platforms is one part of a bigger hybrid work security picture. Watch our recording, Cloud & AI Endpoints: Scaling Secure Hybrid Work, for a wider look at endpoint and cloud security. Prerequisites Before you begin, make sure you have the following in place: An Azure Synapse workspace already created. Global Administrator or Privileged Role Administrator access in Microsoft Entra ID. Synapse Administrator or SQL Active Directory Admin role within the Synapse workspace. Confirming these upfront will save you from hitting permission errors halfway through the setup. Step 1: Review Default Users in Your Synapse SQL Pool 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. Step 2: Create a SQL User Linked to an Entra ID Group 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. Step 3: Create the Group in Microsoft Entra ID 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. Want to go deeper on identity management? Watch our recording, Latest Innovations in Microsoft Security: Strengthening Identity Management with Entra, for the latest on securing identities with Microsoft Entra. Step 4: Verify Access in the Synapse Workspace 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. Get Help with Azure Synapse Security Securing a Synapse workspace is one piece of a larger identity and access strategy — our Data Governance & Protection and Secure Endpoints & Access services help you extend these same principles across your entire Azure environment. If you’re ready to talk through your setup, contact us and we’ll help you build a plan.
