Uredi

Deli z drugimi prek


Using Azure Policy to enforce Microsoft Entra-only authentication with Azure SQL

Applies to: Azure SQL Database Azure SQL Managed Instance

This article guides you through creating an Azure Policy that would enforce Microsoft Entra-only authentication when users create an Azure SQL Managed Instance, or a logical server for Azure SQL Database. To learn more about Microsoft Entra-only authentication during resource creation, see Create server with Microsoft Entra-only authentication enabled in Azure SQL.

Note

Although Azure Active Directory (Azure AD) has been renamed to Microsoft Entra ID, the policy names currently contain the original Azure AD name, so Microsoft Entra-only and Azure AD-only authentication is used interchangeably in this article.

In this article, you learn how to:

Prerequisite

Create an Azure Policy

Start off by creating an Azure Policy enforcing SQL Database or Managed Instance provisioning with Azure AD-only authentication enabled.

  1. Go to the Azure portal.

  2. Search for the service Policy.

  3. Under the Authoring settings, select Definitions.

  4. In the Search box, search for Azure Active Directory only authentication.

    There are two built-in policies available to enforce Azure AD-only authentication. One is for SQL Database, and the other is for SQL Managed Instance.

    • Azure SQL Database should have Azure Active Directory Only Authentication enabled
    • Azure SQL Managed Instance should have Azure Active Directory Only Authentication enabled

    Screenshot of Azure Policy for Azure AD-only authentication

  5. Select the policy name for your service. In this example, we'll use Azure SQL Database. Select Azure SQL Database should have Azure Active Directory Only Authentication enabled.

  6. Select Assign in the new menu.

    Note

    The JSON script in the menu shows the built-in policy definition that can be used as a template to build a custom Azure Policy for SQL Database. The default is set to Audit.

    Screenshot of assigning Azure Policy for Azure AD-only authentication

  7. In the Basics tab, add a Scope by using the selector (...) on the side of the box.

    Screenshot of selecting Azure Policy scope for Azure AD-only authentication

  8. In the Scope pane, select your Subscription from the drop-down menu, and select a Resource Group for this policy. Once you're done, use the Select button to save the selection.

    Note

    If you do not select a resource group, the policy will apply to the whole subscription.

    Screenshot of adding Azure Policy scope for Azure AD-only authentication.

  9. Once you're back on the Basics tab, customize the Assignment name and provide an optional Description. Make sure the Policy enforcement is Enabled.

  10. Go over to the Parameters tab. Unselect the option Only show parameters that require input.

  11. Under Effect, select Deny. This setting prevents creating a logical server without Azure AD-only authentication enabled.

    Screenshot of  Azure Policy effect parameter for Azure AD-only authentication.

  12. In the Non-compliance messages tab, you can customize the policy message that displays if a violation of the policy has occurred. The message will let users know what policy was enforced during server creation.

    Screenshot of Azure Policy non-compliance message for Azure AD-only authentication.

  13. Select Review + create. Review the policy and select the Create button.

Note

It may take some time for the newly created policy to be enforced.

Check policy compliance

You can check the Compliance setting under the Policy service to see the compliance state.

Search for the assignment name that you have given earlier to the policy.

Screenshot of Azure Policy compliance for Azure AD-only authentication.

Once the logical server is created with Azure AD-only authentication, the policy report will increase the counter under the Resources by compliance state visual. You'll be able to see which resources are compliant, or non-compliant.

If the resource group that the policy was chosen to cover contains already created servers, the policy report will indicate those resources that are compliant and non-compliant.

Note

Updating the compliance report may take some time. Changes related to resource creation or Microsoft Entra-only authentication settings are not reported immediately.

Provision a server

You can then try to provision a logical server or managed instance in the resource group that you assigned the Azure Policy. If Azure AD-only authentication is enabled during server creation, the provision will succeed. When Azure AD-only authentication isn't enabled, the provision will fail.

For more information, see Create server with Microsoft Entra-only authentication enabled in Azure SQL.

Next steps