Manage Azure SQL Database Security with Active Directory

Question

Pulumi · Accepted Answer

To manage Azure SQL Database security with Active Directory, you will typically need to configure Azure AD authentication, which allows you to manage database permissions using Azure AD accounts or groups. With this authentication method, you can use Azure AD identities to connect to your SQL Database or Azure Synapse analytics (formerly SQL Data Warehouse).

Here's how to achieve this using Pulumi in a Python program:

1. **Create an Azure AD Application**: To authenticate to Azure SQL using Azure AD, you first need an Azure AD application.
2. **Create a SQL Server and Database**: You will need to create an Azure SQL Server and a database within it.
3. **Configure an Azure Active Directory Administrator for the SQL Server**: You must assign an Azure AD principal as an Active Directory admin for your Azure SQL Server.
4. **Adjust Database Permissions**: You will typically assign specific permissions to the Azure AD identity on the SQL Database through T-SQL statements.

Below is a Pulumi program in Python that performs these steps.

### Pulumi Program in Python

```python
import pulumi
import pulumi_azure_native as azure_native

# Define the resource group
resource_group = azure_native.resources.ResourceGroup('example-resources')

# Create an Azure AD application that will act as a service principal for the SQL Server
ad_application = azure_native.azuread.Application(
    'example-ad-app',
    display_name='ExampleAzureADApplication'
)

# Create a service principal for the above application
ad_service_principal = azure_native.azuread.ServicePrincipal(
    'example-ad-sp',
    application_id=ad_application.application_id
)

# Create an Azure SQL Server that uses the service principal for Azure AD admin
sql_server = azure_native.sql.Server(
    'example-sql-server',
    resource_group_name=resource_group.name,
    location=resource_group.location,
    administrators=azure_native.sql.ServerExternalAdministratorArgs(
        principal_type="ActiveDirectory",
        login=ad_application.display_name,
        sid=ad_service_principal.object_id,
        tenant_id=ad_service_principal.app_owner_tenant_id
    )
)

# Create an Azure SQL Database
sql_database = azure_native.sql.Database(
    'example-sql-database',
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    location=resource_group.location
)

# Configure Active Directory admin for the SQL server
sql_ad_admin = azure_native.sql.ServerAzureADAdministrator(
    'example-sql-ad-admin',
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    administrator_type="ActiveDirectory",
    login=ad_application.display_name,
    sid=ad_service_principal.object_id,
    tenant_id=ad_service_principal.app_owner_tenant_id
)

# Export the pertinent information
pulumi.export('sql_server_name', sql_server.name)
pulumi.export('sql_database_name', sql_database.name)
pulumi.export('ad_application_display_name', ad_application.display_name)
```

In this program, we follow these steps:

- We import the required Pulumi Azure Native modules.
- We create a resource group using the `ResourceGroup` class.
- An Azure AD application is created using the `Application` class, which will act as a service principal.
- We then create a service principal associated with the AD application using the `ServicePrincipal` class.
- The Azure SQL Server is instantiated with the `Server` class, and the AD application's display name and service principal ID (`sid`) are used to set the Azure AD admin for the SQL Server.
- We create an Azure SQL Database within the SQL Server using the `Database` class.
- Finally, we export the SQL Server and database names, along with the display name of the AD application, as outputs from our Pulumi program using the `pulumi.export` function.

This sets up the SQL Server and the Database to be controlled via the Azure AD application. The actual permissions and roles within the SQL Database will need to be configured using T-SQL statements based on your particular requirements, which can be executed from within Azure's management tools like Azure Portal or Azure CLI.