☁️ Azure SQL Elastic Job Step Terraform Module

The T-SQL, the target group and the credential — where an Elastic Job stops being a container and starts being work — targeting hashicorp/azurerm ~> 4.0.

🧩 Overview

⚡ Creates one job step — the only place in the Elastic Jobs family that carries a script.
🔴 The scope is the target group multiplied by the credential. The group decides which databases, the credential decides what rights — and neither is visible from this resource.
🔴 Three credentials do three different jobs in this family: a target group entry's credential enumerates, this step's credential executes, and an output target's credential writes.
🔴 The SQL script is stored in Terraform state in plain form. Anything embedded in it — a password, a key — is a secret in state.
🔴 The retry defaults compound: ten attempts, a ×2 backoff, and a twelve-hour per-execution timeout. maximum_retry_wait_seconds computes what they add up to.
⚠️ sql_script and job_target_group_id both update in place, so a step's reach can change with no replacement in the plan.
⚠️ job_step_index is one-based and not force-new — steps reorder in place, and two steps can share an index without either resource noticing.
🏷️ No tags and no location; the job agent is the only taggable resource in this family.

💡 Why it matters: every other module in this family is scaffolding. This one runs SQL on other people's databases.

❤️ Support this project

If this module saved you time:

⭐ Star the repository — it helps other people find it.
💼 Connect on LinkedIn — linkedin.com/in/microsoftexpert
☕ Buy me a coffee — buymeacoffee.com/microsoftexpert

🗺️ Where this fits in the family

flowchart TB
  JOB["terraform-azurerm-mssql-job"]
  TG["terraform-azurerm-mssql-job-target-group, WHICH databases"]
  CRED["terraform-azurerm-mssql-job-credential, WHAT rights"]
  THIS["terraform-azurerm-mssql-job-step"]
  TARGETS["every database the target group resolves to"]
  OUT["an output database, a SECOND data path"]
  SCHED["terraform-azurerm-mssql-job-schedule, WHEN"]

  JOB -->|"job_id, force-new"| THIS
  TG -->|"job_target_group_id, the SCOPE"| THIS
  CRED -->|"job_credential_id, EXECUTES the T-SQL"| THIS
  THIS -->|"sql_script runs unchanged on each"| TARGETS
  THIS -.->|"output_target writes the result set here"| OUT
  SCHED -.->|"fires the job that owns this step"| JOB

  classDef me fill:#0078D4,stroke:#004578,stroke-width:2px,color:#ffffff
  classDef target fill:#004578,stroke:#00243d,stroke-width:2px,color:#ffffff
  classDef sib fill:#eef3f8,stroke:#9db4c9,color:#1a2733

  class THIS me
  class TARGETS target
  class JOB,TG,CRED,OUT,SCHED sib

Three references meet here, and each brings something different: the job gives identity, the target group gives scope, the credential gives rights. The dotted edge to the output database is the one most easily missed — with an output target configured, the step reads from every target and writes into one specific database that may belong to someone else entirely.

🧬 What this module builds

flowchart TB
  JID["job_id, force-new"]
  NM["name, force-new"]
  IDX["job_step_index, one-based, NOT force-new"]
  TGID["job_target_group_id, the SCOPE, NOT force-new"]
  SQL["sql_script, runs unchanged on every target, STORED IN STATE"]
  CRED["job_credential_id, EXECUTES"]
  RETRY["retry knobs: 10 attempts, x2 backoff, 1s to 120s, 12h timeout"]
  OUT["output_target, a SECOND data path, with a THIRD credential"]
  THIS["azurerm_mssql_job_step.this"]
  OSCOPE["the_scope_is_the_target_group_multiplied_by_the_credential"]
  OTHREE["three_credentials_do_three_different_jobs_in_this_family"]
  OSTATE["the_sql_script_is_stored_in_state_in_plain_form"]
  OWAIT["maximum_retry_wait_seconds"]
  OOUT["an_output_target_is_a_second_data_path"]

  JID --> THIS
  NM --> THIS
  IDX --> THIS
  TGID --> THIS
  SQL --> THIS
  CRED --> THIS
  RETRY --> THIS
  OUT --> THIS
  THIS --> OSCOPE
  THIS --> OTHREE
  THIS --> OSTATE
  THIS --> OWAIT
  THIS --> OOUT

  classDef me fill:#0078D4,stroke:#004578,stroke-width:2px,color:#ffffff
  classDef target fill:#004578,stroke:#00243d,stroke-width:2px,color:#ffffff
  classDef sib fill:#eef3f8,stroke:#9db4c9,color:#1a2733

  class THIS me
  class OSCOPE target
  class JID,NM,IDX,TGID,SQL,CRED,RETRY,OUT,OTHREE,OSTATE,OWAIT,OOUT sib

