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:

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

Prerequisite

• Have permissions to manage Azure Policy. For more information, see Azure RBAC permissions in Azure Policy.

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

   

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.

   

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

   

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.

   

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.

    

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.

    

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.

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

• Overview of Azure Policy for Azure AD-only authentication
• Create server with Microsoft Entra-only authentication enabled in Azure SQL
• Overview of Microsoft Entra-only authentication