cannot create user (Incorrect syntax near 'PASSWORD')

[mikemowgli]

Hello,

I cannot create a user using this code. I'm using the latest provider (0.2.0) with Terraform 0.15

resource "mssql_user" "tamers_synapse_aas_read_only" {
      authentication_type = (known after apply)
      database            = "datatamerssynapseu"
      default_schema      = "dbo"
      id                  = (known after apply)
      password            = "Thisisa8!securepassword"
      principal_id        = (known after apply)
      roles               = [
          "db_datareader",
        ]
      username            = "analysisservices"

      server {
          host = "datatamerssynapseu.database.windows.net"
          port = "30001"

          login {
              password = (sensitive value)
              username = "tamers"
            }
        }
    }

Here is the error I get with TF_LOG_PROVIDER=debug

2021-04-20T10:10:33.566Z [DEBUG] provider: starting plugin: path=.terraform/providers/registry.terraform.io/betr-io/mssql/0.2.0/linux_amd64/terraform-provider-mssql_v0.2.0 args=[.terraform/providers/registry.terraform.io/betr-io/mssql/0.2.0/linux_amd64/terraform-provider-mssql_v0.2.0]
2021-04-20T10:10:33.566Z [DEBUG] provider: plugin started: path=.terraform/providers/registry.terraform.io/betr-io/mssql/0.2.0/linux_amd64/terraform-provider-mssql_v0.2.0 pid=21844
2021-04-20T10:10:33.566Z [DEBUG] provider: waiting for RPC address: path=.terraform/providers/registry.terraform.io/betr-io/mssql/0.2.0/linux_amd64/terraform-provider-mssql_v0.2.0
2021-04-20T10:10:33.581Z [INFO]  provider.terraform-provider-mssql_v0.2.0: configuring server automatic mTLS: timestamp=2021-04-20T10:10:33.581Z
2021-04-20T10:10:33.619Z [DEBUG] provider.terraform-provider-mssql_v0.2.0: plugin address: network=unix address=/tmp/plugin231303290 timestamp=2021-04-20T10:10:33.618Z
module.synapse.mssql_user.tamers_synapse_aas_read_only: Creating...
│ Error: unable to create user [datatamerssynapseu].[analysisservices]: mssql: Parse error at line: 1, column: 37: Incorrect syntax near 'PASSWORD'.
│   on ../../../modules/synapse/main.tf line 182, in resource "mssql_user" "tamers_synapse_aas_read_only":
│  182: resource "mssql_user" "tamers_synapse_aas_read_only" {

FYI:

• the SQL Database is of type datawarehouse (perf level: Gen2: DW100c)
• I checked it is not paused
• I didn't create a login user
• the 'tamers' user you see in the login section is an admin
• I checked that terraform can connect to the DB on the port 30001

[mikemowgli]

could it be related to a charset problem?

[anderius]

Do you have custom collation on the database? And does the error also happen with the above password (or something similar)? I guess you did include a dummy password in the snippet above. :-)

Maybe you can debug it with this sql (taken from the provider source code):

DECLARE @stmt nvarchar(max)
SET @stmt = 'CREATE USER ' + QuoteName('analysisservices') + ' WITH PASSWORD = ' + QuoteName('Thisisa8!securepassword', '''') + ', DEFAULT_SCHEMA = ' + QuoteName('dbo')
EXEC (@stmt)

[magne]

Could you try this with version 0.2.1. This version should remove some issues due to charset/collation problems.

[magne]

Closed since no response.