Cannot create user because of conflicting collation

[sigurdhj]

I'm trying to create a mssql_user resource using Terraform 0.15.0:

resource "mssql_login" "this" {
  server {
    host = data.azurerm_mssql_server.this.fully_qualified_domain_name
    login {
      username = data.azurerm_mssql_server.this.administrator_login
      password = var.sql_master_password
    }
  }

  login_name = local.customer_fullname
  password   = random_password.sql_password.result
}

resource "mssql_user" "this" {
  server {
    host = data.azurerm_mssql_server.this.fully_qualified_domain_name
    login {
      username = data.azurerm_mssql_server.this.administrator_login
      password = var.sql_master_password
    }
  }

  database = azurerm_mssql_database.this.name
  username = local.customer_fullname
  login_name = mssql_login.this.login_name
  roles    = [ "db_owner" ]
}

Applying this resource results in the following error:

Error: unable to create user [customer-scaleout-7732].[customer-scaleout-7732]: mssql: Cannot resolve the collation conflict between "Danish_Norwegian_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The collations are:

select SERVERPROPERTY('collation') # Output: SQL_Latin1_General_CP1_CI_AS
select DATABASEPROPERTYEX('customer-scaleout-7732','collation') # Output: Danish_Norwegian_CI_AS

[anderius]

Exactly the same issue here: mssql: Cannot resolve the collation conflict between "Norwegian_100_CI_AS_SC_UTF8" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

[anderius]

@magne I guess setting the collation in the query would solve this, see https://stackoverflow.com/a/20545527.

This has to be done a couple of places in https://github.com/betr-io/terraform-provider-mssql/blob/master/sql/user.go.

I would suggest setting collate SQL_Latin1_General_CP1_CI_AS. This would be fairly safe, as I guess the only thing we are comparing are usernames, logins and identifiers.