Database secrets engine for Microsoft SQL Server

30min
|
Vault

Vault can generate secrets on-demand for some systems. For example, when an app needs to access an database server, it asks Vault for credentials. Vault will generate credential granting permissions to access the database server. In addition, Vault will automatically revoke this credential after the time-to-live (TTL) expires.

Challenge

Data protection is a top priority, and database credential rotation is a critical part of any data protection initiative. Each role has a different set of permissions granted to access the database. When a system is attacked by hackers, continuous credential rotation becomes necessary and needs to be automated.

Solution

Applications ask Vault for database credentials rather than setting them as environment variables. The administrator specifies the TTL of the database credentials to enforce its validity so that they are automatically revoked when they are no longer used. vault-dynamic-secrets

Each app instance can get unique credentials that they don't have to share. By making those credentials short-lived, you reduce the chance that they might be compromised. If an app was compromised, the credentials used by the app can be revoked rather than changing more global sets of credentials.

Personas

The end-to-end scenario described in this tutorial involves two personas:

admin with privileged permissions to configure secrets engines

apps read the secrets from Vault

Prerequisites

This lab was tested on macOS using an x86_64 based processor. If you are running macOS on an Apple silicon-based processor, use a x86_64 based Linux virtual machine in your preferred cloud provider.

To perform the tasks described in this tutorial, you need to have:

HCP or Vault Community Edition environment
jq installed
Docker installed
ngrok installed and configured with an auth token (HCP Vault only)

Scenario Introduction

In this tutorial, you are going to configure the database secrets engine to support Microsoft SQL Server, and add users to the myapp database. The Vault-generated MSSQL credentials will be assigned the built in db_datareader role to have read only access to the database.

Lab setup

Start MSSQL

Start MSSQL using Docker.

$ docker run -e "ACCEPT_EULA=Y" \
   -e "MSSQL_SA_PASSWORD=P(@)ssword11" \
   -p 1433:1433 \
   -d mcr.microsoft.com/mssql/server:2022-latest

Export an environment variable for the MSSQL SA user.
```
$ export SQL_USERNAME=sa
```
Export an environment variable for the password created for the SA user.
```
$ export SQL_PASSWORD=P(@)ssword11
```
Note
Environment variables are used to streamline the Vault configuration during the tutorial.

Write a script that creates a database called myapp and inserts sample data.

$ tee configure.sql <<EOF
USE [master];
GO
CREATE DATABASE myapp;
GO
USE [myapp];
GO
CREATE TABLE location (street varchar(20), city varchar(20), state varchar(20));
GO
INSERT INTO location (street, city, state)
VALUES ('main', 'anytown', 'california');
EOF

Use sqlcmd to run the script to create the database and insert sample data.

$ sqlcmd -U $SQL_USERNAME -P $SQL_PASSWORD -S 127.0.0.1 -i configure.sql

Example output:

Changed database context to 'master'.
Changed database context to 'myapp'.

(1 rows affected)

Verify you can read the data from the location table.

$ sqlcmd -U $SQL_USERNAME -P $SQL_PASSWORD -S 127.0.0.1 -Q "USE [myapp]; SELECT street FROM location;"

street
--------------------
main

Start Vault

Note

If you do not have access to an HCP Vault cluster, visit the Create a Vault Cluster on HCP tutorial.

Launch the HCP Portal and login.
Click Vault in the left navigation pane.
In the Vault clusters pane, click vault-cluster.
Under Cluster URLs, click Public Cluster URL.
In a terminal, set the VAULT_ADDR environment variable to the copied address.
```
$ export VAULT_ADDR=<Public_Cluster_URL>
```
Return to the Overview page and click Generate token.
Within a few moments, a new token will be generated.
Copy the Admin Token.
Return to the terminal and set the VAULT_TOKEN environment variable.
```
$ export VAULT_TOKEN=<token>
```
Set the VAULT_NAMESPACE environment variable to admin.
```
$ export VAULT_NAMESPACE=admin
```
The admin namespace is the top-level namespace automatically created by HCP Vault. All CLI operations default to use the namespace defined in this environment variable.
For HCP Vault to interact with resources running on your local machine, a tunnel needs to be established.

In another terminal, start ngrok and connect to MSSQL.

$ ngrok tcp 127.0.0.1:1433

Example output:

ngrok                                                                                                                                                              (Ctrl+C to quit)

Session Status                online
Account                       username (Plan: Free)
Update                        update available (version 3.0.5, Ctrl-U to update)
Version                       3.0.3
Region                        United States (us)
Latency                       32.791235ms
Web Interface                 http://127.0.0.1:4040
Forwarding                    tcp://d12b-34-567-89-10.ngrok.io:12345 -> 127.0.0.1:5432

