← Back to Microsoft Azure SQL Database Security Technical Implementation Guide

V-255336

CAT II (Medium)

Azure SQL Database must map the PKI-authenticated identity to an associated user account.

Rule ID

SV-255336r961044_rule

STIG

Microsoft Azure SQL Database Security Technical Implementation Guide

Version

V2R3

CCIs

CCI-000187

Discussion

The DOD standard for authentication is DOD-approved PKI certificates. Once a PKI certificate has been validated, it must be mapped to an Azure SQL Database user account for the authenticated identity to be meaningful to Azure SQL Database and useful for authorization decisions.

Check Content

To verify that Azure Active Directory is configured as the authentication type, use the following PowerShell commands: 

$LogicalServerName = "myServer" 
Get-AzSqlServer -ServerName $LogicalServerName | Get-AzSqlServerActiveDirectoryOnlyAuthentication

If AzureADOnlyAuthentication returns False, this is a finding.

Fix Text

To set the Azure Active Directory Administrator, use the following PowerShell command: 

$LogicalServerName = "myServer" 
Set-AzSqlServerActiveDirectoryAdministrator -ResourceGroupName "myResourceGroup" -ServerName $LogicalServerName -DisplayName "myAADIdentify"

Azure Active Directory Authentication can be enabled using either PowerShell or the Azure CLI.

To enable Azure Active Directory Authentication using PowerShell, use the commands below: 

######
###### Sets the AAD Admin in the SQL Server using PowerShell ######
######
$LogicalServerName = "myServer" 
$ResourceGroup = "myResourceGroup"
$DisplayName = "<AAD Principal>" 
$ObjectId = "<GUID for AAD Principal>"

Set-AzSqlServerActiveDirectoryAdministrator `
-ResourceGroupName $ResourceGroup `
-ServerName $LogicalServerName `
-DisplayName $DisplayName `
-ObjectId$ObjectId

#Sets AD Admin Only
Get-AzSqlServer -ServerName $LogicalServerName `
| Enable-AzSqlServerActiveDirectoryOnlyAuthentication

To enable Azure Active Directory Authentication using the Azure CLI, use the commands below:

######
###### Sets the AAD Admin in the SQL Server using the Azure CLI ######
######
az sql server ad-admin create `
--resource-group $ResourceGroup 
--server $LogicalServerName `
--display-name $DisplayName `
--object-id $ObjectId `

#Sets AD Admin Only
az sql server ad-only-auth enable `
--resource-group $ResourceGroup `
--name $LogicalServerName 

https://docs.microsoft.com/en-us/cli/azure/sql/server/ad-only-auth?view=azure-cli-latest
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell