Feedback Edit

Using Azure Policy to enforce Azure Active Directory only authentication with Azure SQL

  • Article
  • 3 minutes to read

Applies to: Azure SQL Database Azure SQL Managed Instance

Note

The Azure AD-only authentication and associated Azure Policy feature discussed in this article is in public preview.

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

In this article, you learn how to:

  • Create an Azure Policy that enforces logical server or managed instance creation with Azure AD-only authentication enabled
  • Check Azure Policy compliance

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 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 will prevent a logical server creation 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 Azure AD-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 Azure AD-only authentication enabled in Azure SQL.

Next steps