Microsoft SQL Server database secrets engine

Microsoft SQL Server is one of the supported plugins for the database secrets engine. This plugin generates database credentials dynamically based on configured roles for SQL Server, and also supports Static Roles.

See the database secrets engine docs for more information about setting up the database secrets engine.

By default, dynamic credentials are server-level logins: NewUser creates a LOGIN plus a mapped USER in one transaction, and the default revoke path disables the login, kills its active sessions, drops its per-database users (enumerated via sp_msloginmappings), and finally drops the login. Set contained_db=true on the connection config if the target is a contained database; in that mode NewUser should only create a USER (no LOGIN) and the default revoke path simplifies to a single DROP USER IF EXISTS.

Capabilities

Plugin Name	Root Credential Rotation	Dynamic Roles	Static Roles	Username Customization
mssql-database-plugin	Yes	Yes	Yes	Yes

Setup

1. Enable the database secrets engine if it is not already enabled:

   $ bao secrets enable database
   Success! Enabled the database secrets engine at: database/

   By default, the secrets engine will enable at the name of the engine. To enable the secrets engine at a different path, use the -path argument.

2. Configure OpenBao with the proper plugin and connection information:

   $ bao write database/config/my-mssql-database \
       plugin_name="mssql-database-plugin" \
       allowed_roles="my-role" \
       connection_url='sqlserver://{{username}}:{{password}}@mssql.example.com:1433' \
       username="sa" \
       password="Pass-1234"
   Success! Data written to: database/config/my-mssql-database

3. Configure a role that maps a name in OpenBao to a set of SQL statements to execute to create the database credential. This recommended statement creates a server login plus a mapped database user:

   $ bao write database/roles/my-role \
       db_name="my-mssql-database" \
       creation_statements="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}'; \
           CREATE USER [{{name}}] FOR LOGIN [{{name}}]; \
           GRANT SELECT, INSERT, UPDATE, DELETE TO [{{name}}];" \
       default_ttl="1h" \
       max_ttl="24h"
   Success! Data written to: database/roles/my-role

   The {{name}}, {{username}}, and {{password}} fields will be populated by the plugin with dynamically generated values. The {{expiration}} field is also supported, but is informational only: SQL Server logins have no native expiration, so OpenBao's lease plus its revocation of the login/user at expiry are what actually bound the credential's lifetime.

   For a contained database, set contained_db=true on the connection config and use a simpler user-only creation statement:

   $ bao write database/roles/my-contained-role \
       db_name="my-mssql-database" \
       creation_statements="CREATE USER [{{name}}] WITH PASSWORD = '{{password}}'; \
           GRANT SELECT TO [{{name}}];" \
       default_ttl="1h" \
       max_ttl="24h"

Usage

After the secrets engine is configured and a user/machine has an OpenBao token with the proper permission, it can generate credentials.

1. Generate a new credential by reading from the /creds endpoint with the name of the role:

   $ bao read database/creds/my-role
   Key                Value
   ---                -----
   lease_id           database/creds/my-role/2f6a614c-4aa2-7b19-24b9-ad944a8d4de6
   lease_duration     1h
   lease_renewable    true
   password           SsnoaA-8Tv4t34f41baD
   username           v-token-my-role-a1B2c3D4e5F6g7H8i9J0-1717000000

API

The full list of configurable options can be seen in the Microsoft SQL Server database plugin API page.

For more information on the database secrets engine's HTTP API please see the Database secrets engine API page.