Version: betr-io/mssql v0.2.3

I've written a module that creates a login and a user. I set the count to 2 in order. The second user fails with the output below.

module.sql_readwrite_user[1].mssql_login.user: Creation complete after 1s [id=sqlserver://foo.database.windows.net:1433/svc_calorders_readwrite_2]
module.sql_readwrite_user[0].mssql_login.user: Creation complete after 1s [id=sqlserver://foo.database.windows.net:1433/svc_calorders_readwrite_1]
module.sql_readwrite_user[1].mssql_user.user: Creating...
module.sql_readwrite_user[0].mssql_user.user: Creating...
module.sql_readwrite_user[1].mssql_user.user: Creation complete after 1s [id=sqlserver://foo.database.windows.net:1433/MYDB/svc_calorders_readwrite_2]
╷
│ Error: unable to create user [MYDB].[svc_calorders_readwrite_1]: mssql: There is already an object named 'String_Split' in the database.
│ 
│   with module.sql_readwrite_user[0].mssql_user.user,
│   on sql_login/main.tf line 24, in resource "mssql_user" "user":
│   24: resource "mssql_user" "user" {

This seems to be related to #17. It appears to be a race condition where the 2nd concurrent user resource tries to create the split function but then fails as it now exists.

Should the function check/creation be in transaction, or the "exists" test should be repeated inside the create statement?

I did an import: terraform import 'module.sql_db.mssql_user.msi[\"dev-group\"]' 'mssql://mysqlserver.database.windows.net/mydb/dev-group' and it imported the following roles: "db_ddladmin", "db_datareader", "db_datawriter", in that order.

Now, whenever I do terraform apply, it wants to modify the resource, because the following is my resource block:

resource "mssql_user" "msi" {
  for_each = var.managed_identity_groups

  server {
    host = data.azurerm_mssql_server.main.fully_qualified_domain_name
    login {
      username = data.azurerm_mssql_server.main.administrator_login
      password = data.azurerm_key_vault_secret.sqladminsecret.value
    }
  }

  database  = azurerm_mssql_database.db1.name
  username  = each.key
  object_id = each.value
  roles = [
    "db_datareader",
    "db_datawriter",
    "db_ddladmin"
  ]
}

Just because the roles array is in a different order. Even if I tell it to go ahead and apply, the next time I try, it still wants to update the resource.

Hello!

Currently, it is not possible to grant role to user in Synapse dedicated database. Because, 'ALTER ROLE' is not working in such
databases. Only

EXEC sp_addrolemember 'db_owner', 'User';

azure docs

Is there is any chance to provide some parameter in "mssql_user" resource, so when you are using Synapse dedicated pool database, it would be possible to switch to "EXEC" in part of Role assignment?

Code snippet:

Error:

Cheers,
Oleh.

Hello,

I was reading documentation and I am trying to fulfill scenario:

Active directory group assign that group db_owner or reader. When I am reading documentation it requires for me to enter client_secret in order to create account in db. Is this possible and please provide example since on official terraform web site examples are not correct

We have Entra ID-authentication only enabled for our MSSQL Server.

As such, our Logins (outside of the MSSQL Server default logins) are all Entra ID principals.

To add them with pure SQL, we would use something like

CREATE LOGIN [$login] FROM EXTERNAL PROVIDER
ALTER SERVER ROLE [$role] ADD MEMBER [$login]

I would like to use this provider to help manage our server logins in addition to our database users but I'm unable to piece together how EXTERNAL PROVIDER logins with no passwords and specific roles would work with this provider. Is this an unsupported use case?

Hello! I'm trying to do a secret-less connection between the app service and Azure SQL by using mssql_user
below the code:

resource "mssql_user" "web" {
server {
host = var.host_name
azure_login {
tenant_id = var.tenant_id
client_id = var.sql_sp_client_id (used client id of the application that is the ad admin of the SQl server)
client_secret = data.azurerm_key_vault_secret.AAD_admin_password.value
}
}

object_id = var.webapp_principal_id (used oblect_id of the webapp service principal )
database = var.database_name
username = var.webapp_name
roles = ["db_datareader", "db_datawriter"]
}

data "azurerm_key_vault_secret" "AAD_admin_password" {
name = "terraform-iac-secret"
key_vault_id = local.key_vault_id
}

As an admin, I set an application by using
azuread_administrator {
login_username = var.sql_server_ad_admin_username
object_id = var.sql_server_ad_admin_object_id
}

but in the end it throughs me this error: Error: unable to create user [tenant-DB].[wapp_name]: mssql: login error: Login failed for user ''.

Please, could you provide me with some feedback, thanks.

It would be great to provide DB host/username/password via the Provider configuration since that information does not change if you're creating multiple users.

The module mssql_user can be used to add a Managed Identity into a database table, almost the same way you would add an AAD Service Principal to it.

However, it is confusing because when you want to use the object_id property, you have to pass the value of the client_id of the Managed Identity. BUT, the Managed Identity also have an object_id property.

resource "mssql_user" "this" {
  server {
    host = var.mssql_server.fqdn
    azure_login {
    }
  }

  database  = azurerm_mssql_database.database.name
  username  = azurerm_user_assigned_identity.managed_identity.name
  object_id = azurerm_user_assigned_identity.managed_identity.client_id  ###This is the confusing part

  roles     = ["db_datareader"]

  timeouts {
    default = "2m"
  }
}

I think adding the Managed Identity use case to the documentation and the repository examples would help.

Thanks!

As described here - now possible to add external users/groups in server-logins:
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-logins-tutorial?view=azuresql

Dear Author,

I have used terraform to create a database user with specified roles.
The user was created on the given database, but the roles defined in the roles array were not created
What I did:

`resource "mssql_user" "my_user" {
server {
host = >hostname>
login {
username =
password =
}
}

database = azurerm_mssql_database.sqldb.name
default_schema = "dbo"

login_name = #it's important, because I don't want to add AD user. I guess a property missing here
username =
roles = [ "db_datareader", "db_datawriter" ]

depends_on = [
azurerm_mssql_database.sqldb
]
}`

Would be great if it supported a list of permissions as well, for example:

resource "mssql_user" "example" {
  ...
  roles       = [ "db_owner" ]
  permissions = [ "UNMASK", "ALTER ANY MASK" ]
}

Hello,

I was wondering if it was possible to ad an Managed Identity using this terraform provider?

This is the SQL script I would use to add the user

CREATE USER [your application name] FROM EXTERNAL PROVIDER;
ALTER ROLE [role name] ADD MEMBER [your application name];

I'm trying to create an AAD user (CREATE USER FROM EXTERNAL PROVIDER) for a SystemAssigned managed identity, in my case an Azure Stream Analytics jobs. I'm using the following code to do that:

resource "mssql_user" "asa_user_managed_identity" {
  server {
    host = "${var.sql_server_name}.database.windows.net"
    azure_login {
        tenant_id = "xxx"
        client_id = "xxx"
        client_secret = "xxx"
    }
  }
  database   = "sqldb-xxx-ppm-c"
  username   = "asa-xxx-ppm-c"
  roles      = ["db_datareader", "db_datawriter"]
}

The principal that runs this bit of Terraform code is setup as the AAD SQL Server admin on Azure portal. The result of running this is:

mssql_user.asa_user_managed_identity: Creating...
╷
│ Error: unable to create user [sqldb-xxx-ppm-c].[asa-xxx-ppm-c]: mssql: The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'.
│
│   with mssql_user.asa_user_managed_identity,
│   on main.tf line 6, in resource "mssql_user" "asa_user_managed_identity":
│    6: resource "mssql_user" "asa_user_managed_identity" {
│
╵

It looks like the AAD administrator cannot read from the sql_logins view, however, there is also no need to read from there as external users do not have logins attached to them.

Hi,

I've been going around in circles for a while now, trying to manage AD users/groups in Azure SQL databases with this provider in an Azure DevOps pipeline. Here are some excerpts from my terraform project:

resource "azurerm_mssql_server" "main" {
  ...

  azuread_administrator {
    login_username = "MyAppRegistration"
    object_id      = "<The App Reg's Object/App ID>" # As it is set when I manually set this principal as admin in the Azure Portal
    tenant_id      = data.azurerm_client_config.current.tenant_id
  }
}

resource "mssql_user" "ad_user" {
  server {
    host = azurerm_mssql_server.main.fully_qualified_domain_name
    azure_login {
      tenant_id     = data.azurerm_client_config.current.tenant_id
      client_id     = "<The same App Reg's Object/App ID>"
      client_secret = "" # leaving this empty, to pick up the MSSQL_CLIENT_SECRET environment variable
    }
  }
  database  = "some_database"
  username  = "AD_GROUP_NAME"
  object_id = "<The AD group's object ID as found in the Azure Portal>"
  roles     = ["db_owner"]
  timeouts {
    default = "90s"
  }
}

In my azure-pipelines.yml, I'm executing this task to set the MSSQL_CLIENT_SECRET environment variable:

- task: AzureCLI@2
  displayName: 'Azure: Prepare Terraform'
  inputs:
    azureSubscription: '${{parameters.ServiceConnection}}'
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
      Write-Host "##vso[task.setvariable variable=MSSQL_CLIENT_SECRET]$($env:servicePrincipalKey)"
    addSpnToEnvironment: true

I'm using this very same $env:servicePrincipalKey to authenticate to the Azure resource manager provider, so I "know" it's valid.

Executing this definition ultimately ends in:

2022-08-31T14:58:58.455+0200 [ERROR] vertex "mssql_user.ad_user" error: unable to create user [some_database].[AD_GROUP_NAME]: db connection failed after 1m30s timeout

│ Error: unable to create user [some_database].[AD_GROUP_NAME]: db connection failed after 1m30s timeout
│
│ with mssql_user.ad_user,
│ on azure_sql.tf line ..., in resource "mssql_user" "ad_user":
│ ...: resource "mssql_user" "ad_user" {
│

When I set the TF_LOG environment variable to INFO, I can see 90 seconds worth of log messages that say: "provider.terraform-provider-mssql_v0.2.5.exe: ... failed to connect to database: parameter 'secret' cannot be empty: timestamp=..."

I've tried looking around in the source code to see if I can find where this might be going wrong but I just don't understand Go well enough to make real sense of it. As far as I can tell, the client secret is meant to be exchanged for an OAuth token. Does anybody have any thought on what I'm doing wrong, or if this is a bug of some sorts?

I uninstalled the 32-bit of the Azure CLI and installed the 64-bit. Kept having issues with the provider.

So, I did $env:TF_LOG_PROVIDER = "INFO" and kept seeing messages about how az was not recognized as an internal command. I could not for the life of me understand why.

Finally reinstalled the 32-bit version and it worked successfully.

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?

Unable to add UserAssignedIdentity on database as external user and grant db permission on mssql managed database

resource "mssql_user" "adduser" {
  server {
    host = hostname
    login {
      username = "xxxx"
      password = xxxxxx
    }
  }
  database  = "database"
  username  = dbuser
  object_id = dbpass
  roles               = ["db_datawriter", "db_datareader"]
}

UserAssigned Managed Identity has been added on MS SQL however we're unable to create and grant db access on data bases thru terraform

Error: │ Error: unable to create user [database].[hostname]: mssql: Incorrect syntax near 'type'.

We have been blocked from past 1 month, really appreciate any help on the issue.

When I ran SQL cmds on SSMS it worked but we would like to implement thru Terraform

CREATE USER [<managed-identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<managed-identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<managed-identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<managed-identity-name>];
GO

I am running into issues creating a mssql_user. I had no issues with creating the mssql_login.

Resource

variable "roles" {
  default = ["db_datawriter", "db_datareader"]
}

variable "databases" {
  default = ["master"]
}

resource "mssql_user" "user" {
  server {
    host = var.database_host
    port = var.database_port
    login {
      username = var.database_admin_username
      password = var.database_admin_password
    }
  }
  for_each = toset(var.databases)
  username = mssql_login.login.login_name
  roles    = coalesce(var.roles, [])
  database = each.key
}

Plan output

  # module.sqlserver_role.mssql_user.user["master"] will be created
  + resource "mssql_user" "user" {
      + authentication_type = (known after apply)
      + database            = "master"
      + default_schema      = "dbo"
      + id                  = (known after apply)
      + principal_id        = (known after apply)
      + roles               = [
          + "db_datawriter",
          + "db_datareader",
        ]
      + username            = "testuser"

      + server {
          + host = "REDACTED.us-east-1.rds.amazonaws.com"
          + port = "1433"

          + login {
              + password = (sensitive value)
              + username = "rdsuser"
            }
        }
    }

Error:

2021/04/25 22:53:36 [DEBUG] module.sqlserver_role.mssql_user.user["master"]: apply errored, but we're indicating that via the Error pointer rather than returning it: unable to create user [master].[testuser]: mssql: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
2021/04/25 22:53:36 [ERROR] eval: *terraform.EvalApplyPost, err: unable to create user [master].[testuser]: mssql: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
2021/04/25 22:53:36 [ERROR] eval: *terraform.EvalSequence, err: unable to create user [master].[testuser]: mssql: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Change the defaultTimeout to be variable that can be set in the provider:

Why?
If you use azuread_default_chain_auth sometimes you receive a timeout if you depend on Azure CLI credential.

Example usage:

provider "mssql" {
  debug = "false"
  defaultTimeout = 60
}

I believe this could be updated here:
https://github.com/betr-io/terraform-provider-mssql/blob/master/sql/user.go#L113

When the default_schema is not set, we have found that identities (such as managed identities that are members of Azure Entra ID groups) run into issues when running trying to run database migrations that involve setting up schemas, tables, etc.

Typically an error is thrown similar to the following:

Error occurred during DB Migration: "The specified schema name \"<Object ID>@<Directory/Tenant ID>\" either does not exist or you do not have permission to use it."

This gets fixed by running:

ALTER USER [<Azure Entra ID Group Name>] WITH DEFAULT_SCHEMA=[dbo]

Which might be accounted for by specifying the default_schema in the CreateUser function.

We are trying to create a user un a Synapse Dedicated Pool, with a similar script that we use for Azue-SQL-Databases.

Here is the script, the script works fine when used on a SQL-Database

resource "mssql_user" "auser_on_syndp" {
  provider = betrio-mssql
  
  server {
    host     = module.syn_server.full_name
    login {
      username = var.das_fix["sql_sa"]
      password = module.kv_syn_server_admin_password.secret_value
    }
  }
  database   = module.syndp_database.name
  login_name = module.create_syn_login.alogin.login_name
  username   = module.create_syn_login.alogin.login_name

  #roles    = [ "db_owner" ]
}

But here is the error

│ Error: unable to create user [syndpeu2pocedw9].[adf-eu2-poc-60-122-01]: mssql: Parse error at line: 1, column: 108: Incorrect syntax near 'role_cur'.
│ 
│   with module.provision_datafactories[0].module.adf_create["60-122-01"].module.adf_kv_secrets_syndp_cs["CS_1"].module.create_syn_user.mssql_user.auser_on_syndp,
│   on _modules/das/syn/syn_create_user/main.tf line 110, in resource "mssql_user" "auser_on_syndp":
│  110: resource "mssql_user" "auser_on_syndp" {

Any suggestion or in case this is due to the version, what version would solve it ?

Hi all and thank you for really great provider.

I have issue with Azure DevOps pipeline when using mssql_user.
During pipeline execution Azure DevOps has public IP, so we need to add it to MS SQL firewall to plan / apply mssql_user
Is it possible to add inside of mssql_user task adding and removing current public IP firewall rule?

Or maybe you can suggest a workaround?

Thank you

I'm currently using version 0.2.4 of the provider and I encounter issues creating multiple users for the same database.
The error message is the following :
Error: unable to create user USER: mssql: There is already an object named 'String_Split' in the database

Looking at the previous issues and PRs, I saw that race condition can occurs if trying to create multiple users & login on the same database and it was supposedly fixed with #24.

Rolling back to 0.2.3 solved the problem

Hello,
When creating an infra from scratch (sql server, sql database, sql login), I have the following error when I try : terraform plan
" unable to read login [XXXXX]: db connection failed after 30s timeout
I think it's beacause the sql database doesn't exist yet.
So I tried adding : depend_on [sql database]
But same error.
Is there a way to not trying to read the login ?

Thanks

Whenver the administrator password changes, re-running terraform results in this error:

│ Error: unable to read user [abc]: mssql: login error: Login failed for user 'my_sa_account'.│ 
│   on ...... , in resource "mssql_user" "user":
│   22: resource "mssql_user" "user" {

This is actually a bit obvious when you look at the provider design - the login to the server is provided to the resource, not the provider itself. So if the SA login changes, the resource is unable to "update its state". It's storing the login in the terraform state file.

Ideally this should be taking the host/login from the provider registration, not this.

resource "mssql_user" "user" {
  server {
    host = var.endpoint
    login {
      username = var.administrator_login
      password = var.administrator_password
    }
  }

azure_login {
tenant_id = var.base_info.tenant_id
client_id = data.azurerm_key_vault_secret.sqladdusersclientid.value
client_secret = data.azurerm_key_vault_secret.sqladduserstoken.value
}

i'm grabbing the client id and secret from kv. this the id of the service principal that has AD read rights this service principal is in the group that is MS Entra Admin on the server

my error is
mssql: login error: Login failed for user ''

I just discovered this provider after creating my own (https://github.com/dsb-norge/terraform-provider-mssql), and now I am considering using yours instead! :-) But I would need a license.

This issue is for creating a LICENSE file with the projects license.

When trying to create a mssql_user resource using only azure client secrets provided by envvars, i get the error

on of login,azure_login must be specified

resource "mssql_user" "user" {
  server {
    host = "test.com"
  }

  username = "test"
  roles         = ["db_owner"]
}

expected:

read values from envvars, so i dont need to pass them explicitly into the module.

Hello, it would be great if it would support creating a login with SID

CREATE LOGIN TestLogin
WITH PASSWORD = 'SuperSecret52&&', SID = 0x241C11948AEEB749B0D22646DB1A19F2;

It would be great if the provider configuration allowed for a configurable timeout for connecting to the DB.
Currently using Azure SQL serverless which goes to sleep after 1 hour...it usually takes it 45-60 seconds to wake up and I get:

Error: unable to read user [xxx].[xxx]: db connection failed after 30s timeout with mssql_user.xxx, on xxx.database.tf line 54, in resource "mssql_user" "xxx": 54: resource "mssql_user" "xxx" {

Hello,

I cannot create users in an azure database using this code.
I'm using provider 0.2.1 with Terraform 0.13

resource "mssql_user" "user_all_read" {
      + authentication_type = (known after apply)
      + database            = "master"
      + default_schema      = "dbo"
      + id                  = (known after apply)
      + principal_id        = (known after apply)
      + roles               = [
          + "db_datareader",
        ]
      + username            = "app-all-read"

      + server {
          + host = "/subscriptions/1111-111-11-1/resourceGroups/reg-grp-01/providers/Microsoft.Sql/servers/sql-sl01"
          + port = "1433"

          + azure_login {
              + client_id     = "***"
              + client_secret = (sensitive value)
              + tenant_id     = "***"
            }
        }
    }

And this is the error:

mssql_user.user_all_read: Creating...

Error: unable to create user [master].[app-all-read]: parse "sqlserver://%2Fsubscriptions%2F21111-111-11-1%2FresourceGroups%2Freg-grp-01%2Fproviders%2FMicrosoft.Sql%2Fservers%2Fsql-sl01:1433": invalid URL escape "%2F"

I think it's not related, but my database collation is SQL_Latin1_General_CP1_CI_AS

Hello,

My name is Héctor.

I have recently had to solve an issue with creating users with the resource configuration mssql_user.

Specifically, I have had to add the parameter object_id to this resource configuration while there is no place in the documentation that indicates this parameter is optional; only there is this sentence that indicates the posibility of using object id (not object_id as a parameter).
"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 Dictionary Reader. 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."

I have also encountered another issue that I am not sure if it is related to the previous one. The parameter object_id should be defined, supposedly, inside the login block but I get an error when I define it like that in a module and I have had to comment this parameter for the module to run.

Thank you.

Regards.

I was wondering what the reasoning is for logging into the SQL Server via the "server" block within the resource specs? This is the first time that I have seen a terraform provider do it like this and was wondering if there is a specific reason for this.

To be clear what I mean, instead of passing the authentication information via the resource itself, I would have expected to instead initialise the provider with it, thereby establishing a session that is used by all resources thereafter. I.e., something that looks like this:

terraform {
  required_providers {
    mssql = {
      source  = "betr-io/mssql"
      version = "0.2.5"
    }
  }
}

provider "mssql" {
  host = "example-sql-server.database.windows.net"
  azure_login {
    tenant_id     = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
    client_id     = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
    client_secret = "terriblySecretSecret"
  }
}

resource "mssql_login" "example" {
  login_name = "testlogin"
  password = "verysecret"
}

resource "mssql_login" "example2" {
  login_name = "testlogin2"
  password = "veryverysecret"
}

When you look at the missing terraform resource roles for MS SQL now that logins & users are fixed by this much-needed provider, the only thing left is the ability to call SQL scripts with inline or from a template file

it would be lovely of this feature could be added to the provider

example psudo code

resource "mssql_code" myscript" {
  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
    }
  }
      execute_as_username = azurerm_mssql_server.sql_server.administrator_login
      execute_as_password = azurerm_mssql_server.sql_server.administrator_login_password
     
      # database is optional (???) in case the script needs to be applied against a specific file
      database = azurerm_mssql_database.db.name
     
      # script takes input from file, templatefile or expanded multiline HEREDOC format
      script = file("${path.module}/myscript.sql)

}

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.

I've been trying to add my Azure Data Factory managed identity user with this provider.

The terraform execution was successful but I've not been able to authenticate from within ADF. So I've tried to run the SQL commands "manually" with SQL Admin account and I've stumbled across this error:

Principal 'adf-cbi-dev-02' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

Since I'm using the same user in Terraform I was wondering 2 things:

1. Why does this provider do not fail with the same error?
2. Is my ADF managed identity correctly assigned these roles?

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.

Hi

I have recently discovered that creating a custom user in the following way:

resource "mssql_user" "database_access" {
   server {
    host = data.azurerm_mssql_server.mssql_server.fully_qualified_domain_name

    azure_login {
      tenant_id     = var.tenant_id
      client_id     = var.db_client_id
      client_secret = var.db_client_secret
    }
  }

  database       = module.caf_mssql_database.name
  username       = "username"
  password       = "PASSWORD"
  roles          = ["db_owner"]
  default_schema = "dbo"
}

works fine, but the password is not treated as a sensitive field by the provider and the plan shows the raw password when outputted in the terminal or pipeline.

This "feature" makes it almost impossible to use the provider in a setup where you want to generate a password, store it in a vault (e.g. key vault) to ensure that it is kept secret.

I hope you could change this soon in a forthcoming version, I could also provide you with a PR if that would speed things up.

Kind Regards
Jakub

In our Terraform codebase, we’re trying to create a vulnerability assessment baseline resource (see Terraform doc):

resource "azurerm_mssql_database_vulnerability_assessment_rule_baseline" "vulnerability-assessment-baseline-va2130" {
  server_vulnerability_assessment_id = "<subscriptionid>"
  database_name                      = "<databasename>"
  rule_id                            = "VA2130"
  baseline_name                      = "default"
  baseline_result {
    result = [
      "<username>","<SID>"
    ]
  }
}

We can get the username attribute as an output variable from the ‘mssql_user’ resource (i.e. mssql_user.databaseuser.username) and supply it to the vulnerability assessment, but not the SID.

│ Error: Unsupported attribute
│ 
│   on ProjectLogin.tf line 142, in resource "azurerm_mssql_database_vulnerability_assessment_rule_baseline" "vulnerability assessment-baseline-va2130":
│  142:       mssql_user.databaseuser.username,mssql_user.databaseuser.sid
│ 
│ This object has no argument, nested block, or exported attribute named "sid". Did you mean "id"?

Azure cli authentication, az login, is becoming a 'universal' way to log into azure.

While developing pipelines to run terraform, a state file was accidentally saved with empty client_id and client_secret in azure_login.

When running "terraform plan" the refresh would result in "db connection failed after 30s timeout"

It might be helpful to others if this scenario resulted in another error message.

version 0.2.4

We have created some SQL users using the mssql_user resource, and then changed SQL Server's password.
The mssql_user now fails to check the existing user, because it fails to authenticate. Apparently it doesn't use the new password that is passed to it in Terraform's configuration, and instead uses the old password that's already in state.

Sample config:

resource "mssql_user" "existing-sql" {
  server {
    host = "somehost
    login {
      username = "someuser"
      password = "somepw"
    }
  }
  database = "somedb"
  username ="dbuser"
  password = "dbuserpw"
}

I also confirmed this is provider's bug by changing the password manually in the state's JSON file. After that, the terraform plan can authenticate without issues.

Expected behaviour

mssql_user resoruce should always use server's login username and password from the configuration rather than the state

Hi,
Sharing the script. Please let me know any findings. MSSQL version = 13.0.1601.5

terraform {
required_providers {
mssql = {
source = "betr-io/mssql"
version = "0.1.0"
}
}
}
provider "mssql" {
debug = "true"
}

resource "mssql_login" "example" {
server {
host = "xxx.xxx.xxx.xxx"
login {
username = "mssql_admin"
password = "Mynewpassword"
}
}
login_name = "test"
password = "Hello"
}

resource "mssql_user" "example" {
server {
host = "xxx.xx.xxx.xx"
login {
username = "mssql_admin"
password = "Mynewpassword"
}
}
username = "test"
roles = ["db_owner"]
database = "platform-test"
login_name = mssql_login.example.login_name
}

Error

< Error: unable to read user [platform-test].[user]: mssql: 'STRING_AGG' is not a recognized built-in function name.>
Note: Able to create in database but getting the above error. Please give your inputs.

Thanks and Regards,
Ishan Yadav

Good morning,
This provider is working like a charm, I am able to create users over our MS SQL database servers. After the creation, running terraform plan is triggering the following error:
Error: unable to read user [db_name].[user_name]: db connection failed after 30s timeout
│ with module.xxx.mssql_user.xxx,
│ on ../../../modules/xxx/xxx/1-common.tf line 75, in resource "mssql_user" "xxx":
│ 75: resource "mssql_user" "xxx" {
Which is perfectly expected because the database server lies over a firewall. In fact, this is going to work fine if I'm adding a firewall rule exception.
I was wondering, is there a way to avoid the user to be read after the creation? I tried to add the lifecycle {ignore_changes = all} directive but it's not enough to avoid terraform trying to read from the database.
Let me know if you need further specifications. Thanks in advance for your help!
Fede

Hello,

I am trying to import a mssql_login resource to my terraform state with the following command:
terraform import mssql_login.example 'mssql://example-sql-server.database.windows.net/testlogin'

But I have the following error:
Error: neither login nor azure login specified
It seems that the error is thrown by the betr-io/terraform-provider-mssql provider.

Anyone can import this type of resource ?

When trying to destroy a user that owns a fulltext catalog I get the following error message:

Error: unable to delete user [<name>].[<name>]: mssql: The database principal owns a fulltext catalog in the database, and cannot be dropped.

I'm working around this issue with the following SQL:

DECLARE @username VARCHAR(MAX) = '<username>'
DECLARE @userPrincipalId int = 
(
    SELECT [principal_id]
    FROM sys.database_principals
    WHERE [name] = @username
)

DECLARE @crsr CURSOR
SET @crsr = CURSOR FOR SELECT [name] from sys.fulltext_catalogs WHERE principal_id = @userPrincipalId
DECLARE @catalogName VARCHAR(MAX)

OPEN @crsr
FETCH NEXT FROM @crsr INTO @catalogName
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('ALTER AUTHORIZATION ON Fulltext Catalog::[' + @catalogName + '] TO [dbo]')
    FETCH NEXT FROM @crsr INTO @catalogName
END

Hi,

It looks like the provider ignore proxy environment variables (Terraform support it natively)
When configuring an http_proxy, only my sql resources doesn't use the proxy

Best regards

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

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

Hi.

I'm using the resource "mssql_user" as it should give me the possibility to grant SQL permission for one of my security groups in Azure and this way allowing them to access the SQL database using Managed Identity.

The goal in general is to have as few passwords/secrets as possible throughout the entire project. However I can't really figure out if this is supported by your terraform provider.

What I would assume is that it could use my Default Azure Credentials (The ones that I'm using for az login) and then perform the given actions with my personal account.

This is what I'm doing exactly:

resource "mssql_user" "portal" {

  server {
    host = data.azurerm_mssql_server.global.fully_qualified_domain_name

    azuread_default_chain_auth {
      # Should just use DefaultAzureCredential to authorize. So if the current user has access to the SQL server, this should work
    }
  }
  object_id = azuread_group.sql_application_contributors.object_id 
  database  = data.azurerm_mssql_database.global.name
  username  = "OneConnect SQL Contributor"
  roles     = ["db_datareader", "db_datawriter"]
}

But whenever I run it I get the following error:

╷
│ Error: unable to create user [sqldb-moc-global-weu-dev-7jky].[OneConnect SQL Contributor]: db connection failed after 30s timeout
│
│   with mssql_user.portal,
│   on sql_user.tf line 1, in resource "mssql_user" "portal":
│    1: resource "mssql_user" "portal" {
│
╵

Am I doing something wrong?

Thanks in advance