Login as AAD User

[BGajda13]

Hello!

My organization uses free Azure AD, so we don't want to but the P1 level. Problem is that when using free Azure AD users from an external provider can be added only via AD User. It's not possible via Service Principal (P1 level needed and role Directory Readers) or via SQL Account (Only from AAD users it can be). So currently there is a need to manually login into Azure SQL Database and add a user via SQL command (CREATE USER .. FROM EXTERNAL PROVIDER). So we want to automatize that, but the only way to do that is to log in as an AD User. In the current implementation of the provider, I haven't seen such an option to do that. Maybe I'm missing some option?

[bittrance]

I ran into the same limitation. Since it is likely that you are using the azurerm provider together with terraform-provider-mssql, I think it would make sense for terraform-provider-mssql to support AAD auth as provider-level configuration the same way azurerm does it, at least the CLI method and the MSI method. I would be willing to author a PR. Ping @magne ?

[UoB-McKenzie]

Hi, Im also looking into solving this, cannot find anything that works.

[magne]

If you submit a PR, @bittrance, I will certainly consider it. For this kind of added functionality, I would also hope for tests 😄

[bittrance]

I hacked togehter a PoC using the new fedauth option. This means that we can add a third login method (with no arguments) apart from login {...} and azure_login {...}. The question is what name it should have? Some alternatives are azuread_default_credentials {}, fedauth {} or azure_federated_login {}? Alternatively, we could make a special interpretation of azure_login {} (i.e. without arguments). It will look something like this:

resource "mssql_user" "external" {
  server {
    host = azurerm_mssql_server.sql_server.fully_qualified_domain_name
    azuread_default_credentials {}
  }
  database = azurerm_mssql_database.db.name
  username = "[email protected]"
}

@magne Do you have a preference?

[magne]

Just pick what feels natural. I hope this comes with tests and docs if it makes it to a PR 😄