Creating my first Elastic Database Job on Azure

Question

On https://learn.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview it says that there is a new replacement for SQL Agent. However, I cannot get it to work.

I had problems with accepting the "Beta software" terms, but my manager accepted the terms on his account, and I could carry on.

On the portal I get this error message:

Deployment failed
Deployment to resource group 'MyRG' failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.

I have also tried the Powershell interface, and here I get stuck on

New-AzSqlElasticJobAgent : No tenant found in the context.  Please ensure that the credentials you provided are authorized to access an Azure subscription, then run Connect-AzAccount to login.
At line:4 char:28
+ $JobAgent = $JobDatabase | New-AzSqlElasticJobAgent -Name $AgentName
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [New-AzSqlElasticJobAgent], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ElasticJobs.Cmdlet.NewAzureSqlElasticJobAgent

It is probably something simple, but I cannot figure out what...

Answer 1

The error on the portal is not clear, but you should be able to find details in deployment summary.

On portal go to your resource group and navigate to Deployment tab. There you can find history of the deployments with details about progress and errors.

The problem with Powershell looks like you don't set subscription context.

You should set the context to subscription which contains Reasource Group to which you want to deploy content.

To make this work use this example: Example 1: Set the current subscription

After apply current subscription you should be able to see what is the real error during deployment.

Answer 2

After opening a support case with Microsoft, the cause of the problem has been found. The database had a Database Trigger defined, that the new job_admin account did not have sufficient rights to execute. So all schema modifications rolled back.

I initially disabled the trigger like this

disable trigger MyDatabaseTrigger on database MyDatabase

I could also just have granted insert rights to the new job account, like this:

grant insert on dbo.MyChangeLogTable to jobs_admin