How to automate the deployment of an Elastic Job Agent with a Private Endpoint via Bicep
Environment Overview Recently, I worked on a case where our customer encountered an issue during the deployment of an Elastic Job Agent (EJA) and a Private Endpoint (PEP) using a Bicep deployment script. The process resulted in an endless deployment loop. In this article, I will guide you step by step through creating an EJA with a PEP and demonstrate how to automatically approve the connection using a Bicep deployment script. Technical Issue The main challenge is that the EJA PEP is fully managed by Microsoft, which means you cannot specify a name during the script authorization process—the system assigns one randomly. To address this, we will use deploymentScripts in combination with PowerShell to monitor and authorize the process automatically. For more details, please refer to the following resources: References Use deployment scripts in Bicep https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/deployment-script-bicep?tabs=azure-cli Elastic jobs in Azure SQL Database https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview?view=azuresql Elastic job private endpoints https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview?view=azuresql#elastic-job-private-endpoints The Idea Since it's not possible to specify the EJA PEP name as a variable for direct approval—unlike standard PEPs—the approach was to intercept the auto-generated PEP by its ID or name and then authorize it automatically. (Remember to always follow the Principle of Least Privilege (PoLP)); in my example, I used Contributor permissions for simplicity. ****************************************************************************************************************************************************************************************************** /*Disclaimer: ****************************************************************************************************************************************************************************************************** This script is not supported under any Microsoft standard support program or service. This script is provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the script and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the script be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the current script or documentation, even if Microsoft has been advised of the possibility of such damages. I’m not a programmer or a Bicep expert. I simply put together what I was able to reason through in order to meet our client’s request. Any suggestions or modifications are welcome. ****************************************************************************************************************************************************************************************************** This article was written in August 2025; code and modules may change over time. ****************************************************************************************************************************************************************************************************** */ param prefix string = 'bicep' param environment string = 'test' param sqlServerName string = 'bicep-test-sqleja-sqlsrv' param location string = 'westeurope' param forceUpdateTag string = utcNow() //let's check that the previously manually created server is present resource sqlServer 'Microsoft.Sql/servers@2021-11-01' existing = { name: sqlServerName } //create the database resource sqlDb 'Microsoft.Sql/servers/databases@2021-11-01' = { parent: sqlServer name: '${prefix}-${environment}-sqleja-sqldb' location: location properties: { requestedBackupStorageRedundancy: 'Local' maxSizeBytes: 5368709120 } sku: { name: 'S3' } } //create the Job Agent resource sqlJob 'Microsoft.Sql/servers/jobAgents@2024-05-01-preview' = { parent: sqlServer location: location name: '${prefix}-${environment}-sqleja-sqljobagent' properties: { databaseId: sqlDb.id } sku: { name: 'JA100' } } //defines a Target Group within a Job Agent resource targetGroup 'Microsoft.Sql/servers/jobAgents/targetGroups@2024-05-01-preview' = { parent: sqlJob name: '${prefix}-${environment}-sqleja-targetgroup' properties: { members: [ { membershipType: 'Include' serverName: sqlServer.name type: 'SqlServer' } ] } } //generate an UMI to auth and deploy the PS script resource scriptIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = { name: '${prefix}-${environment}-deployscript-umi' location: location } //assign the role to the script UMI //https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles resource scriptRoleAssignment 'Microsoft.Authorization/roleAssignments@2022-04-01' = { name: guid(scriptIdentity.id, 'script-role-assignment') properties: { roleDefinitionId: subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'b24988ac-6180-42a0-ab88-20f7382dd24c') // Contributor principalId: scriptIdentity.properties.principalId } dependsOn: [ delayScript ] } //let's generate a delay to allow Azure AD permissions propagation resource delayScript 'Microsoft.Resources/deploymentScripts@2023-08-01' = { name: 'sleep-60s-bash' location: location kind: 'AzureCLI' identity: { type: 'UserAssigned' userAssignedIdentities: { '${scriptIdentity.id}': {} } } properties: { azCliVersion: '2.76.0' scriptContent: ''' echo "Sleeping for 60 seconds..." sleep 60 echo "Done sleeping." ''' retentionInterval: 'P1D' cleanupPreference: 'Always' timeout: 'PT5M' forceUpdateTag: forceUpdateTag } dependsOn: [ scriptIdentity ] } //create the eja pep resource privateEndpoint 'Microsoft.Sql/servers/jobAgents/privateEndpoints@2024-05-01-preview' = { parent: sqlJob name: '${prefix}-${environment}-sqleja-pep' properties: { targetServerAzureResourceId: sqlServer.id } } //PS script to approve the MS fully managed PEP resource approvePrivateEndpointConnection 'Microsoft.Resources/deploymentScripts@2023-08-01' = { name: 'ps-to-approve-eja-pep-by-script' location: location kind: 'AzurePowerShell' identity: { type: 'UserAssigned' userAssignedIdentities: { '${scriptIdentity.id}': {} } } properties: { azPowerShellVersion: '11.0' scriptContent: ''' Connect-AzAccount -Identity $SqlServerResourceIdForPrivateLink = '/subscriptions/<yourSubID>/resourceGroups/bicep-rg/providers/Microsoft.Sql/servers/bicep-test-sqleja-sqlsrv' $SQLprivateEndpoints = Get-AzPrivateEndpointConnection -PrivateLinkResourceId $SqlServerResourceIdForPrivateLink foreach ($privateEndpoint in $SQLprivateEndpoints) { if ($privateEndpoint.PrivateLinkServiceConnectionState.Status -eq "Pending") { $id = $privateEndpoint.id Write-Output "Approving Private Endpoint Connection: $id" Approve-AzPrivateEndpointConnection -ResourceId "${id}" -Description "Approved by Bicep Deployment Script" } } ''' retentionInterval: 'P1D' cleanupPreference: 'Always' timeout: 'PT30M' forceUpdateTag: forceUpdateTag } dependsOn: [ sqlJob scriptRoleAssignment ] } Deployment Details Below is an overview of the resources created by the automation Conclusion With this article, I’ve wanted to share how to automate the creation of an Elastic Job Agent Private Endpoint without requiring manual approval. Kindly note that the products and options presented in this article are subject to change, this article reflects for Azure SQL Database in September 2025. Please also make sure to review the code carefully. It is provided as-is, with no guarantees, warranties, or support. Any suggestions or improvements are welcome. I hope you find this guide useful and that it inspires you to experiment freely with your deployments. Thank you. Best Regards, VittorioLesson Learned #8: Monitoring the geo-replicated databases.
First published on MSDN on Oct 31, 2016 We received multiple requests in order to have answered the following questions: Is there needed a maintenance plan for geo-replicated databases? How to monitor the geo-replicated databasesAnswering the question: "Is it needed a maintenance plan for geo-replicated databases?",No, there is not needed because is you have a maintenance plan for rebuilding indexes and update statistics for the primary database, these command will be executed in all geo-replicated databases that you have.Lesson Learned #402:Automated Monitoring of Table Growth in SQL Databases Using PowerShell
Uncontrolled table growth in a SQL database can have a significant impact on system performance and efficiency. It is crucial for database administrators to monitor and detect significant changes in table size and row count to take timely corrective actions. In this article, we will introduce a PowerShell script that automates the process of monitoring table growth and provides alert notifications when predefined thresholds are exceeded.Invoke-Sqlcmd with Azure Automation for Azure SQL database
In this blog article, we will be illustrating the required steps to use Invoke-Sqlcmd against an Azure SQL database from Azure Automation. We have received a few cases where customers would like to automate a specific script to be executed on their Azure SQL database like select, update, insert or delete.Restore database across servers(Azure SQL Database and Azure SQL managed instance)- Azure Automation
In this article, we consider the scenario where customers would like to restore their Azure SQL database and managed database from one Azure SQL server to another Azure SQL server, for example for development reasons. The article will provide you with the steps to achieve this by automating the job using Azure AutomationAzure SQL Database Token-based authentication with PowerShell
First published on MSDN on Oct 26, 2018 How to connect to Azure SQL Database using token-based authentication in PowerShell native appsThis guide assumes you already have a deployment of an Azure SQL Database, your PowerShell environment configured and you have an app registration for a native app in Azure Active Directory.How to create a native PowerShell app registration to connect to Azure SQL DB
First published on MSDN on Oct 30, 2018 How to create a native app registrationThis guide assumes that you already own an Azure Subscription, an Azure Active Directory properly setup and your PowerShell environment properly set for running scripts.Lesson Learned #39: Best Practices using BCP in Azure SQL Database
First published on MSDN on Apr 03, 2018 In multiple support cases our customers asked questions about what are the best practices to avoid fill up the transaction log or temporal database when they are importing a high number of rows using BCP.
