MSSQL Database Plugin - Root Rotation

Vault
1

Hi Everyone,

I deployed a secret engine database with the mssql plugin with Terraform, and everything works fine except that since there are no username fields for this plugin, the vault cannot rotate the password for initial configuration.

I do not know if I’m the one misunderstanding how it works, but basically here are the steps to reproduce :

resource "vault_mount" "db" {
  path = "database"
  type = "database"
}

resource "vault_database_secret_backend_connection" "mssql" {
  backend       = vault_mount.db.path
  name          = "dbname"
  allowed_roles = ["role1"]
  
  mssql {
    connection_url = "sqlserver://username:password@dbname:1433"
  }
}

# We rotate the root password
resource "vault_generic_endpoint" "rotate_initial_db_password" {
  depends_on           = [vault_database_secret_backend_connection.mssql]
  path                 = "database/rotate-root/${vault_database_secret_backend_connection.mssql.name}"
  disable_read   = true
  disable_delete = true

  data_json = "{}"
}

and here is the error I get if I launch :

vault write -force database/rotate-root/dbname

Error:

unable to rotate root credentials: no username in configuration

Am I missing something ?

Thanks :slight_smile:

2

I don’t believe you can hardcode your username/password into the connection string as that’s treated as a secret. They need to be placeholders {…} and then with the write command you provide the values to those parameters.

For example (postgres):

vault write database/config/postgresql \
     plugin_name=postgresql-database-plugin \
     connection_url="postgresql://{{username}}:{{password}}@localhost:5432/postgres?sslmode=disable" \
     allowed_roles=readonly \
     username="root" \
     password="rootpassword"

``
3

Hi @aram

I am doing everything using Terraform, not Vault CLI.
If I try to add username / password into the terraform database backend resource, it says that it’s an unexpected variable. :confused:

4

Get it to work with the simplest tool first to make sure it works. After that automate it.

5

As I expected it’s working fine using VAULT CLI because you can specify the username and password.

But in terraform unless I hardcode the username / password (which makes the root rotation non-functioning) I cannot use the templated connection URL.

I think this might just be something missing in the terraform provider. I’ll open a github issue.

6

Ok actually after looking for random stuff on the github of the provider I found some working example

For anyone looking for the solution :

You need to add a “Data” map like so :

resource "vault_database_secret_backend_connection" "mssql" {
  backend       = vault_mount.db.path
  name          = "dbname"
  allowed_roles = ["role1"]
  
  mssql {
    connection_url = "sqlserver://{{username}}:{{password}}@dbname:1433"
  }

data = {
    username = "root"
    password="strongpassword"
    }
}