3

I have created sql server and database with terraform now I have to create database user and assign role to it.

How I can do it. Please suggest me.

sumit salunke
  • 33
  • 1
  • 1
  • 3

4 Answers4

3

I've started working on a Terraform provider that will do this over at https://github.com/jayway/terraform-provider-mssql. It's still very early work, but I've managed to make it provision logins and users for those logins in a local SQL Server instance (that was already running, so I haven't tested it at all in conjunction with e.g. the AzureRM provider). Testing and contributions are very welcome (but don't use it for production scenarios just yet)!

Tomas Aschan
  • 156
  • 1
  • 1
  • 10
  • There is a bunch of such providers in the terraform registry, but either they take the server connection in the provider, which does not play well with creating the server itself from terraform, or they don't support the same authentication methods as the azurerm provider, in my case specifically asking azure cli for the token (the .net sql client can). – Jan Hudec Oct 26 '22 at 07:15
2

Creating users and roles is a function of SQL server, not of the Azure platform. As such Terraforms ARM provider does not deal with this, and as far as I can see Terraform does not have an MSSQL provider to this work (it does have MYSQL and Postgres). You would need to do this work using SQL scripts.

Sam Cogan
  • 38,736
  • 6
  • 78
  • 114
2

Here's how I did this using postgres/psql.

First, generate the md5 password hash by appending the username to the password:

$ echo -n mypasswordbob|md5sum
fd9c9714184c4ae189ca83f1c21aeeb8

Prepend the string 'md5' to the resulting hash to get the encrypted password form that Postgres will accept: md5fd9c9714184c4ae189ca83f1c21aeeb8

Then add in a standalone template file with the database commands you want to run:

data "template_file" "db_roles" {
  vars {
    username_bob = "bob"
    password_bob = "md5fd9c9714184c4ae189ca83f1c21aeeb8"
  }
  template = <<EOF

  create user $${username_bob};
  alter user $${username_bob} WITH ENCRYPTED PASSWORD '$${password_bob}';

  EOF
}

Finally, add a remote-exec provisioner as part of your server creation:

  provisioner "remote-exec" {
    inline = [
      "set -x",
      "cat > db_roles.sql <<EOL\n${data.template_file.db_roles.rendered}\nEOL",
      "psql -U myuser < db_roles.sql",
    ]
  }
Tom McCarty
  • 161
  • 3
0

Use the Terraform file provisioner to deploy the SQL, then use remote-exec provisioner to load it into SQL Server.

marenkay
  • 321
  • 1
  • 4