Profile Applicability:

  • Level 1

Description:
Microsoft Entra authentication allows SQL Server to authenticate users based on identities stored in Microsoft Entra ID (formerly known as Azure Active Directory). This simplifies credential management by enabling centralized authentication for all users, thereby reducing the proliferation of individual credentials across multiple databases.

Rationale:
 Enabling Microsoft Entra authentication provides secure, centralized identity management and eliminates the need for SQL authentication. It supports a variety of authentication methods, including Multi-Factor Authentication (MFA) and integrated Windows authentication. This reduces the risk of unauthorized access and ensures better security management.

Impact:

  • Pros:

    • Centralized management of database users and permissions through Entra ID.

    • Provides stronger security through integrated authentication methods (e.g., MFA).

    • Eliminates the need for password management on SQL servers.

  • Cons:

    • Increased administrative overhead for configuring Entra ID-based permissions.

    • Requires Entra ID licensing and proper configuration.

    • Potential compatibility issues with some legacy applications that do not support Entra ID.

Default Value:

By default, Microsoft Entra authentication for SQL Database/Server is not enabled.

Pre-requisites:
 Ensure that Microsoft Entra ID is configured and that SQL Server is set to use Entra ID for authentication.

Remediation

Test Plan:

Using Azure Portal:

  1. Go to SQL servers in the Azure Portal.

  2. Select the relevant SQL server.

  3. Under Settings, click on Microsoft Entra admin.

  4. Ensure that a value has been set for Admin Name under the Microsoft Entra admin section.

Using Azure CLI:
1.  Run the following command to list the SQL Server administrators:

az sql server ad-admin list --resource-group <resource-group> --server <server>

  • Ensure that the DisplayName is set to the Entra ID account.

Using PowerShell:

1. List all SQL servers:

Get-AzSqlServer

2. For each server, verify the administrator:

Get-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName <resource-group-name> -ServerName <server-name>

  • Ensure the DisplayName is set to the Entra ID account.

Implementation Plan:

Using Azure Portal:

  1. Navigate to SQL servers in the Azure Portal.

  2. Select the SQL server and go to Microsoft Entra admin.

  3. Click on Set admin and choose an Entra ID admin.

  4. Click Save to apply the changes.

Using Azure CLI:
 1. To assign an Entra ID admin, run:

az sql server ad-admin create --resource-group <resource-group-name> --server <server-name> --display-name <display-name> --object-id <object-id>


Backout Plan:

Using Azure Portal:

  1. Go to SQL servers.

  2. Under Microsoft Entra admin, remove the current administrator and save the changes.

Using Azure CLI:
1.  To remove the Entra ID admin, run:

az sql server ad-admin delete --resource-group <resource-group-name> --server <server-name>


References: