Synchronize Azure Active Directory Identities for SQL Access Control

Question

Pulumi · Accepted Answer

To synchronize Azure Active Directory (AAD) identities for SQL Access Control, you'll need to integrate Azure AD with your Azure SQL server. This allows you to manage database permissions using the centralized Azure AD identities. In Azure, you can set a server to use Azure AD for authentication. On a specific Azure SQL server, you can also designate an Azure AD user to be an Azure AD administrator.

Pulumi allows you to define this infrastructure as code using Python. The following program sets up an Azure SQL server with Azure AD authentication, creates a database on this server, and sets an Azure AD user as the SQL server's administrator.

The resources we use from Pulumi's Azure Native package are:

Server: This is your Azure SQL Server instance.
ServerAzureADAdministrator: This resource sets an Azure AD user as the administrator of the SQL server.
ServerAzureADOnlyAuthentication: This enables Azure AD authentication on the SQL server, which means that only Azure AD identities can be used for authentication to the server.

Let's look at how this can be implemented in Python using Pulumi:

import pulumi
import pulumi_azure_native as azure_native

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

# Create an Azure SQL Server instance
sql_server = azure_native.sql.Server("sqlserver",
    resource_group_name=resource_group.name,
    location=resource_group.location,
    administrator_login="pulumiadmin",
    administrator_login_password="Pulumi@Passw0rd#2023", # Please replace with a secure password.
    version="12.0", # Or desired SQL version
)

# Set the Azure SQL Server to use Azure AD Only Authentication
aad_auth = azure_native.sql.ServerAzureADOnlyAuthentication("aadAuth",
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    azure_ad_only_authentication=True,
)

# Set an Azure AD user as the Azure SQL Server administrator
aad_admin = azure_native.sql.ServerAzureADAdministrator("sqlserveraadadmin",
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    login="pulumiuser@example.com", # Azure AD user's email or username
    sid="<OBJECT_ID_OF_THE_AZURE_AD_USER>", # Object ID of the Azure AD user
    tenant_id="<TENANT_ID>", # Tenant ID of your Azure subscription
    administrator_type="ActiveDirectory", # Use 'ActiveDirectory' to set an AAD user as the admin
)

# Creating a SQL Database on the Azure SQL Server
sql_database = azure_native.sql.Database("sqldatabase",
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    sku=azure_native.sql.SkuArgs(
        name="S0",
    ),
)

pulumi.export('server_name', sql_server.name)
pulumi.export('db_name', sql_database.name)

This program declares resources in the following order:

A resource group to contain our resources.
An Azure SQL Server within the resource group.
Activation of Azure AD only authentication on the server.
The designation of an Azure AD user as the server's administrator.
An Azure SQL database hosted on the server.

Please replace placeholder values such as <OBJECT_ID_OF_THE_AZURE_AD_USER> and <TENANT_ID> with the actual IDs from your Azure AD tenant.

The Pulumi program above will manage the deployment of these resources in Azure. Once deployed, your Azure SQL Server will be accessible only by Azure AD users, and you will have an Azure AD admin user that controls access to the SQL database. Remember to keep passwords and other sensitive data secure and manage them appropriately, for example, by using secrets management systems.