Resource inventory

Resource	Count	Notes
`azurerm_mssql_job_step`	1 (`this`)	one step; optionally one output target

ℹ️ The Resource ID is /subscriptions/…/jobs/<job>/steps/<name>.

✅ Provider / Versions

Requirement	Value
Terraform	`>= 1.12.0`
`hashicorp/azurerm`	`~> 4.0`
Provider block	None in this module — the caller configures the provider, its authentication, and the mandatory `features {}` block
Resources created	1

Schema notes that bite

🔴 maximum_retry_interval_seconds must be STRICTLY greater than initial_retry_interval_seconds — equal is refused. The provider enforces it in a CustomizeDiff, which needs a configured provider and a round trip; this module refuses it from the configuration alone, offline.
🔴 The defaults compound: 10 attempts, ×2.0 backoff, 1s initial, 120s ceiling, 43200s (twelve-hour) execution timeout.
⚠️ job_step_index is IntAtLeast(1) — no step zero — and is not force-new.
⚠️ job_target_group_id and sql_script update in place.
⚠️ output_target is a list with MaxItems 1; schema_name defaults to dbo.
⚠️ Nothing parses the script. A syntax error is a failed job execution, not a failed apply.
✅ Force-new: name, job_id.
✅ A requires-import guard is present.
⚠️ The timeouts block is 30/5/30/30 and is NOT timeout_seconds — one bounds the API call, the other bounds the script.
No tags, no location.

🔑 Required Azure RBAC Roles / Permissions

Least-privilege, at the smallest scope that works:

