Creating an AAD user for SystemAssigned managed identity

[alxy]

I'm trying to create an AAD user (CREATE USER FROM EXTERNAL PROVIDER) for a SystemAssigned managed identity, in my case an Azure Stream Analytics jobs. I'm using the following code to do that:

resource "mssql_user" "asa_user_managed_identity" {
  server {
    host = "${var.sql_server_name}.database.windows.net"
    azure_login {
        tenant_id = "xxx"
        client_id = "xxx"
        client_secret = "xxx"
    }
  }
  database   = "sqldb-xxx-ppm-c"
  username   = "asa-xxx-ppm-c"
  roles      = ["db_datareader", "db_datawriter"]
}

The principal that runs this bit of Terraform code is setup as the AAD SQL Server admin on Azure portal. The result of running this is:

mssql_user.asa_user_managed_identity: Creating...
╷
│ Error: unable to create user [sqldb-xxx-ppm-c].[asa-xxx-ppm-c]: mssql: The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'.
│
│   with mssql_user.asa_user_managed_identity,
│   on main.tf line 6, in resource "mssql_user" "asa_user_managed_identity":
│    6: resource "mssql_user" "asa_user_managed_identity" {
│
╵

It looks like the AAD administrator cannot read from the sql_logins view, however, there is also no need to read from there as external users do not have logins attached to them.

[nazirakz]

@alxy Hello! I have exactly the same configuration! How was the issue resolved? cause #33 without an answer.

[alxy]

For us it was working after this change.

Check the following:

• The service principal used for the login to the server is also the AAD admin of the sql server (not sure if that is required, but we did it like this)
• The service principal has read permissions (Directory reader) on the AD tenant. Otherwise it cannot create external users.
• Use latest version of the provider.

Also note that it takes a while (~1min) for system assigned MIs to be available in the AAD tenant. So a first run might fail, if you create the sql user immediately after you spawn the Azure resource.