Connections                   ttl     opn     rt1     rt5     p50     p90
                              0       0       0.00    0.00    0.00    0.00

Copy the ngrok forwarding address.
Return to the terminal where you set the VAULT_ADDR environment variable and set an environment variable for the ngrok address. Do not include tcp://.
```
$ export SQL_ADDR=<actual-address-from-ngrok>
```

The HCP Vault server is ready, you may proceed with the lab.

Start a Vault dev server with root as the root token.
```
$ vault server -dev -dev-root-token-id root
```
The Vault dev server defaults to running at 127.0.0.1:8200. The server is initialized and unsealed.
Insecure operation
Do not run a Vault dev server in production. This approach starts a Vault server with an in-memory database and runs in an insecure way.
In another terminal, export an environment variable for the vault CLI to address the Vault server.
```
$ export VAULT_ADDR=http://127.0.0.1:8200
```
Export an environment variable for the vault CLI to authenticate with the Vault server.
```
$ export VAULT_TOKEN=root
```
Note
For these tasks, you can use Vault's root token. However, it is recommended that root tokens are only used for enough initial setup or in emergencies. As a best practice, use an authentication method or token that meets the policy requirements.
Export an environment variable for the MSSQL instance.
```
$ export SQL_ADDR=127.0.0.1
```

The Vault server is ready, you may proceed with the lab.

Configure the database secrets engine

(Persona: admin)

The database secrets engine generates database credentials dynamically based on configured roles.

Enable the database secrets engine at the database/ path.
```
$ vault secrets enable database
```
The database secrets engine is enabled.
Configure the database secrets engine with the connection credentials for the MSSQL database.
```
$ vault write database/config/mssql \
    plugin_name=mssql-database-plugin \
    connection_url=sqlserver://{{username}}:{{password}}@$SQL_ADDR \
    allowed_roles="readonly" \
    username=$SQL_USERNAME \
    password=$SQL_PASSWORD
```
The secrets engine is configured to work with MSSQL.
Read the Database Root Credential Rotation tutorial to learn about rotating the root credential immediately after the initial configuration of each database.
Define the SQL used to create credentials for the myapp database.
```
$ tee readonly.sql <<EOF
USE [myapp];
CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
CREATE USER [{{name}}] FOR LOGIN [{{name}}];
EXEC sp_addrolemember db_datareader, [{{name}}];
EOF
```
The SQL contains the templatized fields {{name}}, and {{password}}. These values are provided by Vault when the credentials are created.
Note
Amazon RDS does not support using the sysadmin role, which is used by default during Vault's revocation process for MSSQL. Learn more about adding a custom revocation statement in the Database Secrets Engine for Microsoft SQL on AWS RDS tutorial.

Create the role named readonly that creates credentials with the readonly.sql script.

$ vault write database/roles/readonly \
      db_name=mssql \
      creation_statements=@readonly.sql \
      default_ttl=1h \
      max_ttl=24h

Note

Most API calls using cURL do not generate output. If an API is expected to produce output, an example will be displayed.

Enable the database secrets engine at the database/ path.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request POST \
   --data '{"type":"database"}' \
   $VAULT_ADDR/v1/sys/mounts/database

Create an API request payload with the database configuration.

$ tee payload.json <<EOF
{
  "plugin_name": "mssql-database-plugin",
  "connection_url": "sqlserver://{{username}}:{{password}}@$SQL_ADDR",
  "allowed_roles": "readonly",
  "username": "$SQL_USERNAME",
  "password": "$SQL_PASSWORD"
}
EOF

Configure the database secrets engine with the connection credentials for the MSSQL database.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --data @payload.json \
   $VAULT_ADDR/v1/database/config/mssql

Create an API request payload containing the database role definition that creates credentials for the myapp database.
```
$ tee readonly-role.json <<EOF
{
    "db_name": "mssql",
    "creation_statements": [
        "USE [myapp];",
        "CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';",
        "CREATE USER [{{name}}] FOR LOGIN [{{name}}];",
        "EXEC sp_addrolemember db_datareader, [{{name}}];"
    ],
    "default_ttl": "1h",
    "max_ttl": "24h"
}
EOF
```
The creation_statements parameter contains the SQL code with templatized fields {{name}}, and {{password}}. These values are provided by Vault when the credentials are created.
The revocation_statements parameter contains the SQL code to revoke users from MSSQL running on AWS RDS.
Note
Amazon RDS does not support using the sysadmin role, which is used by default during Vault's revocation process for MSSQL. Learn more about adding a custom revocation statement in the Database Secrets Engine for Microsoft SQL on AWS RDS tutorial.

