I have been working on an Azure solution in which I want a Azure Function to use a System Managed Identity to access an Azure SQL DB, all managed using Terraform.

For this to work the managed identity needs to be granted SQl db_datareader & db_datawriter permissions. These SQL permissions are in addition to any Azure RABC permission configured, a point people often forget.

You can use Terraform to set any RBAC permission using the azurerm provider, but not SQL permissions. That needs a SQL connection with and Entra ID authentication as discussed below.

Adding the SQL Permission when logged in as a SQL Admin (how not to do it)

My Azure SQL instance, built by Terraform, enables both SQL and Entra ID administrator accounts.

If you connect the the SQL instance in SQL Management Studio (SSMS) with the SQL account you might think you could add the managed service user permissions using a command in the following form

CREATE USER [api-managed-identity] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [api-managed-identity];

But this failed with the error

Only connections established with Active Directory accounts can create other Active Directory users

Adding the SQL Permission when logged in as an Entra ID SQL Admin

So you need to connect in SSMS using the Entra ID SQL administrator account set by Terraform as shown below:

resource "azurerm_mssql_server" "sql_server" {
  name                          = var.sql_server_name
  resource_group_name           = azurerm_resource_group.rg.name
  location                      = var.location
  version                       = "12.0"
  administrator_login           = var.sql_administrator_login
  administrator_login_password  = var.sql_administrator_password
  minimum_tls_version           = "1.2"
  public_network_access_enabled = true

  azuread_administrator {
    login_username              = var.sql_entra_administrator_login
    object_id                   = var.sql_entra_administrator_object_id
    tenant_id                   = var.sql_entra_administrator_tenant_id
    azuread_authentication_only = false
  }
}

The tenant_id is the critical setting here. You don’t have to set it, assuming you have a single Entra ID tenant. However, it becomes critical when the Entra ID you wish to use is a guest in a number of Azure tenants, as the one I wished to use was.

Side Note - You can’t use an Entra External ID tenant

If the is an Entra External ID tenant instance involved in your solution, the replacement for Azure Active Directory B2C, don’t think this can be used for your Entra ID SQL administrator.

It is possible to set the Entra External ID user’s details as the Entra SQL admin via Terraform, they do not error. However, you can’t login using them in SSMS. You will get an error

Error AADSTS500208: The domain is not a valid login domain for the account type.

So, to get an Entra ID account set as the Entra ID SQL administrator, you have to make sure you have the correct object ID and tenant ID for the account in the subscription the SQL instance has been created in.

You might know these values, but if you don’t, login to the Azure Portal as the user and open the Cloud Shell CLI (or you could use the Azure CLI)

To find your accounts login_username, object_id and tenant_id run the commands

az ad signed-in-user show --query "{object_id:id, login_username:userPrincipalName}"
az account list --query "{tenant_id: [?isDefault].tenantId | [0]}"

You can now update the azuread_administrator block and re-run the Terraform Apply.

Once this completes you should be able to login to SSMS using the Microsoft Entra MFA authentication option as the Entra ID account.

Adding the Managed Identity User’s SQL permission

Now you are logged into SSMS as an Entra ID account who is a SQL administrator you can add the Managed Identity User’s permissions to your Azure SQL databases

To do this you need to know the Managed Identity’s Principle ID, and having their name can help to make things clearer. You can find these in Entra ID, or using the Cloud Shell CLI with the command

az resource list --query "[?identity.type=='SystemAssigned'].{Name:name, PrincipalId:identity.principalId, TenantId:identity.tenantId}" --output table

You can then add permissions in SSMS using a SQL query in the form

USE databasename
CREATE USER [any name, but name from Entra ID make it clearer] FROM EXTERNAL PROVIDER
WITH OBJECT_ID = 'principle id from query';

ALTER ROLE db_datareader ADD MEMBER [name from create user];
ALTER ROLE db_datawriter ADD MEMBER [name from create user];

This is one of those problems that needs you to follow the correct process. It all makes sense in the end, but it is easy to go down the incorrect path.

For the original version of this post see Richard Fennell's personal blog at Adding SQL Permissions for an Azure Managed Identity