Automation of maintenance activities in Azure SQL Database (3° Part)

Database maintenance is an important factor also for Azure SQL and Elastic Jobs is the most complete solution for automation of scheduled activities to be performed on Azure SQL databases.

In the previous article Automating Azure SQL Database maintenance tasks (2° part), we described the initial implementation (preview) of Azure Elastic Job Agents, through which it’s possible to create and schedule processes on one or more Azure SQL databases to execute queries or maintenance tasks.

Last November, Microsoft announced a refresh of this technology with significant improvements in security, management portal, scalability, and much more. Here are the main updates:

• Support for Microsoft Entra ID (formerly Azure Active Directory)
• Private Link managed by the service to securely connect to the target databases
• Integration with Azure Alerts for job execution status information
• Enhanced scalability to connect to multiple targets simultaneously

In this article, I will focus on describing the major changes (compared to the previous post) in terms of configuration and security of connections to the target databases. The initial implementation of Elastic Job technology only allowed authentication with database-scoped credentials, while now the recommended authentication method is based on Microsoft Entra ID.

Authentication with Microsoft Entra ID

With support for Microsoft Entra ID, a user-assigned managed identity (UMI) can now be assigned to the Job Agent during the creation of an Elastic Job or subsequently through the Azure portal or exposed web APIs for this purpose. Although the existing SQL authentication method based on database-scoped credentials continues to be supported, Microsoft Entra ID is the recommended and secure mechanism to allow Job Agents to connect to the target databases.

The Elastic Job agent must be able to authenticate on each logical server or target database. Therefore, before creating (which can also be done later, but my advice is to do it first) an Elastic Job, it is recommended to create a new user-assigned managed identity (UMI), which can be selected during the creation of the Elastic Job. To use Microsoft Entra authentication with a user-assigned managed identity (UMI), the following steps are required:

1. Enable Microsoft Entra authentication on all target servers/databases, in the Azure SQL instance hosting the job database, and in the one hosting the output database of the processes. For this step, you can follow the tutorial Enable Microsoft Entra-only authentication with Azure SQL, leaving SQL Authentication active if desired
2. Create a user-assigned managed identity (UMI) following the steps described in the article Manage user-assigned managed identities
3. Assign the created user-assigned managed identity (UMI) to the Elastic Job agent following the steps described in the section Create and configure the elastic job agent
4. Create a group with target servers and databases and add the jobs with the commands to be executed in the target databases. Unlike the scripts in the previous post, using Microsoft Entra does not require specifying the database-scoped credentials
5. In each of the target servers/databases, create a contained user mapped to the UMI using the T-SQL code below or refer to the examples in the section Create the job authentication.

-- Connect to the master database of the Azure SQL logical instance of job agent
-- Use universal with MFA authentication type

-- Create a login on the master database mapped to a user-assigned managed identity (UMI)
CREATE LOGIN [umi-for-elastic-job] FROM EXTERNAL PROVIDER;
GO

Also, it’s necessary to create a user in the master database mapped to the previously created login. The absence of this user will cause the error:

Failed to determine members of SqlServerTarget (server name 'azure...', server location 'azure...'): The server principal "926e2a2b...@1f36c249..." is not able to access the database "master" under the current security context. Cannot open database "master" requested by the login. The login failed. Login failed for user '926e2a2b...@1f36c249...'.

CREATE USER [umi-for-elastic-job] FROM EXTERNAL PROVIDER;
GO

Now, it’s necessary to connect to the target database of the Azure SQL logical instance where you want the jobs to run to create the appropriate users and grant them the necessary GRANT permissions to access the objects (in the example [dbo].[IndexOptimize] and [dbo].[CommandExecute]).

-- Connect to the target database of the Azure SQL logical instance
-- Create a user on a user database mapped to a login
CREATE USER [umi-for-elastic-job] FROM EXTERNAL PROVIDER;
GO

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT EXECUTE ON OBJECT::[dbo].[IndexOptimize] TO [umi-for-elastic-job];
GRANT EXECUTE ON OBJECT::[dbo].[CommandExecute] TO [umi-for-elastic-job];
GO

Now you are ready to schedule the job by setting a schedule.

Compared to the initial implementation of this technology, the refresh from last November requires the job database to be at level S1 and not S0 as it was in the past.

Pricing and release (GA) of Azure SQL Elastic Jobs

Elastic Jobs will remain free during the preview period and until it’s generally available (GA). Upon GA release, Elastic Jobs will be billed based on the capacity level selected in the Job Agent.

The GA release is planned for the first quarter of 2024. The exact date will be announced soon.

Enjoy using Azure SQL Elastic Jobs!