Vault Database Secrets Engine - MSSQL minimum permission to create the dynamic credentials

Introduction

MSSQL is one of Vault's database secrets engine's supported plugins. This plugin generates database credentials for the MSSQL database dynamically depending on defined roles. This article aims at listing the minimal permissions  for creating a custom role to be associated with the user specified while configuring mssql db secrets engine in Vault.  We've encountered numerous customers with database team restrictions on providing more elevated securityadmin and processadmin built-in roles. So, in this section, we'll look at how we specified the minimal permission at the database level to do the same thing.

Solution

Assume we have a login called vaultroot and a database called testdb in Microsoft SQL Server 2017. And we generated the vaultroot login with the following commands:

# To create the login & enable it
USE [master]
GO
CREATE LOGIN [vaultroot] WITH PASSWORD=N'm+7KddeKMhU2yP9JgSBgHBVXIxzvGQn5Te+qvXGc7PA=', DEFAULT_DATABASE=[test], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [vaultroot] ENABLE
GO

Instead of providing vaultroot authority to the built-in roles securityadmin and processadmin, we will construct the custom role described below:

# Command to create Custom Server role i.e. vaultadminrole
USE [master]
GO
/****** Object:  ServerRole [vaultadminrole] UseFor: Vault MSSQL Secret engine    ******/
CREATE SERVER ROLE [vaultadminrole]
GO

Once a custom role, such as vaultadminrole, has been defined, we must provide it the ability to change any login using the following command:

# Command for adding grant to alter any login to custom role
USE [master]
GO
/****** Object:  ServerRole [vaultadminrole] UseFor: Grant access to alter any login   ******/
GRANT ALTER ANY LOGIN TO [vaultadminrole]
GO

Following that, we must add our user vaultroot to the vaultadminrole custom role.

# Command to add the vaultroot user to custom role
USE [master]
GO
/****** Object:  ServerRole [vaultadminrole] UseFor:adding member to it   ******/
ALTER SERVER ROLE [vaultadminrole] ADD MEMBER [vaultroot]
GO

Now we can configure the vault database secret engine for mssql plugin

root@vaults0:/vagrant/scripts# vault write database/config/my-mssql-database plugin_name="mssql-database-plugin" connection_url='sqlserver://{{username}}:{{password}}@ec2-54-169-82-54.ap-southeast-1.compute.amazonaws.com:1433?database=test' allowed_roles="*" username="vaultroot" password="MyStrongPassword123" disable_escaping="true"
 Success! Data written to: database/config/my-mssql-database

root@vaults0:/vagrant/scripts# echo "CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
> CREATE USER [{{name}}] FOR LOGIN [{{name}}];
> GRANT SELECT ON SCHEMA::dbo TO [{{name}}];" | tee mssql.sql
 #CREATE LOGIN [{{name}}] WITH PASSWORD = '{{password}}';
 #CREATE USER [{{name}}] FOR LOGIN [{{name}}];
 #GRANT SELECT ON SCHEMA::dbo TO [{{name}}];

root@vaults0:/vagrant/scripts# vault write database/roles/mssqlrole db_name=my-mssql-database creation_statements=@mssql.sql default_ttl="1h" max_ttl="24h"
 Success! Data written to: database/roles/mssqlrole

root@vaults0:/vagrant/scripts# vault read database/creds/mssqlrole
 #Key             Value
 #---             -----
 #lease_id        database/creds/mssqlrole/OfV3awDAFIgUeGZyErFF0PLj
 #lease_duration  1h
 #lease_renewable true
 #password        bPA16vKmwlufVza5-qdx
 #username        V_ROOT_1696582031

References:

• MSSQL Database Secret Engine