1. Answers
  2. Configuring Role Assignments for Access to Azure Synapse Dedicated SQL Pools

How do I configure role assignments for access to Azure Synapse Dedicated SQL Pools?

In this guide, we will configure role assignments to provide access to Azure Synapse Dedicated SQL Pools. This setup is essential for managing permissions and ensuring that only authorized users or services can access the SQL pools. We will use Pulumi, an Infrastructure as Code tool, to automate this process.

We will create an Azure Synapse workspace, a dedicated SQL pool within that workspace, and then assign a role to a user or service principal to grant them access to the SQL pool.

import * as pulumi from "@pulumi/pulumi";
import * as azureNative from "@pulumi/azure-native";

// Define the resource group
const resourceGroup = new azureNative.resources.ResourceGroup("resourceGroup", {
    location: "East US",
});

// Define the Synapse workspace
const synapseWorkspace = new azureNative.synapse.Workspace("synapseWorkspace", {
    resourceGroupName: resourceGroup.name,
    location: resourceGroup.location,
    defaultDataLakeStorage: {
        accountUrl: "https://<your-storage-account-name>.dfs.core.windows.net",
        filesystem: "<your-filesystem-name>",
    },
    sqlAdministratorLogin: "sqlAdminUser",
    sqlAdministratorLoginPassword: "sqlAdminPassword123!",
    managedResourceGroupName: resourceGroup.name.apply(name => `${name}-managed`),
});

// Define the Synapse SQL pool
const sqlPool = new azureNative.synapse.SqlPool("sqlPool", {
    resourceGroupName: resourceGroup.name,
    workspaceName: synapseWorkspace.name,
    location: synapseWorkspace.location,
    sku: {
        name: "DW100c",
    },
});

// Define the role assignment
const roleAssignment = new azureNative.authorization.RoleAssignment("roleAssignment", {
    principalId: "<user-or-service-principal-id>",
    roleDefinitionId: pulumi.interpolate`/subscriptions/${azureNative.authorization.getClientConfig().then(config => config.subscriptionId)}/providers/Microsoft.Authorization/roleDefinitions/<role-definition-id>`,
    scope: pulumi.interpolate`/subscriptions/${azureNative.authorization.getClientConfig().then(config => config.subscriptionId)}/resourceGroups/${resourceGroup.name}/providers/Microsoft.Synapse/workspaces/${synapseWorkspace.name}/sqlPools/${sqlPool.name}`,
});

Key Points

  • We created a resource group to contain our resources.
  • We defined an Azure Synapse workspace with necessary configurations.
  • We created a dedicated SQL pool within the Synapse workspace.
  • We assigned a role to a user or service principal to grant access to the SQL pool.

Summary

In this guide, we successfully configured role assignments for access to Azure Synapse Dedicated SQL Pools using Pulumi. This setup ensures that the specified user or service principal has the necessary permissions to interact with the SQL pools, facilitating secure and manageable access control.

Deploy this code

Want to deploy this code? Sign up for a free Pulumi account to deploy in a few clicks.

Sign up

New to Pulumi?

Want to deploy this code? Sign up with Pulumi to deploy in a few clicks.

Sign up