search

betr-io / terraform-provider-mssql

Terraform provider for Microsoft SQL Server
https://registry.terraform.io/providers/betr-io/mssql/latest
MIT License
35 stars 28 forks source link

Random failures when creating a user soon after the database has been created #28

Open Fresa opened 2 years ago

Fresa commented 2 years ago

I'm trying to create contained database users right after a database has been created using the azurerm_mssql_database resourrce. However the mssql_user create request fails for various reasons: Error: unable to read user [my-database-name].[my-user]: login error: mssql: Login failed for user 'admin'. Another error: Error: unable to create user [my-database-name].[my-user]: SQL Server had internal error

When retrying a moment later by planning and applying only the failed user terraform succeeds.

There seems to be some sort of timing issue where the database has been created but is not ready to receive any commands yet. I've seen this problem before when provisioning a local containerized instance of SQL Server and trying to connect and execute scripts right after using sqlcmd. A delay in-between seems to sort it out, but it's of course a hacky, brittle solution.

Nopesound commented 2 years ago

Does anyone have a solution? this problem is driving me crazy.

Nopesound commented 2 years ago

I'm trying to create contained database users right after a database has been created using the azurerm_mssql_database resourrce. However the mssql_user create request fails for various reasons: Error: unable to read user [my-database-name].[my-user]: login error: mssql: Login failed for user 'admin'. Another error: Error: unable to create user [my-database-name].[my-user]: SQL Server had internal error

When retrying a moment later by planning and applying only the failed user terraform succeeds.

There seems to be some sort of timing issue where the database has been created but is not ready to receive any commands yet. I've seen this problem before when provisioning a local containerized instance of SQL Server and trying to connect and execute scripts right after using sqlcmd. A delay in-between seems to sort it out, but it's of course a hacky, brittle solution.

How did you manage to create a delay? Knowing this would help me until I find a solution. I tried with the create_duration parameter but it doesn't seem to be compatible.

Fresa commented 2 years ago 
resource "time_sleep" "wait_for_sqldb" {
  depends_on = [azurerm_mssql_database.sqldb]

  create_duration = "30s"
}

Then you add depends_on = [time_sleep.wait_for_sqldb] to your user resources.

https://registry.terraform.io/providers/hashicorp/time/latest/docs/resources/sleep