• HashiCorp Developer

  • HashiCorp Cloud Platform
  • Terraform
  • Packer
  • Consul
  • Vault
  • Boundary
  • Nomad
  • Waypoint
  • Vagrant
Vault
  • Install
  • Tutorials
  • Documentation
  • API
  • Integrations
  • Try Cloud(opens in new tab)
  • Sign up
Database Credentials

Skip to main content
9 tutorials
  • Dynamic Secrets: Database Secrets Engine
  • Database Root Credential Rotation
  • Database Static Roles and Credential Rotation
  • Couchbase Secrets Engine
  • Database Secrets Engine with MongoDB
  • IBM Db2 Credential Management
  • User Configurable Password Generation for Secret Engines
  • Database Secrets Engine for Microsoft SQL Server on AWS RDS
  • Database Secrets Engine for Microsoft SQL Server

  • Resources

  • Tutorial Library
  • Certifications
  • Community Forum
    (opens in new tab)
  • Support
    (opens in new tab)
  • GitHub
    (opens in new tab)
  1. Developer
  2. Vault
  3. Tutorials
  4. Database Credentials
  5. Database Secrets Engine for Microsoft SQL Server on AWS RDS

Database Secrets Engine for Microsoft SQL Server on AWS RDS

  • 30min

  • VaultVault

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, and Microsoft SQL Server 2019 on AWS RDS.

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

  • HCP or OSS Vault environment
  • jq installed
  • Publicly accessible Microsoft SQL Server RDS instance (Express edition or higher) using SQL Server authentication
  • Microsoft SQL Server command line tools installed

Note: Perform the tasks in this tutorial against a new, unused RDS instance.

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

Configure MSSQL

To streamline the Vault configuration, create environment variables required by the database secret engine for your MSSQL RDS instance.

  1. Export an environment variable for the RDS instance endpoint address.

    $ export SQL_ADDR=<actual-endpoint-address>
    

    You can retrieve the endpoint address from the Connectivity & security tab of the RDS instance.

    ui-aws-rds-endpoint

  2. Export an environment variable for the username created for the RDS instance.

    Note: The default username is admin. Replace admin if you created a custom username.

    $ export SQL_USERNAME=admin
    
  3. Export an environment variable for the password created for the RDS instance.

    $ export SQL_PASSWORD=<actual-password>
    
  4. Verify the security group attached to the RDS instance has an inbound rule for 0.0.0.0/0 on port 1433. ui-aws-security-group-sql

    Note: In production environments, you can create an inbound rule that permits the CIDR block for the HVN connected through a peering or transit gateway connection to your AWS account.

  5. 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
    
  6. Use sqlcmd to run the script to create the database and insert sample data.

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

    Example output:

    Changed database context to 'master'.
    Changed database context to 'myapp'.
    
    (1 rows affected)
    
  1. Verify you can read the data from the location table.

    $ sqlcmd -U $SQL_USERNAME -P $SQL_PASSWORD -S $SQL_ADDR -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.

  1. Launch the HCP Portal and login.

  2. Click Vault in the left navigation pane.

  3. In the Vault clusters pane, click vault-cluster.

  4. Under Cluster URLs, click Public Cluster URL. Public Cluster URL

  5. In a terminal, set the VAULT_ADDR environment variable to the copied address.

    $ export VAULT_ADDR=<Public_Cluster_URL>
    
  6. Return to the Overview page and click Generate token. Generate a Token

    Within a few moments, a new token will be generated.

  7. Copy the Admin Token. Generated Token

  8. Return to the terminal and set the VAULT_TOKEN environment variable.

    $ export VAULT_TOKEN=<token>
    
  9. 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.

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

  1. 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.

  2. 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
    
  3. 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.

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.

  1. Enable the database secrets engine at the database/ path.

    $ vault secrets enable database
    

    The database secrets engine is enabled.

  2. 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.

  3. 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.

  4. Create a custom revocation statement for Vault revoke MSSQL on RDS credentials.

    Note: Amazon RDS does not support using the sysadmin role, which is used by default during Vault's revocation process for MSSQL. Adding the revocation statement to the Vault role performs these steps as the SQL user configured in the Vault database config.

    $ tee revocation.sql <<EOF
    USE [myapp]
       IF EXISTS
         (SELECT name
          FROM sys.database_principals
          WHERE name = N'{{name}}')
       BEGIN
         DROP USER [{{name}}]
       END
    
       IF EXISTS
         (SELECT name
          FROM master.sys.server_principals
          WHERE name = N'{{name}}')
       BEGIN
         DROP LOGIN [{{name}}]
       END
    EOF
    
  5. Create the role named readonly that creates credentials with the readonly.sql script, and revokes credentials with the revocation.sql script.

    $ vault write database/roles/readonly \
          db_name=mssql \
          creation_statements=@readonly.sql \
          revocation_statements=@revocation.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.

  1. 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
    
  2. 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
    
  3. 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
    
  4. Create an API request payload containing the database role definition that creates credentials, and revokes credentials with the revocation.sql script.

    $ 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}}];"
        ],
          "revocation_statements": [
          "USE [myapp] IF EXISTS (SELECT name FROM sys.database_principals WHERE name = N'{{name}}') BEGIN DROP USER [{{name}}] END IF EXISTS (SELECT name FROM master.sys.server_principals WHERE name = N'{{name}}') BEGIN DROP LOGIN [{{name}}] END"
          ],
        "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. Adding the revocation statement to the Vault role performs these steps as the SQL user configured in the Vault database config.

  5. 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
    
  1. 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
    
  2. 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
    
  3. 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
    
  4. Create an API request payload containing the database role definition that creates credentials, and revokes credentials with the revocation.sql script.

    $ 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}}];"
        ],
          "revocation_statements": [
          "USE [myapp] IF EXISTS (SELECT name FROM sys.database_principals WHERE name = N'{{name}}') BEGIN DROP USER [{{name}}] END IF EXISTS (SELECT name FROM master.sys.server_principals WHERE name = N'{{name}}') BEGIN DROP LOGIN [{{name}}] END"
          ],
        "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. Adding the revocation statement to the Vault role performs these steps as the SQL user configured in the Vault database config.

  5. Create the readonly role.

    $ curl --header "X-Vault-Token: $VAULT_TOKEN" \
    --request POST --data @readonly-role.json \
    $VAULT_ADDR/v1/database/roles/readonly
    
  1. 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.

  2. Navigate to Secrets and click Enable new engine. database-secrets-ui-1.png

  3. From the Enable a Secrets Engine page Infra section, select Databases and click Next. database-secrets-ui-2.png

  4. Leave all fields with the default values, and click Enable Engine.

  5. Click Create connection to create a connection from Vault to the MSSQL RDS instance. database-secrets-ui-4.png

  6. Click the Database plugin pulldown menu and select MSSQL.

  7. 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.

  8. 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.

  9. Click Add role.

  10. In the Role name enter readonly.

  11. Click the Type of role pulldown menu and select Dynamic.

  12. 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}}];
    
  13. In the Revocation statements text box enter:

    USE [myapp]
    IF EXISTS
      (SELECT name
       FROM sys.database_principals
       WHERE name = N'{{name}}')
    BEGIN
      DROP USER [{{name}}]
    END
    
    IF EXISTS
      (SELECT name
       FROM master.sys.server_principals
       WHERE name = N'{{name}}')
    BEGIN
      DROP LOGIN [{{name}}]
    END
    
  14. Click Create role.

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

Test MSSQL access

(Persona: apps)

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

  1. 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.

  2. 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)
    
  3. 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 $SQL_ADDR -Q "USE [myapp]; SELECT street FROM location;"
    

    The output displays the sample data from the myapp database.

    street
    --------------------
    main
    
  4. Attempt to insert data into the table.

    $ sqlcmd -U $(echo $TEMP_CREDS | jq -r .username) \
       -P $(echo $TEMP_CREDS | jq -r .password) -S $SQL_ADDR \
       -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'.
    
  1. 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
    }
    
  2. 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 $SQL_ADDR -Q "USE [myapp]; SELECT street FROM location;"
    

    The output displays the sample data from the myapp database.

    Example output:

    street
    --------------------
    main
    
  3. 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 $SQL_ADDR \
       -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'.
    
  1. 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
    }
    
  2. 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 $SQL_ADDR -Q "USE [myapp]; SELECT street FROM location;"
    

    The output displays the sample data from the myapp database.

    Example output:

    street
    --------------------
    main
    
  3. 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 $SQL_ADDR \
       -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'.
    
  1. From the readonly role page, click Generate credentials. ui-vault-database-readonly-generate

  2. Make note of the Lease ID, you will need this in subsequent sections. ui-vault-database-lease-id

  3. Click the copy icon for the generated Username. ui-vault-database-copy-username

  4. Switch to the terminal you configured the SQL_ADDR environment variable from the Lab setup section.

  5. Export an environment variable for temporary credentials.

    $ export TEMP_USERNAME=<actual-username-from-vault>
    
  6. Return to the Vault UI and click the copy icon for the generated Password.

  7. Switch back to the terminal session and export an environment variable for the temporary password.

    $ export TEMP_PASSWORD=<actual-password-from-vault>
    
  8. 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 $SQL_ADDR -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)
    
  9. Attempt to insert data into the table.

    $ sqlcmd -U $(echo $TEMP_USERNAME) \
    -P $(echo $TEMP_PASSWORD) \
    -S $SQL_ADDR \
    -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.

  1. 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.

  2. Create a variable that stores the first lease ID.

    $ LEASE_ID=$(vault list -format=json sys/leases/lookup/database/creds/readonly | jq -r ".[0]")
    
  3. 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.

  4. Revoke the lease without waiting for its expiration.

    $ vault lease revoke database/creds/readonly/$LEASE_ID
    
    All revocation operations queued successfully!
    
  5. 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.

  6. 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.

  7. 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.

  1. 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"
    ]
    
  2. 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]")
    
  3. 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
    }
    
  4. 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
    
  5. 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": []
    }
    
  6. 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
    
  7. 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.

  8. 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.

  1. 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"
    ]
    
  2. 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]")
    
  3. 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
    }
    
  4. 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
    
  5. 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": []
    }
    
  6. Read new credentials from the readonly database role.

    $ curl --header "X-Vault-Token: $VAULT_TOKEN" \
       --request GET \
       $VAULT_ADDR/v1/database/creds/readonly | jq
    
  7. 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.

  8. 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.

  1. Return to the Vault UI and click Access, and then click Leases.

  2. Enter the lease ID from the Test MSSQL access section and click Lookup.

  3. Observe the TTL for the lease and then click Renew lease.

  4. Click Renew lease. ui-vault-database-lease-renewed

    The TTL is renewed to 3600 seconds (1 hour).

  5. Click Revoke lease and select Confirm. ui-vault-database-lease-revoked

    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 secret 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
  1. 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.

  2. 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.

  3. 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.

  1. Configure the database secrets engine with the mssql password policy.

    $ vault write database/config/mssql \
         password_policy="mssql"
    
  2. 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.

  1. 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
    
  2. 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
    
  3. 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...
    
  1. 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
    
  2. 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
    
  3. 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...
    
  1. Return to the Vault UI and click Secrets, and then click database.

  2. Click the Connections tab, and then click mssql.

  3. Click Edit configuration.

  4. Click the Use custom password policy toggle button.

  5. Type mssql in the text box and click Save. vault-ui-database-password-policy

  6. From the mssql configuration page, click readonly.

  7. Click Generate credentials.

  8. Click the view icon for the generated Password. ui-vault-database-password-view

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

Define a username template

(Persona: apps)

The database secret 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.

  1. 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.

  2. 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.

  3. 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.

  1. 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.

  2. 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.

  3. 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
    
  4. 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
    }
    
  1. 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.

  2. 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.

  3. 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
    
  4. 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
    }
    
  1. Click Back to return to the mssql configuration page.

  2. Click Edit configuration.

  3. Click the Username template toggle button.

  4. 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.

  5. Click Save.

  6. From the mssql configuration page, click readonly.

  7. Click Generate credentials.

  8. Click the view icon for the generated Username. ui-vault-database-username-view

    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:

  • Direct Application Integration
  • Vault Agent Caching

Help and Reference

  • Secrets Engines - Databases
  • Role API
  • Database Root Credential Rotation
  • Database Static Roles and Credential Rotation
 Previous
 Next

On this page

  1. Database Secrets Engine for Microsoft SQL Server on AWS RDS
  2. Challenge
  3. Solution
  4. Personas
  5. Prerequisites
  6. Scenario Introduction
  7. Lab setup
  8. Configure the database secrets engine
  9. Test MSSQL access
  10. Manage leases
  11. Implement a password policy
  12. Define a username template
  13. Next Steps
  14. Help and Reference
Give Feedback(opens in new tab)
  • Certifications
  • System Status
  • Terms of Use
  • Security
  • Privacy
  • Trademark Policy
  • Trade Controls
  • Give Feedback(opens in new tab)