Enabling Single Sign-On for Azure SQL with Azure AD

Question

Pulumi · Accepted Answer

To enable Single Sign-On (SSO) for Azure SQL Database using Azure Active Directory (Azure AD), we need to undertake a few steps. These involve creating and configuring Azure AD resources, a SQL server, and setting up the server to use Azure AD only authentication.

Here's an overview of what we will be doing:

Create an Azure AD Service Principal: A service principal is required to grant applications access to Azure resources. This will be used to authenticate applications to the database.
Set up Azure SQL Server: We will create a new Azure SQL Server and configure it to use Azure AD for authentication.
Enable Azure AD-only Authentication: We will explicitly enable Azure AD-only authentication on the SQL Server for added security.
Create a Managed Instance if necessary: Managed instances in Azure SQL can also be used with Azure AD integration, in case you need to use them for your use case.

Below is a Python program written using Pulumi's infrastructure-as-code (IaC) approach for enabling SSO with Azure AD for Azure SQL. Note that the Azure AD app registration and consent granting (if required) are typically done outside of Pulumi. Here we are assuming the Azure AD application has already been registered.

import pulumi
from pulumi_azure_native import resources, sql
import pulumi_azuread as azuread

# Assume you already have a Resource Group created, or create a new one.
# Replace `existing-rg-name` with the name of your resource group.
resource_group = resources.ResourceGroup.get(name="existing-rg-name")

# Create an Azure AD Service Principal for our SQL Server
service_principal = azuread.ServicePrincipal(
    "servicePrincipal",
    application_id="<APPLICATION_ID_FROM_AZURE_AD>", # Application (client) ID from Azure AD app registration
)

# Create a SQL Server and set the Azure AD admin to the service principal created above.
sql_server = sql.Server(
    "sqlServer",
    resource_group_name=resource_group.name,
    administrators=sql.ServerExternalAdministratorArgs(
        login=service_principal.application_id,
        principal_type="Application",
        sid=service_principal.id,
        tenant_id=pulumi.config.require("azure:tenantId"), # Tenant ID associated with your Azure AD
    ),
    location="East US",
    server_name="myserver",
    version="12.0"
)

# Enable Azure AD-only Authentication on the SQL Server
ad_only_auth = sql.ServerAzureADOnlyAuthentication(
    "adOnlyAuthentication",
    authentication_name="Default",
    resource_group_name=resource_group.name,
    server_name=sql_server.name,
    azure_ad_only_authentication=True
)

# If you're using Managed Instances, you can set them up similarly:
# managed_instance = sql.ManagedInstance(
#     "managedInstance",
#     ... # Other necessary properties
# )

# Export the Azure SQL Server name and Azure AD-only authentication status
pulumi.export("sql_server_name", sql_server.name)
pulumi.export("azure_ad_only_authentication_enabled", ad_only_auth.azure_ad_only_authentication)

In this program, we create an Azure AD service principal (ServicePrincipal), which is essential for enabling Azure AD integration with the Azure SQL server. You need to replace <APPLICATION_ID_FROM_AZURE_AD> with the actual application ID of the Azure AD application that you have registered.

Next, we create a new SQL Server (sql.Server) with the administrator set to the Azure AD service principal. The administrators property of the Server resource is used to set the Azure AD admin for the SQL server.

Then, we enable Azure AD-only Authentication on the server to ensure that only Azure AD authentication is allowed for this server. This is done using the sql.ServerAzureADOnlyAuthentication resource with the azure_ad_only_authentication property set to True.

Finally, we export the SQL Server name and the status of Azure AD-only authentication as stack outputs.

Before running this code, make sure you have the Azure CLI installed and configured with the right permissions to create these resources. You also need to have Pulumi installed and configured.

Replace the placeholder values (e.g., <APPLICATION_ID_FROM_AZURE_AD>, existing-rg-name, myserver, etc.) with actual values relevant to your Azure environment.