Question

You are configuring an Elastic Job agent in Azure SQL Database to run a nightly index maintenance script across 40 databases in a target group. The job must connect to each target database and also write execution output to a central logging database. The job repeatedly fails with authentication errors when connecting to the target databases. You confirm the job's database-scoped credential exists in the job database. What must you also do to allow the job to authenticate successfully against every target database?

Accepted Answer

Create a matching database-scoped credential and a corresponding login/user with the required permissions in each target database, using the same credential name referenced by the job step.

Answer

Grant the Elastic Job agent's system-assigned managed identity the SQL DB Contributor role on each target server in the Azure portal.

Answer

Add a server-level firewall rule on each target logical server to allow the job database's outbound IP address.

Answer

Enable the 'Allow Azure services and resources to access this server' setting on every target logical server.