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

Use Azure Active Directory Admin for SQL Authentication

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

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

Ensure that Azure Active Directory authentication is configured to allow you to centrally manage identity and access to your Microsoft Azure SQL database servers by using an Active Directory administrator.

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

Security

Azure Active Directory (AAD) authentication represents an instrument that is used to connect to Microsoft Azure SQL databases and SQL data warehouses using identities available within Active Directory. With Azure AAD 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 AAD 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 Azure Active Directory (AAD);

Allowing customers to manage database permissions using external AAD groups;

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 Azure Active Directory without domain synchronization;

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

Allowing similar connections from SQL Server Data Tools (SSDT) that use Active Directory Interactive Authentication.

Audit

To determine if an Active Directory 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 Active Directory admin to access the Azure Active Directory (AAD) SQL authentication settings for the selected database server.

06 On the Active Directory admin configuration page, check the Active Directory admin feature status. If the status is currently set to No Active Directory admin, there is no Active Directory 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 Azure Active Directory 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 Azure Active Directory 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 Azure Active Directory (AAD) 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 Active Directory admin to access the Active Directory SQL authentication settings for the selected database server.

06 On the Active Directory admin configuration page, click Set admin to initiate the setup process.

07 On the Add admin panel, choose the Azure Active Directory (AAD) 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 AD 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 Azure Active Directory 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 AAD and imported members from other directories who are native or federated members. The Azure Active Directory (AAD) 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 AAD 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

Unlock the Remediation Steps

Free 30-day Trial

Automatically audit your configurations with Conformity
and gain access to our cloud security platform.

Confirmity Cloud Platform

No thanks, back to article

You are auditing:

Use Azure Active Directory Admin for SQL Authentication

Risk Level: Medium