Microsoft SQL Dynamic Secrets

Once you have installed the DSV Engine, you can use DSV to create Dynamic Secrets. DSV currently supports contained MSSQL databases. DSV does not currently support traditional MSSQL databases.

Dynamic Secret Setup

Creating a Base Secret: in the CLI, create a base secret containing the credentials of the MSSQL account that will be responsible for creating new accounts on a given server. You must mark the secret as an MSSQL root secret by including the type options with a value of mssql. All fields in the data object are required.

Port is an integer and does not require quotations.

Example Base Secret:

Copy 
{
    "attributes": {    "type": "mssql"  },  "data": {    "database": "TestContainedDB",    "password": "yourpassword",    "port": 1433,    "server": "localhost",    "username": "yourusername"  }}

Creating a new dynamic secret: the dynamic secret will be linked to the root secret. The root secret for MSSQL dynamic secrets must be a standard SQL server login that sits outside of the contained database. Use the following format:

Dynamic Secret Example

Copy 
{
 "attributes": {
    "grantPermissions": {
      "what": "SELECT",
      "where": "exampletable"
    },
    "linkConfig": {
      "linkType": "dynamic",
      "linkedSecret": "mssql:base2"
    },
    "pool": "pool1",
    "ttl": 900,
    "userPrefix": "test"
  }
}

Dynamic Secret Guide

  • grantPermissions: specifies the permissions assigned by MSSQL to the new user account.

    • what: defines the database access permissions the user will have in MSSQL. Permissions may include CONNECT, CREATE, SELECT, or other SQL statements.
    • where: defines the location within the database for permissions to apply.

  • linkType: is always dynamic for dynamic secrets.

  • linkedSecret: the path of the root secret.

  • pool: designates the Engine pool that DSV will use to generate dynamic secrets.

  • ttl: specifies the number of seconds for which the new account will exist before the engine automatically deletes it.

    ttl must be set at or above 900.

  • userPrefix: an optional key whose value is a string prepended to all MSSQL account usernames created from the dynamic secret.

  • data: this field remains blank for dynamic secrets.

Sending a MSSQL task to an engine

Read the MSSQL dynamic secret. A randomly chosen engine in the engine pool should receive the MSSQL task and perform it. The engine attempts to create an MSSQL account and reports back success or failure. Upon success, the user also receives the new working credentials. As long as there is at least one running engine in a given pool, an engine will receive an MSSQL account revocation task and delete the account once its TTL expires.

Third Party Reference

For contained server configuration details, refer to MSSQL Database Documentation