Vault for SQL Server – Dynamic Credentials

Security and automation are two of my favorite topics. HashiCorp designed Vault from the ground up to be API-driven and highly automation friendly. Because of this, I believe it is a valuable tool for SQL Server DBAs and developers. This is the first post in a series that will cover how you can use HashiCorp Vault with SQL Server.

Vault has two primary use cases: Secrets Management and Data Protection. In this first installment of using Vault with Microsoft SQL Server, we’re going to focus on one of my favorite aspects of Secrets Management – Dynamic Credentials.

Getting Started with Vault

First, you’ll need to download and install Vault. Install manually or through your favorite package manager. For example, use Chocolatey for Windows as shown:

choco install vault

Start a simple dev server instance of Vault with the following command:

vault server -dev

This is a quick way to run an instance of Vault. However, this instance is ephemeral and data will not be persisted.

After starting the Vault server, you will see output similar to this:

WARNING! dev mode is enabled! In this mode, Vault runs entirely in-memory
and starts unsealed with a single unseal key. The root token is already
authenticated to the CLI, so you can immediately begin using Vault.

You may need to set the following environment variable:

PowerShell:
    $env:VAULT_ADDR="http://127.0.0.1:8200"
cmd.exe:
    set VAULT_ADDR=http://127.0.0.1:8200

The unseal key and root token are displayed below in case you want to
seal/unseal the Vault or re-authenticate.

Unseal Key: 3uFJ87adick+pDlO/nuzwDArV8/CryhenQ8ABnGlBRc=
Root Token: s.EwumnZ1MwZHSefbxbWZph8u4

Development mode should NOT be used in production installations!

Pay particular attention to the Unseal Key and Root Token. You should absolutely never share your unseal keys and root token in a production environment. I will cover the concepts of unsealing and token management in a future post.

For now, open a new PowerShell terminal window (leave the Vault server running in the first terminal) and set the environment variable as recommended:

$env:VAULT_ADDR="http://127.0.0.1:8200"

Logging in to the Vault client requires the root token. Replace the token value below with the one from your dev server to log into Vault:

vault login s.EwumnZ1MwZHSefbxbWZph8u4

Putting Vault to Work with SQL Server

About Secrets Engines

There are many Secrets Engines available in Vault that act as components to talk to other services and systems. These include Active Directory, cloud providers, key/value stores, and database engines such as Microsoft SQL Server. Secrets Engines have one or more configurations. A configuration tells Vault which storage engine plugin to use and how to connect to SQL Server. A username and password need to be provided for a login. In addition, this login requires privileges to create/drop logins and manage processes, create/drop database users, and grant database permissions.

First, I will create the login on my SQL Server:

USE master;
GO

CREATE LOGIN [vault-VaultSample] WITH PASSWORD = N'CmZv4@H4U4AP';

ALTER SERVER ROLE [securityadmin] ADD MEMBER [vault-VaultSample];
ALTER SERVER ROLE [processadmin] ADD MEMBER [vault-VaultSample];
GRANT ALTER ANY LOGIN TO [vault-VaultSample];
GO

Enabling the Secrets Engine

Next, enable the Vault database secrets engine from the terminal where you authenticated to Vault:

vault secrets enable database

Finally, we write the configuration to Vault. Replace the server name in connection_url as well as the password to match your environment:

vault write database/config/mssql-VaultSample `
    plugin_name=mssql-database-plugin `
    connection_url='sqlserver://{{username}}:{{password}}@rhel7_docker02:1433' `
    allowed_roles="VaultSample-role" `
    username="vault-VaultSample" `
    password="CmZv4@H4U4AP"

Note the structure of the path we wrote the config to – database/config/mssql-VaultSample. In this path, the first part is the endpoint for the database storage engine. Next it tells Vault that it is a config definition for the connection named mssql-VaultSample.

We just provided a password in plain text for a highly privileged SQL login. Thus, you should rotate the password immediately with the following command:

vault write -force database/rotate-root/mssql-VaultSample

NOTE: The password for the vault-VaultSample login is known only to Vault and cannot be retrieved. Therefore, it is very important to use a dedicated login for this purpose.

Vault Database Roles

A role maps a database connection name in Vault to customizable SQL statements that create and drop SQL Server logins and database users. The role can include custom SQL to perform additional steps. For example, granting role memberships or any other custom business logic that you might want to include. In this example, I’m going to define a role that will be used to create a server login and database user with db_datareader role membership in the VaultSample database.

Create the database and database role in SQL Server:

CREATE DATABASE VaultSample;
GO

USE VaultSample;
GO

CREATE USER [vault-VaultSample] FOR LOGIN [vault-VaultSample]; -- Add a database user for the Vault root login
ALTER ROLE [db_accessadmin] ADD MEMBER [vault-VaultSample];
ALTER ROLE [db_securityadmin] ADD MEMBER [vault-VaultSample];
CREATE ROLE [vault_datareader]; -- Create a user-defined database role because only db_owner members can alter fixed database roles
ALTER ROLE [db_datareader] ADD MEMBER [vault_datareader]; -- Add user-defined role to fixed db_datareader role
GRANT ALTER ANY USER TO [vault-VaultSample]; -- Required for Vault to drop database users when the TTL expires
GO

USE master;
GO

ALTER LOGIN [vault-VaultSample] WITH DEFAULT_DATABASE = [VaultSample]; -- Change the default database for the root login
GO

Create the role in Vault with the code below. Vault will automatically replace the values for {{name}} and {{password}}:

vault write database/roles/VaultSample-role `
    db_name=mssql-VaultSample `
    creation_statements="CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';`
        CREATE USER [{{name}}] FOR LOGIN [{{name}}];`
        ALTER ROLE [vault_datareader] ADD MEMBER [{{name}}];" `
    revocation_statements="DROP USER [{{name}}]; `
        DROP LOGIN [{{name}}];" `
    default_ttl="1m" `
    max_ttl="1h"

It’s worth mentioning that the example role in HashiCorp’s documentation only includes a creation_statement and not a revocation_statement. However, if the login you configured the plugin to use isn’t a sysadmin member (and it shouldn’t be), default revocation will fail unless you explicitly provide a revocation_statement as shown above. Instead, the login will only be disabled (not dropped), and you will see errors such as the following in the Vault log. This could become a big problem on a busy system.

2019-09-17T15:50:14.177-0700 [ERROR] expiration: failed to revoke lease: lease_id=database/creds/VaultSample-role/yCJ3mSwI8lw0DyPo182Cfz2A error="failed to revoke entry: resp: (*logical.Response)(nil) err: mssql: Logins other than the current user can only be seen by members of the sysadmin role."

Dynamic SQL Server Credentials

It’s time to see where Vault really shines now that configuration and roles have been set up. Dynamic database credentials allow your users and applications to request just-in-time credentials. In addition, these requests can be audited and will automatically expire after a defined TTL. This is an absolute game-changer for managing access to your databases. For example, instead of application passwords that don’t get rotated often enough (or ever) because it’s seen as too much of a hassle, or a sysadmin login being used for routine activities, Vault can provide these credentials on an as-needed basis and automatically clean them up.

I’m going to request a new credential from Vault with the following command:

vault read database/creds/VaultSample-role

The new login and user are visible in SSMS:

I can now take the username and password from the output and use it to make a new connection to my SQL Server instance.

TTL and Revocation

You may have noticed that I set a very short default_ttl in the role definition (lease_duration in the vault read output). This was done simply to speed the process along for demonstration purposes. As a result, one minute after requesting the credential, it is no longer visible in SSMS. Vault has automatically dropped the user and login and they’re no longer present in SQL Server:

2019-09-17T16:36:45.569-0700 [INFO]  expiration: revoked lease: lease_id=database/creds/VaultSample-role/hAgaRNVr2y9Nh2a6yYgKTVPP

The following are just a few examples of how dynamic credentials could be leveraged in your environment:

• Integrate the Vault client into your custom applications and scripts to request database credentials at the time the database connection is established and renew the lease periodically while the application is running. If these credentials were ever compromised, they would be useless after the TTL period expired.
• Have your DBAs use Vault to request a temporary set of credentials when they truly need sysadmin privileges. This can help prevent accidents that can occur when people perform routine operations while logged in with highly privileged accounts.
• Roles allow for consistent self-service creation of database credentials without DBA intervention.
• Eliminate sharing of database credentials between applications and users.

This has been a basic introduction to how Vault can manage database credentials securely, but it is a very robust tool with many more capabilities. Keep checking back for more posts in this series where I’ll cover topics such as Vault permissions, connecting to remote Vault servers, renewing leases, backend storage options, high availability, and auto-unseal. If you want to use Vault to manage your SQL Server credentials at your company and need some assistance, SqlCS is here to help.