Logo of Trend Micro
Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Use Microsoft Entra Admin for SQL Authentication

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: Sql-004

Ensure that Microsoft Entra ID authentication is configured to allow you to centrally manage identity and access to your Microsoft Azure SQL database servers by using a Microsoft Entra administrator.

This rule resolution is part of the Conformity Security & Compliance tool for Azure.

Security

Microsoft Entra ID authentication represents an instrument that is used to connect to Microsoft Azure SQL databases and SQL data warehouses using identities available within Microsoft Entra ID. With Microsoft Entra ID authentication, identities of database users and other Microsoft services can be managed in one central location. Central ID management provides a single place to manage SQL database users and simplifies permission management. The Microsoft Entra ID authentication feature benefits include:

Providing a secure alternative to SQL database server authentication;

Help to reduce the proliferation of user identities across Azure SQL database servers;

Handling password rotation in a single place;

Eliminating the need for storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Microsoft Entra ID;

Allowing customers to manage database permissions using external Microsoft Entragroups;

Allowing token-based authentication for applications connecting to Azure SQL databases;

Providing support for ADFS (domain federation) or native user/password authentication for a local Microsoft Entra ID without domain synchronization;

Allowing connections from SQL Server Management Studio that use Microsoft Entra ID Universal Authentication, which includes Multi-Factor Authentication (MFA);

Allowing similar connections from SQL Server Data Tools (SSDT) that use Microsoft Entra ID Interactive Authentication.

Audit

To determine if a Microsoft Entra administrator is configured for SQL authentication within your Azure SQL database server settings, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers provisioned in your Azure account.

04 Click on the name of the SQL server that you want to examine.

05 In the navigation panel, under Settings, select Microsoft Entra admin to access the Microsoft Entra ID SQL authentication settings for the selected database server.

06 On the Microsoft Entra admin configuration page, check the Microsoft Entra admin feature status. If the status is currently set to No Microsoft Entra admin, there is no Microsoft Entra administrator configured to handle SQL authentication for the selected database server.

07 Repeat steps no. 4 – 6 for each SQL database server available in the selected subscription.

08 Repeat steps no. 3 – 7 for each subscription created in your Microsoft Azure cloud account.

Using Azure PowerShell

01 Run Get-AzSqlServer PowerShell cmdlet using custom query filters to list the names of all SQL database servers, available within the current Azure subscription: 

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName

02 The command output should return the requested SQL database server information: 

ServerName          ResourceGroupName
----------          -----------------
cc-prod-sql-server  cloud-shell-storage-westeurope
cc-app-db-server    cloud-shell-storage-westeurope

03 Run Get-AzureRmSqlServerActiveDirectoryAdministrator PowerShell command using the name of the SQL server that you want to examine as identifier parameter and custom query filters to describe the name of Microsoft Entra administrator configured to handle SQL authentication for the selected SQL server: 

Get-AzSqlServerActiveDirectoryAdministrator -ServerName "cc-prod-sql-server" -ResourceGroupName "cloud-shell-storage-westeurope" | Select-Object DisplayName

If Get-AzureRmSqlServerActiveDirectoryAdministrator cmdlet request is not returning an output, there is no Microsoft Entra administrator configured for SQL authentication to the selected SQL database server.

04 Repeat step no. 3 for each SQL database server provisioned in the current subscription.

05 Repeat steps no. 1 – 5 for each subscription available within your Microsoft Azure cloud account.

Remediation / Resolution

To configure an Microsoft Entra administrator for SQL authentication and access to your Microsoft Azure SQL database servers, perform the following actions:

Using Azure Console

01 Sign in to Azure Management Console.

02 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

03 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

04 Click on the name of the SQL database server that you want to reconfigure.

05 In the navigation panel, under Settings, select Microsoft Entra admin to access the Microsoft Entra ID SQL authentication settings for the selected database server.

06 On the Microsoft Entra admin configuration page, click Set admin to initiate the setup process.

07 On the Add admin panel, choose the Microsoft Entra administrator (or search it by the name/email address) that you want to configure for SQL authentication and access to your Azure SQL database server, then click Select to select the chosen Microsoft Entra admin user and return to the configuration page.

08 Click Save to apply the configuration changes.

09 Repeat steps no. 4 – 8 for each SQL database server available within the selected subscription.

10 Repeat steps no. 3 – 9 for each subscription created within your Microsoft Azure cloud account.

Using Azure CLI and PowerShell

01 Run Set-AzureRmSqlServerThreatDetectionPolicy PowerShell command using the name of the SQL server that you want to reconfigure and the name of the associated resource group as identifier parameters (see Audit section part I to identify the right Azure resources) to provision an Microsoft Entra admin for SQL authentication and access to the selected SQL database server, in the current Azure subscription. You can set up only one admin user at a time and this must be one of the following: native members of AAD, federated members of Microsoft Entra ID and imported members from other directories who are native or federated members. The Microsoft Entra groups created as security groups, Microsoft accounts such as those created for outlook.com, hotmail.com, or live.com domains, are not supported as administrators. Other guest accounts, such as those created for gmail.com domains, are not supported as administrators as well. For example, the following PowerShell command request configures an Microsoft Entra admin user named "Cloud Conformity Admin" (native member) for SQL authentication and access to an SQL database server identified by the name "cc-prod-sql-server": 

Set-AzSqlServerActiveDirectoryAdministrator -ServerName "cc-prod-sql-server" -ResourceGroupName "cloud-shell-storage-westeurope" -DisplayName "Cloud Conformity Admin"

02 The command output should return the PowerShell command request metadata: 

ResourceGroupName      ServerName         DisplayName           ObjectId
-----------------      ----------         -----------           --------
cloud-shell-storage-westeurope cc-prod-sql-server Cloud Conformity Admin abcdabcd-abcd-abcd-abcd-abcdabcdabcd

03 Repeat step no. 1 and 2 for each SQL database server provisioned in the selected subscription.

04 Repeat steps no. 1 – 3 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Jul 24, 2019

Related Sql rules