Vault's Dynamic Secrets for Microsoft SQL Server/Database?

Vault
, ,
1

How can I setup and use Vault’s dynamic secrets for for Microsoft Azure SQL Server/Database? I read about PostgreSQL-based Dynamic Secrets: Database Secrets Engine and Using HashiCorp Vault C# Client with .NET Core documentation. However, I am still uncertain on how to use Vault’s dynamic secrets for Microsoft SQL Server/Database.

  • Which role/user that contains dynamic secrets that we should use for the Microsoft Azure SQL Server/Database connections string?
  • Is the connection string to the Microsoft Azure SQL Server/Database immediately fails when the credentials expired?
  • How does one use PostgreSQL-based Database Root Credential Rotation for Microsoft Azure SQL Server/Database, preferably using Terraform?

In addition, I would like to setup these dynamic secrets for Microsoft SQL Server/Database using Terraform instead of setting it up using below steps.

vault write projects-api/database/config/projects-database \
	 	plugin_name=mssql-database-plugin \
	 	connection_url='sqlserver://{{username}}:{{password}}@db:1433' \
	 	allowed_roles="projects-api-role" \
	 	username="sa" \
	 	password="Testing!123"

vault write projects-api/database/roles/projects-api-role \
    db_name=projects-database \
    creation_statements="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';\
				USE HashiCorp;\
				CREATE USER [{{name}}] FOR LOGIN [{{name}}];\
        GRANT SELECT,UPDATE,INSERT,DELETE TO [{{name}}];" \
    default_ttl="2m" \
    max_ttl="5m"

vault policy write projects-api ./projects-role-policy.hcl

vault write auth/approle/role/projects-api-role \
	  role_id="projects-api-role" \
		token_policies="projects-api" \
		token_ttl=1h \
		token_max_ttl=2h \
		secret_id_num_uses=5

Please correct me if I am wrong with the below suggestions.

  • Substitute vault write projects-api/database/config/projects-database command with vault_database_secret_backend_role Terraform resource?
resource "vault_database_secret_backend_role" "backend_role" {
  # ...
  creation_statements = [
    "CREATE USER [{{name}}] WITH PASSWORD = '{{password}}';",
    "ALTER ROLE db_owner ADD MEMBER [{{name}}];"
  ]
  revocation_statements = ["DROP USER IF EXISTS [{{name}}]"]
  default_ttl           = "120" # 2 minutes
  max_ttl               = "240" # 4 minutes
}
  • Substitute vault write projects-api/database/roles/projects-api-role command with vault_approle_auth_backend_role Terraform resource?
resource "vault_auth_backend" "approle" {
  type  = "approle"
}

resource "vault_approle_auth_backend_role" "approle" {
  # ...
  backend   = vault_auth_backend.approle.path
  token_ttl              = "120" # 2 minutes
  token_max_ttl          = "240" # 4 minutes
  token_explicit_max_ttl = "300" # 5 minutes
}
  • Is the above generated AppRole responsible to generate dynamic credentials and it must have proper privileges to create/update/delete credentials for the database?