Contributor on the Azure SQL server hosting the agent, or a custom role with Microsoft.Sql/servers/jobAgents/jobs/steps/*.
🔴 The permission to edit a step is the permission to change what a job runs and where. Both the script and the target group update in place.
🔴 No Azure role bounds what the script can do — that is the credential's SQL-side permission set, which nothing in Terraform can see.
⚠️ An output target in another resource group or subscription needs its own grant for the write credential.

Azure Prerequisites

An existing Elastic Job, target group and, in practice, credential.
🔴 The credential's principal on every target database, with the rights the script needs.
🔴 The output database, schema and table, if an output target is configured.
The caller configures the provider, its authentication, and features {}.

📁 Module Structure

terraform-azurerm-mssql-job-step/
├── providers.tf   # required_version + the pinned azurerm; no provider block
├── variables.tf   # 13 inputs, 15 validations, the retry interactions documented
├── main.tf        # the keystone `this`; output_target rendered only when supplied
├── outputs.tf     # the id first, then the computed retry cost and credential set
├── README.md      # this file
├── SCOPE.md       # the cross-module contract
├── LICENSE        # MIT
└── .gitignore

⚙️ Quick Start

module "rebuild_step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "rebuild-indexes"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  job_credential_id   = module.job_credential.id

  sql_script = "EXEC sp_rebuild_indexes"
}

⚠️ The caller configures the provider, its authentication and the mandatory features {} block. 🔴 This runs on every database the target group resolves to, with the rights of that credential. 🔴 The retry defaults apply: ten attempts and a twelve-hour execution timeout — see example 5.

🔌 Cross-Module Contract

Consumes

Input	Type	Typical source
`job_id`	`string`	`terraform-azurerm-mssql-job` — its `id`
`job_target_group_id`	`string`	`terraform-azurerm-mssql-job-target-group` — its `id`
`job_credential_id`	`string`	`terraform-azurerm-mssql-job-credential` — its `id`
`output_target.mssql_database_id`	`string`	`terraform-azurerm-mssql-database` — its `id`

Emits

Output	Description
`id`	Resource ID, first
`maximum_retry_wait_seconds`	Computed — what the four retry knobs add up to
`credentials_in_use`	Computed — the distinct principals this step references
`writes_results_to_a_table`	Computed — is there a second data path?
`the_sql_script_is_stored_in_state_in_plain_form`	Constant `true`

📚 Example Library

1 · The smallest real call

module "step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "rebuild-indexes"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  sql_script          = "EXEC sp_rebuild_indexes"
}

⚠️ No credential is supplied here, which the provider permits and Azure generally does not — the argument is Optional in the schema, which is not the same as optional in practice. 🔴 Index 1, not 0. There is no step zero.

2 · 🔴 The three credentials

# 1. ENUMERATES the target -- on the target group entry.
module "targets" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-target-group.git?ref=v1.0.0"

  name         = "prod"
  job_agent_id = module.job_agent.id

  job_targets = {
    server = {
      server_name       = "sqlsrv-prod"
      job_credential_id = var.enumeration_credential_id
    }
  }
}

module "step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "rebuild-indexes"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id

  # 2. EXECUTES the T-SQL.
  job_credential_id = var.execution_credential_id

  sql_script = "EXEC sp_rebuild_indexes"

  output_target = {
    mssql_database_id = var.results_database_id
    table_name        = "rebuild_log"

    # 3. WRITES the results.
    job_credential_id = var.write_credential_id
  }
}

🔴 Three roles, three arguments, three resources. They are often the same principal, which is exactly why the distinction goes unnoticed until a server-scoped target fails to enumerate or an output write is refused. 💡 credentials_in_use lists the distinct ones this step references, for access review.

3 · 🔴 The script runs everywhere, unchanged

sql_script = <<-SQL
  IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'audit_log')
    CREATE TABLE dbo.audit_log (id INT IDENTITY, run_at DATETIME2 DEFAULT SYSUTCDATETIME());
  INSERT INTO dbo.audit_log DEFAULT VALUES;
SQL

🔴 This executes on every database the target group resolves to — and if that group contains a whole-server target, that is every database on the server, including ones created later. 🔴 Write it to be idempotent. A failing execution is retried ten times by default, so a script that is not safe to run twice will be run twice. The guard clause above is the pattern. ⚠️ Nothing parses it. A syntax error surfaces as a failed job execution days later, not as a failed apply.

4 · 🔴 The script is in state

# WRONG -- this password is now in the state file, in plain text.
sql_script = "EXEC sp_configure_link @password = 'S3cret!'"

# BETTER -- read it from somewhere the script can reach at run time.
sql_script = "EXEC sp_configure_link @password = (SELECT secret FROM dbo.vault WHERE name = 'link')"

🔴 sql_script is an ordinary string argument. It appears in plan output and in the state file exactly as written, and no marking on this resource changes that. ⚠️ This library's standing position applies with more force here: sensitive = true would only redact plan output, and this argument does not even carry that. The control that matters is not embedding the secret.

5 · 🔴 What the retry defaults actually cost

module "step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "rebuild-indexes"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  sql_script          = "EXEC sp_rebuild_indexes"

  # All four of these are the PROVIDER's defaults, shown for clarity:
  retry_attempts                    = 10
  initial_retry_interval_seconds    = 1
  retry_interval_backoff_multiplier = 2.0
  maximum_retry_interval_seconds    = 120
  timeout_seconds                   = 43200 # TWELVE HOURS
}

output "worst_case_waiting" {
  value = module.step.maximum_retry_wait_seconds # 487
}

🔴 487 seconds of waiting, plus up to ten twelve-hour executions. The intervals are 1, 2, 4, 8, 16, 32, 64, then 120, 120, 120 — the ceiling caps the doubling. 🔴 A step failing for a structural reason — a missing table, a right it does not have — retries all ten times before anything reports it. Lower retry_attempts where failure is not transient. 💡 maximum_retry_wait_seconds computes this from your actual settings, because four interacting arguments state their sum nowhere.

6 · Tighter retries for a non-transient step

module "step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "validate-schema"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  sql_script          = "EXEC sp_validate_schema"

  retry_attempts                    = 2
  initial_retry_interval_seconds    = 5
  retry_interval_backoff_multiplier = 1 # constant, not growing
  maximum_retry_interval_seconds    = 10
  timeout_seconds                   = 300 # five minutes
}

ℹ️ A multiplier of exactly 1 gives a constant interval — the closest this resource offers to disabling the backoff. Below 1 is refused. ⚠️ retry_attempts cannot be zero. One is the minimum, so there is no "do not retry". 🔴 maximum_retry_interval_seconds must be strictly greater than the initial — 10 and 5 here. Equal is refused, not just smaller.

7 · 🔴 The output target is a second data path

module "step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "collect-sizes"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  job_credential_id   = var.execution_credential_id

  sql_script = "SELECT DB_NAME() AS db, SUM(size) * 8 AS kb FROM sys.database_files GROUP BY DB_NAME()"

  output_target = {
    mssql_database_id = var.central_reporting_db_id
    table_name        = "database_sizes"
    schema_name       = "collected"
    job_credential_id = var.write_credential_id
  }
}

🔴 The step reads from every target and writes into ONE database — which may be on another server, in another resource group, or owned by another team. Whatever the script selects lands there in full. ⚠️ The table is not created by this module, and nothing checks it exists or that the credential may write to it. A misconfigured output target is a failed job execution, not a failed apply. ℹ️ schema_name defaults to dbo. output_target_table emits the schema-qualified name.

8 · Ordering steps

module "step_one" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "prepare"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  sql_script          = "EXEC sp_prepare"
}

module "step_two" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "rebuild"
  job_id              = module.nightly.id
  job_step_index      = 2
  job_target_group_id = module.targets.id
  sql_script          = "EXEC sp_rebuild_indexes"
}

⚠️ Steps run in index order and a failure stops the ones after it — the index is control flow, not a label. 🔴 Nothing stops two steps sharing an index. Each step is a separate Terraform resource, so neither this module nor the provider can see the collision; Azure resolves it, and not necessarily as intended. ℹ️ The index is not force-new, so reordering is an in-place edit.

9 · 🔴 Scope and rights can change without a replacement

# Both of these are IN-PLACE updates. Neither shows as a replacement.
job_target_group_id = var.much_wider_target_group_id   # the step now runs on far more
sql_script          = "DROP TABLE dbo.everything"     # ...and does something else

🔴 A step's reach and behaviour are both editable in place. A plan showing an in-place update to this resource can represent a complete change in what a job does and where. 💡 Read the target group's job_targets_covering_a_whole_server before approving a change to job_target_group_id. ℹ️ fields_that_can_change_after_creation lists all ten, which is most of the resource.

10 · What the module refuses

# Each of these fails at `terraform validate`, before any Azure call:

job_id              = module.job_agent.id   # the AGENT's id, one level up
job_target_group_id = module.job_agent.id   # not a target group id
job_credential_id   = module.job_agent.id   # not a credential id
name                = "  "                  # empty
sql_script          = "   "                 # empty
job_step_index      = 0                     # one-based
job_step_index      = 1.5                   # whole numbers

initial_retry_interval_seconds = 0          # range is 1..2147483
retry_attempts                 = 0          # minimum is 1
retry_interval_backoff_multiplier = 0.5     # minimum is 1

initial_retry_interval_seconds = 60
maximum_retry_interval_seconds = 60         # must be STRICTLY greater

output_target = { mssql_database_id = var.server_id, table_name = "t" }   # a SERVER id
output_target = { mssql_database_id = var.db_id, table_name = "  " }      # empty table

✅ A multiplier of exactly 1 is accepted — a constant interval. ✅ The range boundaries pass: 1 and 2147483 are both legal.

11 · Reviewing a step before approving it

output "step_review" {
  value = {
    runs_on        = module.step.job_target_group_id
    as             = module.step.job_credential_id
    all_principals = module.step.credentials_in_use
    writes_out     = module.step.writes_results_to_a_table
    writes_to      = module.step.output_target_table
    retry_wait     = module.step.maximum_retry_wait_seconds
    exec_timeout   = module.step.timeout_seconds
    script_in_state = module.step.the_sql_script_is_stored_in_state_in_plain_form
  }
}

🔴 Three of these answer questions the plan does not: how long the retries will take, which principals are involved, and whether there is a second data path out. 💡 runs_on is only half the scope — follow it to the target group's own outputs for the rest.

12 · 🏗️ End-to-end composition

module "job_agent" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-agent.git?ref=v1.0.0"

  name        = "jobs"
  database_id = var.control_database_id
  location    = "eastus"
}

module "job_credential" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-credential.git?ref=v1.0.0"

  name                = "jobuser"
  job_agent_id        = module.job_agent.id
  username            = "jobuser"
  password_wo         = var.job_password
  password_wo_version = 1
}

module "targets" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-target-group.git?ref=v1.0.0"

  name         = "prod-databases"
  job_agent_id = module.job_agent.id

  job_targets = {
    appdb = {
      server_name       = module.job_agent.server_name
      database_name     = "appdb"
      job_credential_id = module.job_credential.id
    }
  }
}

module "nightly" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job.git?ref=v1.0.0"

  name         = "nightly-maintenance"
  job_agent_id = module.job_agent.id
  description  = "Rebuild indexes on appdb"
}

module "rebuild_step" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-step.git?ref=v1.0.0"

  name                = "rebuild-indexes"
  job_id              = module.nightly.id
  job_step_index      = 1
  job_target_group_id = module.targets.id
  job_credential_id   = module.job_credential.id

  sql_script      = "EXEC sp_rebuild_indexes"
  retry_attempts  = 3
  timeout_seconds = 3600
}

module "nightly_schedule" {
  source = "git::https://github.com/microsoftexpert/terraform-azurerm-mssql-job-schedule.git?ref=v1.0.0"

  job_id   = module.nightly.id
  type     = "Recurring"
  interval = "PT24H"
}

output "elastic_jobs" {
  value = {
    scope      = module.targets.target_types_by_key
    retry_wait = module.rebuild_step.maximum_retry_wait_seconds
    principals = module.rebuild_step.credentials_in_use
  }
}

✅ All six modules of the Elastic Jobs family, in dependency order. 🔴 jobuser must exist on appdb with the rights sp_rebuild_indexes needs — the credential module records it and creates nothing. ✅ The retries and timeout are chosen here rather than inherited, which is the point of example 5.

📥 Inputs

Required — job_id, name, job_step_index, job_target_group_id, sql_script.

Optional — job_credential_id, the four retry arguments, timeout_seconds, output_target, timeouts.

Full input schemas

variable "job_id"              { type = string } # force-new; the JOB, not the agent
variable "name"                { type = string } # force-new
variable "job_step_index"      { type = number } # ONE-based; not force-new
variable "job_target_group_id" { type = string } # the SCOPE; not force-new
variable "sql_script"          { type = string } # STORED IN STATE in plain form

variable "job_credential_id" {
  type    = string # EXECUTES the T-SQL
  default = null
}

variable "initial_retry_interval_seconds" {
  type    = number # 1..2147483
  default = 1
}

variable "maximum_retry_interval_seconds" {
  type    = number # 1..2147483; STRICTLY greater than the initial
  default = 120
}

variable "retry_attempts" {
  type    = number # >= 1; there is no "do not retry"
  default = 10
}

variable "retry_interval_backoff_multiplier" {
  type    = number # >= 1; exactly 1 means a constant interval
  default = 2.0
}

variable "timeout_seconds" {
  type    = number # 1..2147483; the default is TWELVE HOURS
  default = 43200
}

variable "output_target" {
  type = object({
    mssql_database_id = string                 # a SQL DATABASE id
    table_name        = string
    schema_name       = optional(string, "dbo")
    job_credential_id = optional(string)       # WRITES the results
  })
  default = null # a second data path -- omit it to discard the result set
}

variable "timeouts" {
  type = object({
    create = optional(string) # 30m -- the API call, NOT the script
    read   = optional(string) # 5m
    update = optional(string) # 30m
    delete = optional(string) # 30m
  })
  default = null
}

🧾 Outputs

Output	Description	Notes
`id`	Resource ID	first
`name`	As configured
`job_id`	As configured
`job_name`	Computed
`job_agent_id`	Computed — derived	composition
`job_agent_name`	Computed
`server_id`	Computed	RBAC scope
`server_name`	Computed
`resource_group_name`	Computed
`job_step_index`	One-based	control flow
`job_target_group_id`	Where the scope comes from	scope review
`job_credential_id`	The EXECUTION credential	access review
`timeout_seconds`	Per execution; 12h default	operations
`retry_attempts`	10 by default	operations
`maximum_retry_wait_seconds`	Computed — the four knobs' sum	operations
`credentials_in_use`	Computed — distinct principals	access review
`writes_results_to_a_table`	Computed	data review
`output_target_database_id`	Computed	data review
`output_target_table`	Computed — schema-qualified	data review
`this_is_where_a_job_becomes_work`	Constant `true`	read this first
`the_scope_is_the_target_group_multiplied_by_the_credential`	Constant `true`	scope review
`three_credentials_do_three_different_jobs_in_this_family`	Constant `true`	access review
`the_sql_script_is_stored_in_state_in_plain_form`	Constant `true`	secret review
`the_script_runs_on_every_target_unchanged`	Constant `true`	design review
`the_script_should_be_idempotent_because_it_is_retried`	Constant `true`	design review
`the_retry_defaults_are_generous_and_compound`	Constant `true`	operations
`the_twelve_hour_timeout_is_a_default_not_a_choice`	Constant `true`	operations
`the_maximum_retry_interval_must_exceed_the_initial_one`	Constant `true`	design rationale
`step_indexes_are_one_based_and_not_force_new`	Constant `true`	control flow
`an_output_target_is_a_second_data_path`	Constant `true`	data review
`the_output_table_is_not_created_by_this_module`	Constant `true`	troubleshooting
`force_new_fields`	Two	change planning
`fields_that_can_change_after_creation`	Ten	change planning
`create_refuses_an_existing_step`	Constant `true` by default	adoption
`the_import_guard_can_be_disabled_by_a_provider_feature`	Constant `true`	blast radius
`module_timeouts_are_not_the_step_timeout`	Constant `true`	operations
`fields_azure_returns_on_read`	Where drift is detectable	drift review
`this_resource_supports_no_azure_resource_tags`	Constant `true`	tagging policy

🔴 No output is marked sensitive, and one input should worry you anyway — see the_sql_script_is_stored_in_state_in_plain_form.

🧠 Architecture Notes

A step is the only resource in this family that does anything. The agent is infrastructure, the job is a name, the schedule is timing, the target group is a list — and this is where a script, a scope and a set of rights meet. That also makes it the only place where reviewing "what can this job do" has a complete answer, and even here the answer is split: job_target_group_id points at the which, job_credential_id at the what rights, and neither is resolvable from this resource alone.

Three credentials, three purposes, easily conflated. A target group entry's credential enumerates a server or pool; this step's credential executes the T-SQL; an output target's credential writes the results. In most configurations they are the same principal, which is precisely why the distinction surfaces only when one of them is wrong. credentials_in_use collapses the set this step references into one output for review.

The retry policy is four interacting arguments with generous defaults. Ten attempts, a doubling backoff starting at one second, a two-minute ceiling and a twelve-hour per-execution timeout are all the provider's defaults rather than choices anyone made. Nothing states what they add up to, so maximum_retry_wait_seconds computes the capped geometric sum — 487 seconds at the defaults — and the real worst case is that plus up to ten twelve-hour executions. For a step whose failure is structural rather than transient, that is a long time before anyone hears about it.

Idempotence is not optional. Because a failing execution is retried automatically, a script that is not safe to run twice will be run twice. And because the same script runs unchanged on every database the target group resolves to, anything it assumes about schema, data or permissions must hold everywhere.

The script is in state. sql_script is an ordinary string argument: it appears in plan output and in the state file exactly as written, with no sensitivity marking. This library's standing position — that sensitive = true redacts plans and does not encrypt state — applies here with more force, because there is no marking at all. A credential embedded in a script is a credential in state.

Almost everything updates in place. Only name and job_id are force-new unconditionally; the script, the target group and every retry knob change without replacement. So an in-place update to this resource can represent a complete change in what a job does and where — which is not what an in-place update usually signals in a plan.

But one argument is force-new in a single direction, and nothing outside the plan says so. job_credential_id is marked force-new from inside the provider's CustomizeDiff, by a direct ForceNew() call guarded by the old value was non-empty and the new one is empty — its own source comment reads "Once set, job_credential_id cannot be removed." So setting the credential is an in-place update, changing it to a different credential is an in-place update, and deleting the line destroys and recreates the step. That fact is in no schema walk, not in the binary provider schema and not in the published documentation, because it is not a schema flag — the plan is the only place it appears. clearing_job_credential_forces_replacement reports it.

🧱 Design Principles

Concern	This module's default (empty call)	Opt-out
Retry policy	the provider's — 10 attempts, ×2, 1s–120s	tighten it; the sum is emitted
Execution timeout	43200s (twelve hours), the provider's	lower it
Output target	none — the result set is discarded	configure one, and read example 7
Credential	none — the schema permits it, Azure generally does not	supply one
Retry interval rule	strictly greater, refused offline	— the provider needs a round trip to refuse it
Script content	not parsed, not checked, not redacted	— do not embed secrets
Step index	required, one-based	—
Secrets	none accepted — but the script is stored in state	—

🔴 Secure-by-default cannot reach inside a script. What this module can do is compute the retry cost, name every principal involved, and say plainly that the script is stored in state — three things the plan does not show.

🚀 Runbook

terraform init -backend=false
terraform validate
terraform fmt -check

Pin the module with ?ref=v1.0.0 — never a branch.
Plan-only. A human applies from CI.
🔴 Read maximum_retry_wait_seconds and timeout_seconds before accepting the defaults on a step whose failure would be structural.
🔴 Follow job_target_group_id to that module's scope outputs before approving a change to it.
🔴 Check credentials_in_use in access review — no Azure role bounds what those principals can do.
⚠️ Never embed a secret in sql_script. It goes to state in plain form.

🧪 Testing

terraform validate covers the configuration and the retry arithmetic; the consequences are runtime:

Checked offline by `validate`	Only reachable at apply or at run time
Every ID is the right kind, anchored	Whether those resources exist
The index is a whole number ≥ 1	Whether another step shares it
Ranges on all four retry arguments and the timeout	How long the step really takes
Maximum retry interval strictly exceeds the initial	Whether the script is valid T-SQL
Output target is a database ID with a non-empty table	Whether that table exists or is writable
—	What the credential is actually permitted to do

💬 Example Output

id                         = "/subscriptions/.../jobs/nightly-maintenance/steps/rebuild-indexes"
name                       = "rebuild-indexes"
job_name                   = "nightly-maintenance"
job_agent_name             = "jobs"
server_name                = "sqlsrv01"
job_step_index             = 1
job_target_group_id        = "/subscriptions/.../targetGroups/prod-databases"
job_credential_id          = "/subscriptions/.../credentials/jobuser"
timeout_seconds            = 3600
retry_attempts             = 3
maximum_retry_wait_seconds = 7
credentials_in_use         = ["/subscriptions/.../credentials/jobuser"]
writes_results_to_a_table  = false
output_target_table        = null
force_new_fields           = ["name", "job_id"]
the_sql_script_is_stored_in_state_in_plain_form = true

🔍 Troubleshooting

Symptom	Cause	Fix
A failing step retries for hours	Ten attempts with a doubling backoff, all defaults	Lower `retry_attempts` — example 6
A step holds a database for twelve hours	`timeout_seconds` defaults to 43200	Set it deliberately
`maximum_retry_interval_seconds must be STRICTLY GREATER`	The two intervals are equal or inverted	Equal is refused, not just smaller
The script ran somewhere unexpected	The target group's scope is wider than assumed	Check `job_targets_covering_a_whole_server` on that module
The script succeeded once and failed on retry	It is not idempotent	Retries are automatic; make it safe to repeat
A password appeared in the state file	It was embedded in `sql_script`	Read it from within SQL instead — example 4
The output target write failed	The table, schema or write credential is wrong	Nothing here checks them — example 7
Two steps ran in an unexpected order	They share a `job_step_index`	Each step is a separate resource; nothing sees the collision
A step's reach changed with no replacement in the plan	The target group and script both update in place	Expected — example 9
`A resource with the ID ... already exists`	The requires-import guard fired	Import the step rather than creating it

🔗 Related Docs

azurerm_mssql_job_step — provider documentation
Azure SQL Elastic jobs overview — steps, targets and credentials
Create and manage elastic jobs — including output targets
Sibling modules: terraform-azurerm-mssql-job, terraform-azurerm-mssql-job-target-group, terraform-azurerm-mssql-job-credential, terraform-azurerm-mssql-job-schedule, terraform-azurerm-mssql-job-agent
This module's SCOPE.md

💙 "Infrastructure as Code should be standardized, consistent, and secure."

Navigation Menu

Repository navigation

About

Repository files navigation

☁️ Azure SQL Elastic Job Step Terraform Module

🧩 Overview

❤️ Support this project

🗺️ Where this fits in the family

🧬 What this module builds

✅ Provider / Versions

🔑 Required Azure RBAC Roles / Permissions

Azure Prerequisites

📁 Module Structure

⚙️ Quick Start

🔌 Cross-Module Contract

📚 Example Library

📥 Inputs

🧾 Outputs

🧠 Architecture Notes

🧱 Design Principles

🚀 Runbook

🧪 Testing

💬 Example Output

🔍 Troubleshooting

🔗 Related Docs

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
SCOPE.md		SCOPE.md
main.tf		main.tf
outputs.tf		outputs.tf
providers.tf		providers.tf
variables.tf		variables.tf

Latest commit

History

Folders and files

Repository files navigation

☁️ Azure SQL Elastic Job Step Terraform Module

🧩 Overview

❤️ Support this project

🗺️ Where this fits in the family

🧬 What this module builds

✅ Provider / Versions

🔑 Required Azure RBAC Roles / Permissions

Azure Prerequisites

📁 Module Structure

⚙️ Quick Start

🔌 Cross-Module Contract

📚 Example Library

📥 Inputs

🧾 Outputs

🧠 Architecture Notes

🧱 Design Principles

🚀 Runbook

🧪 Testing

💬 Example Output

🔍 Troubleshooting

🔗 Related Docs

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages