Azure SQL Database

​

How Azure SQL Database Authentication Works

1. Master Database: Contains server-level logins that can authenticate to the Azure SQL Database server
2. User Databases: Individual databases that contain database users mapped to server logins

1. Create Login in Master Database: Creates a server-level login with the specified password
2. Create User in Target Database: Creates a database user mapped to the login and grants the necessary permissions

​

Prerequisite

• loginmanager role in the master database (to create logins)
• db_owner role in the target database (to create users and grant permissions)

​

Set up Dynamic Secrets with Azure SQL Database

1

Open Secret Overview Dashboard

Open the Secret Overview dashboard and select the environment in which you would like to add a dynamic secret.

2

Click on the 'Add Dynamic Secret' button

3

Select `Azure SQL Database`

4

Provide the inputs for dynamic secret parameters

​

Secret Name

string

required

Name by which you want the secret to be referenced

​

Default TTL

string

required

Default time-to-live for a generated secret (it is possible to modify this value after a secret is generated)

​

Max TTL

string

required

Maximum time-to-live for a generated secret

​

Metadata

list

required

List of key/value metadata pairs

​

Host

string

required

Azure SQL Database server hostname (e.g., myserver.database.windows.net)

​

Port

number

required

Database port (typically 1433 for Azure SQL Database)

​

User

string

required

Username that will be used to create dynamic secrets (must have loginmanager role in master and db_owner in target database)

​

Password

string

required

Password that will be used to create dynamic secrets

​

Database

string

required

Name of the target database where users will be created and granted permissions

​

Encrypt Connection (SSL)

boolean

Enable SSL encryption for the database connection (recommended for Azure SQL Database)

​

CA(SSL)

string

SSL certificate authority certificate. For Azure SQL Database, this is typically not required as Azure manages the certificates.

5

Configure SQL Statements

Azure SQL Database dynamic secrets use predefined SQL statements that follow Azure’s security best practices:

​

Master Creation Statement

string

SQL statement executed in the master database to create a server-level login. This login allows authentication to the Azure SQL Database server.

​

Creation Statement

string

SQL statement executed in the target database to create a database user and grant permissions. The user is mapped to the login created in the master database.

​

Revocation Statement

string

SQL statements executed when a lease expires or is manually revoked. The system intelligently routes DROP USER commands to the target database and DROP LOGIN commands to the master database for proper cleanup.

​

Username Template

string

default:"{{randomUsername}}"

Specifies a template for generating usernames. This field allows customization of how usernames are automatically created.Allowed template variables are:

• {{randomUsername}}: Random username string
• {{unixTimestamp}}: Current Unix timestamp
• {{identity.name}}: Name of the identity that is generating the secret
• {{random N}}: Random string of N characters

Allowed template functions are:

• truncate: Truncates a string to a specified length
• replace: Replaces a substring with another value

Examples:

Copy

Ask AI

{{randomUsername}}                              // 3POnzeFyK9gW2nioK0q2gMjr6CZqsRiX
{{unixTimestamp}}                               // 17490641580
{{identity.name}}                               // testuser
{{random-5}}                                    // x9k2m
{{truncate identity.name 4}}                    // test
{{replace identity.name 'user' 'replace'}}      // testreplace

6

Click 'Submit'

After submitting the form, you will see a dynamic secret created in the dashboard.

If this step fails, ensure your user has the proper permissions in both the master database (loginmanager role) and target database (db_owner role).

7

Generate dynamic secrets

Once you’ve successfully configured the dynamic secret, you’re ready to generate on-demand credentials. To do this, simply click on the ‘Generate’ button which appears when hovering over the dynamic secret item. Alternatively, you can initiate the creation of a new lease by selecting ‘New Lease’ from the dynamic secret lease list section.When generating these secrets, it’s important to specify a Time-to-Live (TTL) duration. This will dictate how long the credentials are valid for.

Ensure that the TTL for the lease falls within the maximum TTL defined when configuring the dynamic secret.

Once you click the Submit button, a new secret lease will be generated and the credentials for it will be shown to you.

​

Audit or Revoke Leases

1. Drops the user from the target database
2. Drops the login from the master database

​

Renew Leases