You deploy an instance of SQL Server on Azure Virtual Machines named VM1.
You need to create a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1. The solution must use the principle of least privilege.
What should you create first?
To create a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1 while adhering to the principle of least privilege, a system-assigned managed identity in Azure AD should be created first. A system-assigned managed identity is automatically managed by Azure and tied specifically to the Azure VM instance. This means that permissions can be meticulously controlled and granted only to the required resources, satisfying the least privilege principle. This approach ensures that the SQL Server Agent job can perform its tasks securely without extensive manual handling of credentials.
The answer is correct. C. a system-assigned managed identity in Azure AD should be created first to implement the principle of least privilege when creating a SQL Server Agent job that will rebuild indexes of the databases hosted on VM1. With a system-assigned managed identity, Azure automatically creates an identity in Azure AD that is tied to the Azure VM instance. This allows you to grant the identity permissions to the specific resources that it requires, without having to manage credentials or rotate passwords. Using a managed identity ensures that only the necessary permissions are granted to the job, which is in line with the principle of least privilege. The job can then use this managed identity to perform the required actions, such as rebuilding indexes.
A. local Windows account The question does not say if tenant has Azure AD or is joined to domain, and also I don't see how SQL Agent needs system-assigned or user-assigned managed identity to run jobs on SQL Server ? I can see how local windows account is needed for SQL Agent service to run
Careful guys, While a user-assigned identity is convenient as far as being able to assign it to multiple resources, this questions specifically refers to a SINGLE sql server on a SINGLE virtual machine. If this question was asking about how to rebuild indexes on multiple sql vm servers, then yes it would be better to leverage a user-assigned identity. My answer is C.
See Dalamain’s comment and article. I choose C
Some info here: https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview#managed-identity-types
C. Create a system-assigned managed identity in Azure AD. A system-assigned managed identity enables an Azure resource to identify itself to Azure AD. It can be used to access Azure resources and services without typically storing usernames and passwords in the code.
should be user assigned managed identity. "User-assigned managed identities are more efficient in a broader range of scenarios than system-assigned managed identities. See the table below for some scenarios and the recommendations for user-assigned or system-assigned. User-assigned identities can be used by multiple resources, and their life cycles are decoupled from the resources’ life cycles with which they’re associated. Read which resources support managed identities." https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/managed-identity-best-practice-recommendations
I think it should be user-assigned managed identity.