Implementing Role-Based Access Control for Azure SQL with AD

Question

Pulumi · Accepted Answer

Implementing Role-Based Access Control (RBAC) for Azure SQL with Azure Active Directory (AD) involves a few different resources and steps. Here's an outline of what we'll do to set this up using Pulumi in Python:

1. Create a new Azure Active Directory Service Principal. This is a unique identity that can be used for applications or services that need to access or modify resources.
2. Set up Azure AD-only authentication for your Azure SQL Server. This ensures that only Azure AD credentials are used for authentication.
3. Grant the appropriate roles to your Azure AD identities for accessing the SQL server. This step is where you define your access control policies.
4. Create an Azure SQL Database, which will be protected by the RBAC policies you've set up.
5. To demonstrate RBAC, we'll also create an Azure AD Directory Role Member, if you have specific roles to associate with your service principal. (For simplicity, we might skip this in the code example unless it's a crucial part of your request).

Next, we'll go through each of these steps in a Pulumi program. We'll start with a complete Pulumi stack in Python that will set up a simple RBAC-protected Azure SQL Database using identities from Azure AD.

Please make sure you have the Azure provider set up in Pulumi before running this code by following instructions at [Pulumi's Azure Setup Guide](https://www.pulumi.com/docs/intro/cloud-providers/azure/setup/).

```python
import pulumi
import pulumi_azure_native as azure_native
import pulumi_azuread as azuread

# Create a new Azure Active Directory Service Principal
service_principal = azuread.ServicePrincipal("sqlServicePrincipal", application_id="your-application-id")

# Create a Resource Group if not already existing
resource_group = azure_native.resources.ResourceGroup("sqlResourceGroup")

# Create an Azure SQL Server
sql_server = azure_native.sql.Server("sqlServer",
    resource_group_name=resource_group.name,
    location=resource_group.location,
    administrator_login="sqladminuser",
    administrator_login_password="CHANGE_to_a_Strong_Password123!",
    version="12.0"  # or the version you wish to use
)

# Enable Azure AD-only authentication for the SQL Server
server_ad_auth = azure_native.sql.ServerAzureADOnlyAuthentication("sqlServerAzureADOnlyAuthentication",
    server_name=sql_server.name,
    resource_group_name=resource_group.name,
    azure_ad_only_authentication=True
)

# Create an Azure SQL Database
sql_database = azure_native.sql.Database("sqlDatabase",
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    location=resource_group.location,
    sku=azure_native.sql.SkuArgs(name="S0")
)

# Output the Azure SQL Server ID which will be required for setting up roles and permissions
pulumi.export("sqlServerId", sql_server.id)

# As an example, we are showing how to assign a directory role member.
# Normally, you would have to assign SQL roles on the database to the service principal or to the users/groups in AD
# Directory Role assignment (only necessary if you have distinct directory roles to assign)
# Commented out as it's not always necessary for basic RBAC setup with SQL and AD
# directory_role_member = azuread.DirectoryRoleMember("roleMember",
#    role_object_id="the-role-object-id",
#    member_object_id=service_principal.object_id
#)

# Note: In a production scenario, you would likely need to create or grant roles within the SQL server to specific identities.
# That process would involve custom scripting or interactions with the Azure SQL Database after creation because
# Pulumi currently does not directly support creating these role assignments.
```

Here's what each part of the above code does:

- **Service Principal**: This is the identity we create for whatever application or service will interact with the SQL server.
- **Resource Group**: It's a container that holds related resources for an Azure solution.
- **SQL Server**: This is the managed service instance of SQL in Azure.
- **Server AD Authentication**: This resource is used to configure the Azure SQL Server to only use Azure AD for authentication.
- **SQL Database**: This is the actual database where your data will be stored.

The `pulumi.export` statement at the end is used to output the ID of the SQL Server. In a real-world scenario, you'd use this ID to configure access policies and roles within Azure using additional tools or manual setup in the Azure Portal, as direct role assignments in Azure SQL via Pulumi may require custom scripts.

Remember that setting up a secure RBAC environment requires carefully planning your access policies. The above example will get the resources in place but configuring the fine details of RBAC will require additional steps not covered here.