Create the readonly role.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
--header "X-Vault-Namespace: $VAULT_NAMESPACE" \
--request POST --data @readonly-role.json \
$VAULT_ADDR/v1/database/roles/readonly

Enable the database secrets engine at the database/ path.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request POST \
   --data '{"type":"database"}' \
   $VAULT_ADDR/v1/sys/mounts/database

Create an API request payload with the database configuration.

$ tee payload.json <<EOF
{
  "plugin_name": "mssql-database-plugin",
  "connection_url": "sqlserver://{{username}}:{{password}}@$SQL_ADDR",
  "allowed_roles": "readonly",
  "username": "$SQL_USERNAME",
  "password": "$SQL_PASSWORD"
}
EOF

Configure the database secrets engine with the connection credentials for the MSSQL database.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
   --data @payload.json \
   $VAULT_ADDR/v1/database/config/mssql

Create an API request payload containing the database role definition that creates credentials for the myapp database.
```
$ tee readonly-role.json <<EOF
{
    "db_name": "mssql",
    "creation_statements": [
        "USE [myapp];",
        "CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';",
        "CREATE USER [{{name}}] FOR LOGIN [{{name}}];",
        "EXEC sp_addrolemember db_datareader, [{{name}}];"
    ],
    "default_ttl": "1h",
    "max_ttl": "24h"
}
EOF
```
The creation_statements parameter contains the SQL code with templatized fields {{name}}, and {{password}}. These values are provided by Vault when the credentials are created.
Note
Amazon RDS does not support using the sysadmin role, which is used by default during Vault's revocation process for MSSQL. Learn more about adding a custom revocation statement in the Database Secrets Engine for Microsoft SQL on AWS RDS tutorial.

Create the readonly role.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
--request POST --data @readonly-role.json \
$VAULT_ADDR/v1/database/roles/readonly

Open a web browser and launch the Vault UI. Login with the token value saved to the VAULT_TOKEN environment variable created in the Lab setup section.
Tip
From the CLI, run echo $VAULT_TOKEN to retrieve the value.
Navigate to Secrets and click Enable new engine.
From the Enable a Secrets Engine page Infra section, select Databases and click Next.
Leave all fields with the default values, and click Enable Engine.
Click Create connection to create a connection from Vault to the MSSQL instance.
Click the Database plugin pulldown menu and select MSSQL.
Enter the following information for the MSSQL plugin form:
- Connection name: mssql
- Connection URL: Retrieve value saved to the SQL_ADDR environment variable created in the Lab setup section and enter sqlserver://{{username}}:{{password}}@<SQL_ADDR>.
- Username: Enter the value saved to the SQL_USERNAME environment variable created in the Lab setup section.
- Password: Enter the value saved to the SQL_PASSWORD environment variable created in the Lab setup section.
Click Create database and then click Enable without rotating.
Read the Database Root Credential Rotation tutorial to learn about rotating the root credential immediately after the initial configuration of each database.
Click Add role.
In the Role name enter readonly.
Click the Type of role pulldown menu and select Dynamic.
In the Creation statements text box enter:
```
USE [myapp];
CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
CREATE USER [{{name}}] FOR LOGIN [{{name}}];
EXEC sp_addrolemember db_datareader, [{{name}}];
```
Note
Amazon RDS does not support using the sysadmin role, which is used by default during Vault's revocation process for MSSQL. Learn more about adding a custom revocation statement in the Database Secrets Engine for Microsoft SQL on AWS RDS tutorial.
Click Create role.

Vault is now configured to support generating dynamic credentials for Microsoft SQL Server.

Test MSSQL access

(Persona: apps)

Mimic the steps typically performed by an application to generate dynamic credentials and read data from the myapp database.

Read credentials from the readonly database role.

$ vault read database/creds/readonly

Key                Value
---                -----
lease_id           database/creds/readonly/y2X3pgeWA1CZ3MATsZBqKmrX.lVGQF
lease_duration     1h
lease_renewable    true
password           K5zezkH-GcQZSukOmKUp
username           v-token-hcp-root-readonly-X0aL2ZMSKNCQ3nMFaSgP-1668538290

Vault generates a unique username and password to access MSSQL.

Read credentials from the readonly database role and save them to an environment variable.
```
$ TEMP_CREDS=$(vault read database/creds/readonly -format=json | jq -r .data)
```

Connect to the MSSQL database and list the sample data using the credentials stored in the TEMP_CREDS environment variable.

$ sqlcmd -U $(echo $TEMP_CREDS | jq -r .username) \
   -P $(echo $TEMP_CREDS | jq -r .password) \
   -S 127.0.0.1 -Q "USE [myapp]; SELECT street FROM location;"

