Adding Azure AD Users to SQL via DevOps Pipeline

Azure SQL with Azure AD authentication is the way forward - no passwords to rotate, better auditing, integration with Conditional Access. But automating user provisioning from a pipeline requires specific configuration.

The Goal

Automatically add Azure AD users or groups to Azure SQL when they're created:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [[email protected]]

Prerequisites

1. Azure AD Admin set on SQL Server - A user or group must be set as Azure AD admin
2. Service Principal - Your pipeline needs a service principal
3. SQL permissions - The service principal needs permission to create users
4. Entra ID Directory Readers role - The service connection running the pipeline must have the Directory Readers role in Entra ID (not Global Reader - just the basic reader role is enough). This allows SQL Server to resolve user/group names when executing CREATE USER FROM EXTERNAL PROVIDER

Setting the Azure AD Admin

In Terraform:

resource "azurerm_mssql_server" "this" {
  name                         = "sql-production"
  resource_group_name          = azurerm_resource_group.this.name
  location                     = azurerm_resource_group.this.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password = random_password.sql.result
}

resource "azurerm_mssql_server_extended_auditing_policy" "this" {
  server_id              = azurerm_mssql_server.this.id
  log_monitoring_enabled = true
}

# Set Azure AD admin
resource "azurerm_mssql_server_microsoft_entra_administrator" "this" {
  server_id              = azurerm_mssql_server.this.id
  login_username         = "SQL-Admins"
  object_id              = data.azuread_group.sql_admins.object_id
  tenant_id              = data.azurerm_client_config.current.tenant_id
  azuread_authentication_only = false
}

Service Principal as SQL Admin

For pipeline automation, make the service principal a member of the SQL admin group, or set it directly as admin.

Then grant it permission in the database:

-- Connect as Azure AD admin first
CREATE USER [your-service-principal-name] FROM EXTERNAL PROVIDER
ALTER ROLE db_owner ADD MEMBER [your-service-principal-name]

Pipeline Configuration

trigger: none

pool:
  vmImage: 'ubuntu-latest'

parameters:
- name: userEmail
  type: string
- name: role
  type: string
  default: 'db_datareader'
  values:
  - db_datareader
  - db_datawriter
  - db_owner

steps:
- task: AzureCLI@2
  displayName: 'Add Azure AD User to SQL'
  inputs:
    azureSubscription: 'your-service-connection'
    scriptType: 'bash'
    scriptLocation: 'inlineScript'
    inlineScript: |
      # Get access token for SQL
      TOKEN=$(az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv)

      # SQL command
      SQL_CMD="
        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '${{ parameters.userEmail }}')
        BEGIN
          CREATE USER [${{ parameters.userEmail }}] FROM EXTERNAL PROVIDER
        END
        ALTER ROLE ${{ parameters.role }} ADD MEMBER [${{ parameters.userEmail }}]
      "

      # Execute using sqlcmd with AAD token
      sqlcmd -S sql-production.database.windows.net \
        -d YourDatabase \
        -G \
        -P "$TOKEN" \
        -Q "$SQL_CMD"

Alternative: Using PowerShell

If sqlcmd isn't available, use PowerShell:

$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=sql-production.database.windows.net;Database=YourDatabase;"
$connection.AccessToken = $token
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = @"
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$userEmail')
BEGIN
  CREATE USER [$userEmail] FROM EXTERNAL PROVIDER
END
ALTER ROLE db_datareader ADD MEMBER [$userEmail]
"@

$command.ExecuteNonQuery()
$connection.Close()

Handling Groups

For Azure AD groups (recommended over individual users):

CREATE USER [App-Database-Readers] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [App-Database-Readers]

In your pipeline, use object ID for groups with special characters:

$groupId = "00000000-0000-0000-0000-000000000000"  # Azure AD Object ID
$command.CommandText = "CREATE USER [$groupId] FROM EXTERNAL PROVIDER WITH TYPE = X"

Common Errors

"Principal 'xxx' could not be found"

• The user/group doesn't exist in Azure AD
• Wrong tenant configuration

"The server principal is not able to access the database"

• Service principal isn't in the database yet
• Connect as Azure AD admin to add it first

"User already exists"

• Check with SELECT * FROM sys.database_principals WHERE name LIKE '%user%'

Need help with Azure SQL security and automation? Get in touch - we help organisations implement secure database practices.