Microsoft SQL Server database plugin HTTP API

The Microsoft SQL Server database plugin is one of the supported plugins for the database secrets engine. This plugin generates database credentials dynamically based on configured roles for SQL Server.

Configure connection

In addition to the parameters defined by the Database Backend, this plugin has a number of parameters to further configure a connection.

Method	Path
POST	/database/config/:name

Parameters

• connection_url (string: <required>) – Specifies the SQL Server connection string, in the sqlserver://{{username}}:{{password}}@host:port?database=... format. This field can be templated and supports passing the username and password parameters in the {{field_name}} format.

• max_open_connections (int: 4) – Specifies the maximum number of open connections to the database.

• max_idle_connections (int: 0) – Specifies the maximum number of idle connections to the database. A zero uses the value of max_open_connections and a negative value disables idle connections. If larger than max_open_connections it will be reduced to be equal.

• max_connection_lifetime (string: "0s") – Specifies the maximum amount of time a connection may be reused. If <= 0s, connections are reused forever.

• username (string: <required>) – Specifies the root credential username OpenBao uses to log into SQL Server and issue/revoke dynamic users. This account must hold sufficient privileges (e.g. securityadmin / dbcreator, or equivalent) to create and drop logins, create and drop database users, and enumerate/kill sessions for the default revocation path to succeed.

• password (string: <required>) – Specifies the root credential password corresponding to username.

• contained_db (bool: false) – Set to true if the target is a contained database. Contained-database users have no corresponding server-level login, so this changes the default revocation path from ALTER LOGIN ... DISABLE + session KILL + DROP USER per mapped database + DROP LOGIN to a single DROP USER IF EXISTS against the connection's current database.

• username_template (string) - Template describing how dynamic usernames are generated.

• disable_escaping (boolean: false) – Turns off the escaping of special characters inside of the username and password fields. See the databases secrets engine docs for more information. Defaults to false.

Default Username Template

{{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 20) (.RoleName | truncate 20) (random 20) (unix_time) | truncate 128 }}

Example Usernames:

Example
DisplayName	token
RoleName	myrolename
Username	v-token-myrolename-uszt1n4cyhal4m0xtgx3-1614294836

Sample payload

{
  "plugin_name": "mssql-database-plugin",
  "allowed_roles": "readonly",
  "connection_url": "sqlserver://{{username}}:{{password}}@mssql.example.com:1433",
  "username": "sa",
  "password": "Pass-1234"
}

Sample request

$ curl \
    --header "X-Vault-Token: ..." \
    --request POST \
    --data @payload.json \
    http://127.0.0.1:8200/v1/database/config/my-mssql-database

Statements

Statements are configured during role creation and are used by the plugin to determine what is sent to the database on user creation and revocation. For more information on configuring roles see the Role API in the database secrets engine docs.

Every statement below is split on ; and executed as its own query. The following placeholders are substituted:

• {{name}} – the generated username, used by the creation and revocation statements
• {{username}} – alias of {{name}}, used by the rotation and root rotation statements
• {{password}} – the generated (or rotated) password
• {{expiration}} – the expiration timestamp, formatted 2006-01-02 15:04:05-0700. This value is informational only: SQL Server logins have no native expiration, so OpenBao's lease and its revocation of the login/user at expiry are what actually bound the credential's lifetime.

Parameters

The following are the statements used by this plugin. If not mentioned in this list the plugin does not support that statement type.

• creation_statements (list: <required>) – Specifies the database statements executed to create and configure a user. Must be a semicolon-separated string, a base64-encoded semicolon-separated string, a serialized JSON string array, or a base64-encoded serialized JSON string array. The {{name}}, {{password}}, and {{expiration}} values will be substituted. There is no built-in default; a role without creation_statements fails to issue credentials. Recommended statement (server login + database user):

  CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
  CREATE USER [{{name}}] FOR LOGIN [{{name}}];
  GRANT SELECT, INSERT, UPDATE, DELETE TO [{{name}}];

  For a contained database (contained_db=true), create a database user only:

  CREATE USER [{{name}}] WITH PASSWORD = '{{password}}';
  GRANT SELECT TO [{{name}}];

• revocation_statements (list: []) – Specifies the database statements to be executed to revoke a user. Must be a semicolon-separated string, a base64-encoded semicolon-separated string, a serialized JSON string array, or a base64-encoded serialized JSON string array. The {{name}} value will be substituted. If not provided, the plugin runs its default best-effort revoke instead:

  • Non-contained database (contained_db=false, the default):

    1. ALTER LOGIN [<login>] DISABLE
    2. KILL every active session belonging to the login (enumerated via sys.dm_exec_sessions)
    3. DROP USER for every database the login is mapped to (enumerated via the undocumented sp_msloginmappings procedure)
    4. DROP LOGIN [<login>]

    Each step runs best-effort, so an individual failure (for example, the root credential lacking VIEW SERVER STATE) does not stop later steps from being attempted; all errors encountered are combined and returned.

  • Contained database (contained_db=true): a single DROP USER IF EXISTS [<user>] against the connection's current database.

• rotation_statements (list: []) – Specifies the database statements executed to rotate the password for a static role. The {{username}} and {{password}} values will be substituted. If not provided, and contained_db=false, defaults to:

  ALTER LOGIN [{{username}}] WITH PASSWORD = '{{password}}'

  Contained databases have no default rotation statement (their users have no server login for ALTER LOGIN to target), so rotation_statements must be supplied explicitly when contained_db=true; otherwise rotation is a no-op.

• root_rotation_statements (list: []) – Specifies the database statements executed when rotating the root user's password via database/rotate-root/:name (see Configure connection). The {{username}} and {{password}} values will be substituted. If not provided, uses the same default as rotation_statements (and is subject to the same contained-db caveat above).

rollback_statements is accepted by the database secrets engine framework but is not used by this plugin.