The output displays the sample data from the myapp database.

street
--------------------
main

Attempt to insert data into the table.

$ sqlcmd -U $(echo $TEMP_CREDS | jq -r .username) \
   -P $(echo $TEMP_CREDS | jq -r .password) -S 127.0.0.1 \
   -Q "USE [myapp]; INSERT INTO location (street, city, state) VALUES ('second', 'anytown', 'california');"

The insert permission was denied because the temporary credentials were only assigned a role with read permissions.

Changed database context to 'myapp'.
Msg 229, Level 14, State 5, Server EC2AMAZ-S3ASM3ST, Line 1
The INSERT permission was denied on the object 'location', database 'myapp', schema 'dbo'.

Read credentials from the readonly database role and save them to an environment variable.

$ TEMP_CREDS=$(curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request GET \
   $VAULT_ADDR/v1/database/creds/readonly | jq) && echo $TEMP_CREDS

Vault generates a unique username and password to access MSSQL.

Example output:

{
  "request_id": "2e4fa232-1a44-a48d-7476-4efc7a27f3ba",
  "lease_id": "database/creds/readonly/DHtlHJBQEUENqFlXZ9jjitEa.eRRcd",
  "renewable": true,
  "lease_duration": 3600,
  "data": {
    "password": "5FW-aU-tHa0scgoGaQsk",
    "username": "v-token-hcp-root-readonly-mVUgt5VjjWgtckopWhO4-1668795862"
  },
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

Connect to the MSSQL database and list the sample data using the credentials stored in the TEMP_CREDS environment variable.

$ sqlcmd -U $(echo $TEMP_CREDS | jq -r .data.username) \
   -P $(echo $TEMP_CREDS | jq -r .data.password) \
   -S 127.0.0.1 -Q "USE [myapp]; SELECT street FROM location;"

The output displays the sample data from the myapp database.

Example output:

street
--------------------
main

Attempt to insert data into the table.

$ sqlcmd -U $(echo $TEMP_CREDS | jq -r .data.username) \
   -P $(echo $TEMP_CREDS | jq -r .data.password) -S 127.0.0.1 \
   -Q "USE [myapp]; INSERT INTO location (street, city, state) VALUES ('second', 'anytown', 'california');"

The insert permission was denied because the temporary credentials were only assigned a role with read permissions.

Example output:

Changed database context to 'myapp'.
Msg 229, Level 14, State 5, Server EC2AMAZ-S3ASM3ST, Line 1
The INSERT permission was denied on the object 'location', database 'myapp', schema 'dbo'.

Read credentials from the readonly database role and save them to an environment variable.

$ TEMP_CREDS=$(curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request GET \
   $VAULT_ADDR/v1/database/creds/readonly | jq) && echo $TEMP_CREDS

Vault generates a unique username and password to access MSSQL.

Example output:

{
  "request_id": "2e4fa232-1a44-a48d-7476-4efc7a27f3ba",
  "lease_id": "database/creds/readonly/DHtlHJBQEUENqFlXZ9jjitEa.eRRcd",
  "renewable": true,
  "lease_duration": 3600,
  "data": {
    "password": "5FW-aU-tHa0scgoGaQsk",
    "username": "v-token-hcp-root-readonly-mVUgt5VjjWgtckopWhO4-1668795862"
  },
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

Connect to the MSSQL database and list the sample data using the credentials stored in the TEMP_CREDS environment variable.

$ sqlcmd -U $(echo $TEMP_CREDS | jq -r .data.username) \
   -P $(echo $TEMP_CREDS | jq -r .data.password) \
   -S 127.0.0.1 -Q "USE [myapp]; SELECT street FROM location;"

The output displays the sample data from the myapp database.

Example output:

street
--------------------
main

Attempt to insert data into the table.

$ sqlcmd -U $(echo $TEMP_CREDS | jq -r .data.username) \
   -P $(echo $TEMP_CREDS | jq -r .data.password) -S 127.0.0.1 \
   -Q "USE [myapp]; INSERT INTO location (street, city, state) VALUES ('second', 'anytown', 'california');"

The insert permission was denied because the temporary credentials were only assigned a role with read permissions.

Example output:

Changed database context to 'myapp'.
Msg 229, Level 14, State 5, Server EC2AMAZ-S3ASM3ST, Line 1
The INSERT permission was denied on the object 'location', database 'myapp', schema 'dbo'.

From the readonly role page, click Generate credentials.
Make note of the Lease ID, you will need this in subsequent sections.
Click the copy icon for the generated Username.
Switch to the terminal you configured the SQL_ADDR environment variable from the Lab setup section.
Export an environment variable for temporary credentials.
```
$ export TEMP_USERNAME=<actual-username-from-vault>
```
Return to the Vault UI and click the copy icon for the generated Password.
Switch back to the terminal session and export an environment variable for the temporary password.
```
$ export TEMP_PASSWORD=<actual-password-from-vault>
```

Connect to the MSSQL database and list the sample data using the temporary credentials from Vault.

$ sqlcmd -U $(echo $TEMP_USERNAME) \
-P $(echo $TEMP_PASSWORD) \
-S 127.0.0.1 -Q "USE [myapp]; SELECT street FROM location;"

The output displays the sample data from the myapp database.

Example output:

Changed database context to 'myapp'.
street
--------------------
main

(1 rows affected)

Attempt to insert data into the table.

$ sqlcmd -U $(echo $TEMP_USERNAME) \
-P $(echo $TEMP_PASSWORD) \
-S 127.0.0.1 \
-Q "USE [myapp]; INSERT INTO location (street, city, state) VALUES ('second', 'anytown', 'california');"

The insert permission was denied because the temporary credentials were only assigned a role with read permissions.

Example output:

Changed database context to 'myapp'.
Msg 229, Level 14, State 5, Server EC2AMAZ-NMO0SMC, Line 1
The INSERT permission was denied on the object 'location', database 'myapp', schema 'dbo'.

Manage leases

(Persona: admin)

The credentials are managed by the lease ID and remain valid for the lease duration (TTL) or until revoked. Once revoked the credentials are no longer valid.

List the existing leases.

$ vault list sys/leases/lookup/database/creds/readonly

Keys
----
0XRYUO9ivzBz44lOiYrtmrBt.OywaL
PQ32SURhydsSqu286tvsOpli.OywaL

All valid leases for database credentials are displayed.

Create a variable that stores the first lease ID.

$ LEASE_ID=$(vault list -format=json sys/leases/lookup/database/creds/readonly | jq -r ".[0]")

Renew the lease for the database credential by passing its lease ID.

$ vault lease renew database/creds/readonly/$LEASE_ID

Key                Value
---                -----
lease_id           database/creds/readonly/IQKUMCTg3M5QTRZ0abmLKjTX
lease_duration     1h
lease_renewable    true

The TTL of the renewed lease is set to 1h.

Revoke the lease without waiting for its expiration.

$ vault lease revoke database/creds/readonly/$LEASE_ID

All revocation operations queued successfully!

List the remaining leases.

$ vault list sys/leases/lookup/database/creds/readonly

Keys
----
PQ32SURhydsSqu286tvsOpli.OywaL

The lease is no longer valid and is not displayed.

Revoke all remaining leases associated with the path database/creds/readonly.
```
$ vault lease revoke -prefix database/creds/readonly

All revocation operations queued successfully!
```
The prefix flag matches all valid leases with the path prefix of database/creds/readonly.

List the existing leases.

$ vault list sys/leases/lookup/database/creds/readonly

No value found at sys/leases/lookup/database/creds/readonly/

All the leases with this path as a prefix have been revoked.

List the existing lease IDs.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq -r ".data.keys"

All valid leases for database credentials are displayed.

Example output:

[
  "PmPDYV1HNUuYqnynAVyRLu8R.bS3H1"
]

Create a variable that stores the first (and only) lease ID.

$ LEASE_ID=$(curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq -r ".data.keys[0]")

Renew the lease for the database credential by passing its lease ID.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request PUT \
   --data "{ \"lease_id\": \"database/creds/readonly/$LEASE_ID\" }" \
   $VAULT_ADDR/v1/sys/leases/renew | jq

The TTL of the renewed lease is set and displayed as 3600 seconds (1 hour).

Example output:

{
  "request_id": "bc000c54-3114-4739-457d-dbed8137c0f0",
  "lease_id": "database/creds/readonly/PmPDYV1HNUuYqnynAVyRLu8R.bS3H1",
  "renewable": true,
  "lease_duration": 3600,
  "data": null,
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

Revoke the lease without waiting for its expiration.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request PUT \
   --data "{ \"lease_id\": \"database/creds/readonly/$LEASE_ID\" }" \
   $VAULT_ADDR/v1/sys/leases/revoke

List the existing leases.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq

The lease is no longer valid and is not displayed.

Example output:

{
  "errors": []
}

Read new credentials from the readonly database role.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request GET \
   $VAULT_ADDR/v1/database/creds/readonly | jq

Revoke all the leases associated with the path database/creds/readonly.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request PUT \
   $VAULT_ADDR/v1/sys/leases/revoke-prefix/database/creds/readonly

The prefix flag matches all valid leases with the path prefix of database/creds/readonly.

List the existing leases.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq

All the leases with this path as a prefix have been revoked.

List the existing lease IDs.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq -r ".data.keys"

All valid leases for database credentials are displayed.

Example output:

[
  "PmPDYV1HNUuYqnynAVyRLu8R.bS3H1"
]

Create a variable that stores the first (and only) lease ID.

$ LEASE_ID=$(curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq -r ".data.keys[0]")

Renew the lease for the database credential by passing its lease ID.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request PUT \
   --data "{ \"lease_id\": \"database/creds/readonly/$LEASE_ID\" }" \
   $VAULT_ADDR/v1/sys/leases/renew | jq

The TTL of the renewed lease is set and displayed as 3600 seconds (1 hour).

Example output:

{
  "request_id": "bc000c54-3114-4739-457d-dbed8137c0f0",
  "lease_id": "database/creds/readonly/PmPDYV1HNUuYqnynAVyRLu8R.bS3H1",
  "renewable": true,
  "lease_duration": 3600,
  "data": null,
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

Revoke the lease without waiting for its expiration.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request PUT \
   --data "{ \"lease_id\": \"database/creds/readonly/$LEASE_ID\" }" \
   $VAULT_ADDR/v1/sys/leases/revoke

List the existing leases.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq

The lease is no longer valid and is not displayed.

Example output:

{
  "errors": []
}

Read new credentials from the readonly database role.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request GET \
   $VAULT_ADDR/v1/database/creds/readonly | jq

Revoke all the leases associated with the path database/creds/readonly.
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request PUT \
   $VAULT_ADDR/v1/sys/leases/revoke-prefix/database/creds/readonly
```
The prefix flag matches all valid leases with the path prefix of database/creds/readonly.

List the existing leases.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request LIST \
   $VAULT_ADDR/v1/sys/leases/lookup/database/creds/readonly | jq

All the leases with this path as a prefix have been revoked.

Return to the Vault UI and click Access, and then click Leases.
Enter the lease ID from the Test MSSQL access section and click Lookup.
Observe the TTL for the lease and then click Renew lease.
Click Renew lease.
The TTL is renewed to 3600 seconds (1 hour).
Click Revoke lease and select Confirm.
The lease is revoked and no longer available.
Instead of revoking individual leases, you can also revoke all leases at a specific path. To learn how to revoke all leases at a specific path, review the CLI or API workflow in this tutorial.

Implement a password policy

(Persona: admin)

Refer to the password policy tutorial for more details.

Define a password policy

The database secrets engines generate passwords that adhere to a default pattern that may be overridden with a new password policy. A policy defines the rules and requirements that the password must adhere to and can provide that password directly through a new endpoint or within secrets engines.

The passwords you want to generate adhere to these requirements.

length of 20 characters
at least 1 uppercase character
at least 1 lowercase character
at least 1 number
at least 1 symbol

Define a password policy in a file named password-policy.hcl.
```
1 2 3 4 5 6 7 8 9 1011121314151617181920212223$ tee password-policy.hcl <<EOF
length=20

rule "charset" {
  charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  min-chars = 1
}

rule "charset" {
  charset = "abcdefghijklmnopqrstuvwxyz"
  min-chars = 1
}

rule "charset" {
  charset = "0123456789"
  min-chars = 1
}

rule "charset" {
  charset = "!@#$%^&*"
  min-chars = 1
}
EOF
```
The policy is written in HashiCorp Configuration Language (HCL).
Each rule stanza defines a character set and the minimum number of occurrences those characters need to appear in the generated password. These rules are cumulative so each one adds more requirements on the password generated.
Line 2: The length field sets the length of the password returned to 20 characters.
Lines 4-7: The charset rule includes uppercase characters with a minimum of 1 character.
Lines 9-12: The charset rule includes lowercase characters with a minimum of 1 character.
Lines 14-17: The charset rule includes numbers with a minimum of 1 character.
Lines 19-22: The charset rule includes special characters with a minimum of 1 character.
Create a Vault password policy named mssql with the password policy rules defined in password-policy.hcl.
```
$ vault write sys/policies/password/mssql policy=@password-policy.hcl

Success! Data written to sys/polices/password/password-policy
```
This policy can now be accessed directly to generate a password or referenced by its name mssql when configuring supported secrets engines.

Generate a password from the mssql password policy.

$ vault read sys/policies/password/mssql/generate

Key         Value
---         -----
password    #v!RQDHxHunJ1TUmCyys

The password generated adheres to the defined requirements.

Add a password policy to the database configuration

Now that the password policy has been created, you can assign the policy to the database secrets engine configuration.

Configure the database secrets engine with the mssql password policy.

$ vault write database/config/mssql \
     password_policy="mssql"

Read credentials from the readonly database role with the mssql policy attached.
```
$ vault read database/creds/readonly
```
The credentials display the username and password generated. The password generated adheres to the mssql password policy defined in the secrets engine's configuration.

Create an API request payload with the database configuration.

$ tee payload.json <<EOF
{
  "plugin_name": "mssql-database-plugin",
  "connection_url": "sqlserver://{{username}}:{{password}}@$SQL_ADDR",
  "allowed_roles": "readonly",
  "username": "$SQL_USERNAME",
  "password": "$SQL_PASSWORD",
  "password_policy": "mssql"
}
EOF

Configure the database secrets engine with the mssql password policy.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
     --data @payload.json \
     $VAULT_ADDR/v1/database/config/mssql

Read credentials from the readonly database role with the mssql policy attached.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
   --request GET \
   $VAULT_ADDR/v1/database/creds/readonly | jq

The credentials display the username and password generated. The password generated adheres to the mssql password policy defined in the secrets engine's configuration.

Example output:

...snip...
"data": {
 "password": "8pH4XNiFd*aZ9jd%IBDV",
 "username": "v-token-hcp-root-readonly-u9SzHvocSqB8kRqRDrje-1669141998"
},
...snip...

Create an API request payload with the database configuration.

$ tee payload.json <<EOF
{
  "plugin_name": "mssql-database-plugin",
  "connection_url": "sqlserver://{{username}}:{{password}}@$SQL_ADDR",
  "allowed_roles": "readonly",
  "username": "$SQL_USERNAME",
  "password": "$SQL_PASSWORD",
  "password_policy": "mssql"
}
EOF

Configure the database secrets engine with the mssql password policy.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
     --data @payload.json \
     $VAULT_ADDR/v1/database/config/mssql

Read credentials from the readonly database role with the mssql policy attached.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
   --request GET \
   $VAULT_ADDR/v1/database/creds/readonly | jq

The credentials display the username and password generated. The password generated adheres to the mssql password policy defined in the secrets engine's configuration.

Example output:

...snip...
"data": {
 "password": "8pH4XNiFd*aZ9jd%IBDV",
 "username": "v-token-hcp-root-readonly-u9SzHvocSqB8kRqRDrje-1669141998"
},
...snip...

Return to the Vault UI and click Secrets, and then click database.
Click the Connections tab, and then click mssql.
Click Edit configuration.
Click the Use custom password policy toggle button.
Type mssql in the text box and click Save.
From the mssql configuration page, click readonly.
Click Generate credentials.
Click the view icon for the generated Password.
The password generated adheres to the mssql password policy defined in the secrets engine's configuration.

Define a username template

(Persona: apps)

The database secrets engine generates usernames that adhere to a default pattern. A customized username template may be provided to meet the needs of your organization.

Note

Ensure that custom username templates include enough randomness to prevent the same username being generated multiple times.

Read credentials from the readonly database role.
```
$ vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/ZxoKlbklsliYA4hZs7umoPIz
lease_duration     1h
lease_renewable    true
password           9MSegMz7N1Fr69ZTyb#D
username           v-token-readonly-wGLPkpDyc6AgqBfMZTD3-1604195404
```
The generated username, v-token-readonly-wGLPkpDyc6AgqBfMZTD3-1604195404, uses the default pattern expressed as a Go template, {{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 8) (.RoleName | truncate 8) (random 20) (unix_time) | truncate 63 }}.
Refer to the Username Templating documentation to learn more functions that can be applied.
Configure the database secrets engine with the username template.
```
$ vault write database/config/mssql \
    username_template="myorg-{{.RoleName}}-{{unix_time}}-{{random 8}}"
```
This username template is prefixed with myorg-, uses the name of role, readonly, the unix timestamp in seconds, and a random sequence of 8 characters.

Read credentials from the readonly database role.

$ vault read database/creds/readonly

Key                Value
---                -----
lease_id           database/creds/readonly/NOCGtSbz7g4FFjcztX6Bqh3S
lease_duration     1h
lease_renewable    true
password           -h3B-JteYjgOPYIC6dGQ
username           myorg-readonly-1616447348-af9eHMWD

The username generated adheres to the template provided to the configuration.

Read credentials from the readonly database role.
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request GET \
    $VAULT_ADDR/v1/database/creds/readonly | jq
```
The generated username uses the default pattern expressed as a Go template {{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 8) (.RoleName | truncate 8) (random 20) (unix_time) | truncate 63 }}.
Example output:
```
...snip...
"data": {
 "password": "8pH4XNiFd*aZ9jd%IBDV",
 "username": "v-token-hcp-root-readonly-u9SzHvocSqB8kRqRDrje-1669141998"
},
...snip...
```
Refer to the Username Templating documentation to learn more functions that can be applied.

Create an API request payload with the database configuration to include the username_template key.

$ tee payload.json <<EOF
{
  "plugin_name": "mssql-database-plugin",
  "connection_url": "sqlserver://{{username}}:{{password}}@$SQL_ADDR",
  "allowed_roles": "readonly",
  "username": "$SQL_USERNAME",
  "password": "$SQL_PASSWORD",
  "password_policy": "mssql",
  "username_template": "myorg-{{.RoleName}}-{{unix_time}}-{{random 8}}"
}
EOF

This username template is prefixed with myorg-, uses the name of role, readonly, the unix timestamp in seconds, and a random sequence of 8 characters.

Re-configure the database secrets engine with the connection credentials for the MSSQL database.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
     --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
     --data @payload.json \
     $VAULT_ADDR/v1/database/config/mssql

Read credentials from the readonly database role.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --header "X-Vault-Namespace: $VAULT_NAMESPACE" \
    --request GET \
    $VAULT_ADDR/v1/database/creds/readonly | jq

The username generated adheres to the template provided to the configuration.

Example output:

{
  "request_id": "b22efbd9-a643-1532-8e38-4729e7513333",
  "lease_id": "database/creds/readonly/HAjGz8RpkZNcLHAL1QiOws3L.bS3H1",
  "renewable": true,
  "lease_duration": 3600,
  "data": {
    "password": "aPA@m8rllupPLIMFcCel",
    "username": "myorg-readonly-1669056262-d5C5RdRf"
  },
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

Read credentials from the readonly database role.
```
$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --request GET \
    $VAULT_ADDR/v1/database/creds/readonly | jq
```
The generated username uses the default pattern expressed as a Go template {{ printf "v-%s-%s-%s-%s" (.DisplayName | truncate 8) (.RoleName | truncate 8) (random 20) (unix_time) | truncate 63 }}.
Example output:
```
...snip...
"data": {
 "password": "8pH4XNiFd*aZ9jd%IBDV",
 "username": "v-token-hcp-root-readonly-u9SzHvocSqB8kRqRDrje-1669141998"
},
...snip...
```
Refer to the Username Templating documentation to learn more functions that can be applied.

Create an API request payload with the database configuration to include the username_template key.

$ tee payload.json <<EOF
{
  "plugin_name": "mssql-database-plugin",
  "connection_url": "sqlserver://{{username}}:{{password}}@$SQL_ADDR",
  "allowed_roles": "readonly",
  "username": "$SQL_USERNAME",
  "password": "$SQL_PASSWORD",
  "password_policy": "mssql",
  "username_template": "myorg-{{.RoleName}}-{{unix_time}}-{{random 8}}"
}
EOF

This username template is prefixed with myorg-, uses the name of role, readonly, the unix timestamp in seconds, and a random sequence of 8 characters.

Re-configure the database secrets engine with the connection credentials for the MSSQL database.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" --request POST \
     --data @payload.json \
     $VAULT_ADDR/v1/database/config/mssql

Read credentials from the readonly database role.

$ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --request GET \
    $VAULT_ADDR/v1/database/creds/readonly | jq

The username generated adheres to the template provided to the configuration.

Example output:

{
  "request_id": "b22efbd9-a643-1532-8e38-4729e7513333",
  "lease_id": "database/creds/readonly/HAjGz8RpkZNcLHAL1QiOws3L.bS3H1",
  "renewable": true,
  "lease_duration": 3600,
  "data": {
    "password": "aPA@m8rllupPLIMFcCel",
    "username": "myorg-readonly-1669056262-d5C5RdRf"
  },
  "wrap_info": null,
  "warnings": null,
  "auth": null
}

Click Back to return to the mssql configuration page.
Click Edit configuration.
Click the Username template toggle button.
Copy the following into the Enter the custom username template to use text box.
```
myorg-{{.RoleName}}-{{unix_time}}-{{random 8}}
```
This username template is prefixed with myorg-, uses the name of role, readonly, the unix timestamp in seconds, and a random sequence of 8 characters.
Refer to the Username Templating documentation to learn more functions that can be applied.
Click Save.
From the mssql configuration page, click readonly.
Click Generate credentials.
Click the view icon for the generated Username.
The username generated adheres to the template defined in the secrets engine's configuration.

Next Steps

There are some tools available to help integrate your applications with Vault's database secrets engine. Using those tools, the existing applications may require minimum to no code change to work with Vault.

Refer to the following tutorials:

Help and Reference

Database secrets engine MSSQL on AWS RDS

Next Collection

Data encryption