Unable to create Login for an Azure AD user/group

[ghost]

Unable to create Login for an Azure AD user/group, because the password for mssql_login resource is required.
How do I skip the password parameter for creating a login for Azure AD user, and let the module know that it's for Azure AD user/group and not an SQL Auth Login.

[alxy]

External users, i.e. those that are created out of AAD objects, do not need a login. You can directly create a user. Please refer to the docs how it looks like for a managed identity, however, it will be very similar for a regular user or group. The password is not required on the mssql_user resource.

[ghost]

@alxy: Thanks for your quick response, I am looking at alternate way to execute this query via Terraform to create a login and use it within the mssql_user resource
CREATE LOGIN [DBA GROUP] FROM EXTERNAL PROVIDER WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

[alxy]

This is perfectly possible, you can use the mssql_user resource for that. What is the problem?
Note, that when you want to run this exactly in this form the principal who runs the terraform code also needs Directory Readers role on the AD tenant, as is stated in the docs:

Note that in order to create an external user referencing an Azure AD entity (user, application), the Azure SQL Server needs to be a member of an Azure AD group assigned the Azure AD role Directory Readers. If it is not possible to give the Azure SQL Server this role (through the group), you can use the object id of the Azure AD entity instead.

Your code would roughly translate like this:

resource "mssql_user" "example" {
  server {
    host = "example-sql-server.database.windows.net"
    login {
       username = "sa"
      password = "p4sswd"
    }
  }

  database  = "master"
  login_name  = "DBA GROUP" # this needs to be the name of the security group in your AAD tenant
  username = "dba-group" # this will be the SQL-username of the external user on your SQL server
}

[eriktim]

Thanks @alxy. Here's what I tried to create a user for an AD group.

This is the initial Terraform script I used:

data "azuread_group" "this" {
  display_name = "tf-group"
}

resource "mssql_user" "this" {
  server {
    host = azurerm_mssql_server.sql_server.fully_qualified_domain_name
    login {
      username = azurerm_mssql_server.sql_server.administrator_login
      password = azurerm_mssql_server.sql_server.administrator_login_password
    }
  }

  database  = azurerm_mssql_database.this.name
  username  =  data.azuread_group.this.display_name
  login_name  = data.azuread_group.this.display_name
  object_id = data.azuread_group.this.object_id

  roles = [
    "db_owner",
    "db_datawriter",
    "db_datareader",
  ]
}

However, using login_name gives me the following error:

Error: unable to create user [my-database].[tf-group]: mssql: Cannot add the principal 'tf-group', because it does not exist or you do not have permission.

When leaving login_name out, the user is created succesfully!
All good and users within that group actually seems to have the proper access.

Now I was also having a look at the database using SSMS and I noticed this principal is marked as being an EXTERNAL_USER, as opposed to groups that where added manually using CREATE USER [sql-group] FROM EXTERNAL PROVIDER; (I assume that query does the same as not using the object_id):

SELECT name, type, type_desc FROM sys.database_principals

name	type	type_desc
tf-group	E	EXTERNAL_USER
sql-group	X	EXTERNAL_GROUP

Not sure what the impact is of having E rather than X set, but is there a way to construct AD group users via this route as well?

[alxy]

Are you absolutely sure the SQL server has read access on your AAD tenant?

Note that in order to create an external user referencing an Azure AD entity (user, application), the Azure SQL Server needs to be a member of an Azure AD group assigned the Azure AD role Directory Readers. If it is not possible to give the Azure SQL Server this role (through the group), you can use the object id of the Azure AD entity instead.

You can check if that works by running a CREATE USER ... FROM EXTERNAL PROVIDER statement manually, and see if that works?

[eriktim]

When running

CREATE USER [my-group] FROM EXTERNAL PROVIDER

using SSMS it creates the user with type X, so that works.

When running Terraform without object_id I get that error again:

Error: unable to create user [my-database].[my-group]: mssql: Cannot add the principal 'my-group', because it does not exist or you do not have permission.

When running Terraform with object_id I creates the users using type E.

---

I do not know the whole codebase, but I noticed the type seems to be set here when using object_id.

[eriktim]

@alxy any thoughts on this?

[eriktim]

FYI, this in the end seemed to work:

resource "mssql_user" "this" {
  server {
    host = azurerm_mssql_server.sql_server.fully_qualified_domain_name
    azuread_default_chain_auth {} // login as AD user for adding EXTERNAL user/grp without object_id
  }

  database  = azurerm_mssql_database.this.name
  username  =  data.azuread_group.this.display_name

  roles = [
    "db_owner",
    "db_datawriter",
    "db_datareader",
  ]
}

[alxy]

@eriktim Nice to hear that you could solve it with this other auth mechanism. 👍

[amantur]

Not exactly the AAD group, but I'm trying to create a user for managed identity of an app service that I'm creating in same pipeline (Azure Devops). The external user is created and added to the specific roles as well but I'm not able to login from the app service. Here is the code:

resource "mssql_user" "app_services" {
  for_each = local.my_app_services

  username  = module.app_services[each.key].azurerm_app_service_output.name
  object_id = module.app_services[each.key].azurerm_app_service_output.identity[0].principal_id
  
  database  = ${local.sql_db.name}
  
  roles = ["db_datareader", "db_datawriter"]

  server {
    host = module.sqlServer.sql_server.fully_qualified_domain_name
    azuread_managed_identity_auth {
    }
    # login {
    #   username = local.sql_admin_user
    #   password = module.sqlServer.admin_password
    # }
  }

If I delete the user and create directly in database (CREATE USER [my-service-name] FROM EXTERNAL PROVIDER then I'm able to connect and login from the code.

Also, the service principal (user) running the pipeline is member of AAD group which is AD admin for the SQL Server.

Not sure what is going wrong there :(

[amantur]

how we got over this:

1. Create user managed identity (manually).
2. Give this user identity rights Directory.Users.Read.All
3. Add this identity to SQL Server by specifying Identity{ type = "UserAssigned"}
4. Run SQL statement to create users

I'll try to use mssql_user resource once I get a little breather!

[dankarmyy]

@amantur I ran into the same issue as you. The problem is that the provider documentation is incorrect. You cannot use principal_id of a system assigned identity for the object_id property:

object_id = module.app_services[each.key].azurerm_app_service_output.identity[0].principal_id

The value you need for a system assigned managed identity is from client_id (application id). Unfortunately, this isn't exported as a property from the resource. So you have to go about it a different way - using the azapi provider:

In my case, I'm granting the SMI of a web app the ability to read data from an Azure SQL database.

data "azapi_resource" "app-service-identity" {
  name      = "default"
  parent_id = azurerm_linux_web_app.example.id
  type      = "Microsoft.ManagedIdentity/identities@2018-11-30"

  response_export_values = ["properties.clientId"]
}

resource "mssql_user" "appservice-user" {
 ...

  database  = "mydb"
  username  = azurerm_linux_web_app.example.name
  object_id = jsondecode(data.azapi_resource.app-service-identity.output).properties.clientId

  roles     = ["db_datareader"]
...
}

This is only for SMI, if you want to grant a specific user or AD group access, the method stated in the documentation is correct.

Hope it helps

[amantur]

@amantur I ran into the same issue as you. The problem is that the provider documentation is incorrect. You cannot use principal_id of a system assigned identity for the object_id property:

object_id = module.app_services[each.key].azurerm_app_service_output.identity[0].principal_id

The value you need for a system assigned managed identity is from client_id (application id). Unfortunately, this isn't exported as a property from the resource. So you have to go about it a different way - using the azapi provider:

In my case, I'm granting the SMI of a web app the ability to read data from an Azure SQL database.

data "azapi_resource" "app-service-identity" {
  name      = "default"
  parent_id = azurerm_linux_web_app.example.id
  type      = "Microsoft.ManagedIdentity/identities@2018-11-30"

  response_export_values = ["properties.clientId"]
}

resource "mssql_user" "appservice-user" {
 ...

  database  = "mydb"
  username  = azurerm_linux_web_app.example.name
  object_id = jsondecode(data.azapi_resource.app-service-identity.output).properties.clientId

  roles     = ["db_datareader"]
...
}

This is only for SMI, if you want to grant a specific user or AD group access, the method stated in the documentation is correct.

Hope it helps

I'll give it a try, I'm currently using powershell as had to deliver the infra. I think it may work because now SQL Server identity has directory read permissions.

[jason-berk-k1x]

@eriktim

FYI, this in the end seemed to work:

resource "mssql_user" "this" {
  server {
    host = azurerm_mssql_server.sql_server.fully_qualified_domain_name
    azuread_default_chain_auth {} // login as AD user for adding EXTERNAL user/grp without object_id
  }

  database  = azurerm_mssql_database.this.name
  username  =  data.azuread_group.this.display_name

  roles = [
    "db_owner",
    "db_datawriter",
    "db_datareader",
  ]
}

did you literally use azuread_default_chain_auth {} or did you add values there.

I'm terribly confused..... :-(

using Terraform, I created the sql server like so

resource "azurerm_mssql_server" "sqlserver" {
  name                         = "my-sqlserver-${var.sub}"
  resource_group_name          = azurerm_resource_group.sqlserver.name
  location                     = local.location
  version                      = "12.0"
  administrator_login          = "localadmin"
  administrator_login_password = random_password.admin_password.result

  azuread_administrator {
    login_username              = data.azuread_group.sql_server_admin_group.display_name
    object_id                   = data.azuread_group.sql_server_admin_group.object_id
    tenant_id                   = data.azurerm_client_config.current.tenant_id
    azuread_authentication_only = false
  }

  identity {
    type = "SystemAssigned"
  }
}

then I create a database

resource "azurerm_mssql_database" "db" {
  name                        = "${var.sub}-db"
  server_id                   = azurerm_mssql_server.sqlserver.id
  .... fields omitted for readability ......
}

now I want to add my "developers" AAD group to the master database. My understanding is that doing so would allow any member of that AAD group to login to any database on the server (maybe this is incorrect???)

resource "mssql_user" "developers" {
  server {
    host = azurerm_mssql_server.sqlserver.fully_qualified_domain_name
    azuread_default_chain_auth {}
  }

  username = data.azuread_group.developers.display_name

  roles = [
    "db_owner",
    "db_datawriter",
    "db_datareader",
  ]
}

which fails with

module.shared_infra.mssql_user.developers: Creating...
╷
│ Error: unable to create user [master].[My Developers]: DefaultAzureCredential: failed to acquire a token.
│ Attempted credentials:
│ EnvironmentCredential: missing environment variable AZURE_TENANT_ID
│ WorkloadIdentityCredential: no client ID specified. Check pod configuration or set ClientID in the options
│ ManagedIdentityCredential: failed to authenticate a system assigned identity. The endpoint responded with {"error":"invalid_request","error_description":"Identity not found"}
│ AzureCLICredential: ERROR: Please run 'az login' to setup account.

which makes sense, but I'm confused on which credentials I need to provide. I've got the TF running as a service principal and I've got an AAD group ("sql_server_admin_group") but its just the group assigned as the AAD admin on the server.

[eriktim]

@jason-berk-k1x been a while and on to other things now, so just some comments

• yes I used the azuread_default_chain_auth {} like in the snippet
• I noticed you did not set the database, is that as intented?
• I was not using identity {}

[mikeviggiani]

I ran into a similar issue and wound up looking how to approach the user creation using an azure devops pipeline and stumbled upon this https://fgheysels.github.io/managed-identity-users-in-sql-via-devops/ and figured a similar approach needs to be used if using the SA vs Enrta Admin.

Looking at

terraform-provider-mssql/sql/user.go

Line 109 in a0cab2c

SET @stmt = 'CREATE USER ' + QuoteName(@username) + ' WITH SID=' + CONVERT(varchar(64), CAST(CAST(@objectId AS UNIQUEIDENTIFIER) AS VARBINARY(16)), 1) + ', TYPE=E'

SET @stmt = 'CREATE USER ' + QuoteName(@username) + ' WITH SID=' + CONVERT(varchar(64), CAST(CAST(@objectId AS UNIQUEIDENTIFIER) AS VARBINARY(16)), 1) + ', TYPE=E'

I assume this is responsible for setting the external group as a user, perhaps some other parameter to specify a user or group if an object ID is provided? if group set it to type X

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql?view=sql-server-ver16

[WolfyUK]

When upgrading to azure/azapi v2, you no longer need to jsondecode() when retrieving the clientId, i.e.:

data "azapi_resource" "app-service-identity" {
  name                   = "default"
  parent_id              = azurerm_linux_web_app.example.id
  type                   = "Microsoft.ManagedIdentity/identities@2018-11-30"
  response_export_values = ["properties.clientId"]
}

resource "mssql_user" "appservice-user" {
  ...
  database  = "mydb"
  username  = azurerm_linux_web_app.example.name
  object_id = data.azapi_resource.app-service-identity.output.properties.clientId
  roles     = ["db_datareader"]
  ...
}