Create a dynamic MSSQL secret

A shared database password lives in every app that uses it, and changing it means changing it everywhere at once. A dynamic secret replaces it with a login of its own for each machine: your Burrow creates it in MSSQL the moment a machine reads the secret, with only the rights you choose, and removes it when its time is up.

What you need#

• An MSSQL database your Burrow can reach, and a role in your Burrow that can write secrets.

• An admin login there that can create logins and database users, ideally one made for your Burrow.

• Machines with access to the environment the secret goes in. This guide uses api's prod.

Open a new dynamic secret#

Open the environment in Projects, select the + at the top right of the secrets table and choose Dynamic. The dialog has three steps: Connection, Lease & credential, and Review.

Connect to your database#

Name the secret, such as MSSQL. Machines read it as api/prod/MSSQL, like any other secret. Set Provider to MSSQL: each machine's login is then a server login with a user in your database. The other databases work the same way, each with its own kind of login, and SSH user, below the line, issues SSH certificates instead.

Then fill in where the database is and how your Burrow signs in to it:

• Host and Port: where your Burrow reaches the database, 1433 by default.

• Database: the database each login gets its user in. It's also the login's default database.

• Admin user and Admin password: the login your Burrow uses to create and remove each machine's login. It never leaves your Burrow, and machines never see it.

Choose how the connection is protected:

With either checking option, a CA certificate field appears. If your database's certificate comes from your own CA, paste that CA's certificate there. A managed database's certificate is trusted already, so leave it empty.

Select Test connection. Your Burrow signs in with the admin login and checks it can create logins, by making a switched-off login and its user and dropping both again. Each machine's own login is made when it first reads the secret. Select Continue.

Shape each password#

Every login gets its own random password. Under Password, set its length, from 8 to 64, which of A to Z, a to z, 0 to 9 and symbols it uses, and whether to leave out characters that look alike, such as 0 and O. Or choose UUID. The example shows the shape; each login's real password is made when it's issued.

Decide what each login may do#

The grants are T-SQL, run in the secret's database on each login's database user as it's created, with {{name}} standing for that user. Without any, a login can sign in but can't touch your data. Select Build to write them from a list:

• Access: Read-only reads rows, Read / write also inserts, updates and deletes them, and Full can change the schema too.

• Privileges: tick them one by one instead, under Data (reading and changing rows, running procedures, foreign keys) and Schema changes (creating tables, views and procedures, and altering the schema). Any mix that isn't a preset is marked Custom.

• Scope: the schema the grants apply to, dbo unless you change it. Rights on a schema cover every object in it, including ones created later.

The statements show as you choose. Use these grants puts them in the grants box. Read / write on dbo, with Run procedures ticked for an app that calls stored procedures, writes this one:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::[dbo] TO {{name}};

Then select Test the grants. Your Burrow makes a switched-off login, applies the grants to its user and drops both again, so a typo or a missing schema shows up now rather than when a machine reads.

Set the lease policy#

The lease policy decides how each login lives. A new secret starts with sensible defaults, so you may not need to change any of them:

MSSQL can't expire a login at a set time, so there's no Database expiry setting here: your Burrow drops each login when its lease ends.

Review and create#

Select Continue to see everything on one page, then Create dynamic secret. The admin password and CA certificate are saved with the secret, encrypted like any other value.

Read it from a machine#

A machine reads a dynamic secret the way it reads a structured one, and gets its own login as fields: host, port, database, sslmode, sslrootcert when you gave a CA, username and password. The username starts with rk_ and names the machine and the secret, so you can pick it out in MSSQL's own list of logins.

ratel run sets one variable per field, so MSSQL gives MSSQL_HOST, MSSQL_USERNAME, MSSQL_PASSWORD and so on:

ratel run --env api/prod -- ./server

A machine keeps its login across reads until it ends, and the secret's row counts the logins out. With ratel run --watch, the command restarts when its login gets a new password; a renewal keeps the password, so it restarts nothing.

Change it later#

Choose Configure from the secret's row menu. Each tab saves on its own:

The connection and grants are kept as the secret's value, so Version History lists every change, and reverting to an earlier one replaces the logins like any other change.

See and revoke logins#

Choose Logins from the row menu to see every login out: the machine holding it, its username, its state, and when it was issued and ends.

The bin at the end of a row revokes that login now. Your Burrow switches it off so it can't sign in, ends its open sessions where the admin login may, hands any schema it created to dbo so your data stays, and drops its database user and login. Its machine gets a new login on its next read.

If a login isn't issued#

Every option in one place: Dynamic secrets in the documentation.