Using Azure Managed Identity for executing SQL Job

Introduction

In Microsoft SQL Server, a SQL Job, also known as a SQL Server Agent job, is an automated task or process scheduled to run at specific intervals or in response to certain events. These tasks can include:

• Database maintenance activities (e.g., backups, index maintenance, integrity checks)
• Data integration processes (e.g., ETL, data loading)
• Report generation
• Administrative tasks (e.g., monitoring, alerting)

Traditionally, on-premises, when executing SQL jobs, you would use AD service account or SQL user under which jobs executes. AD service account provides better control from management perspective compared to SQL user. When you want to modernize and move to Azure SQL DB or Azure SQL Managed Instance, unless your on-premises Active Directory is enabled for authentication, you cannot use AD service account.

Azure Active Directory (AD) supports two types of managed identities: System-assigned managed identity (SMI) and user-assigned managed identity (UMI). In this article, we will explore use of SMI and UMI for the above purpose. This article is applicable to Azure SQL DB and Azure SQL Managed Instance.

Azure Managed Identity

A common challenge for developers is the management of secrets, credentials, certificates, and keys used to secure communication between services. SQL Managed…