Control: An Azure Active Directory administrator should be provisioned for SQL servers

powerpipe control run azure_compliance.control.sql_server_azure_ad_authentication_enabled

powerpipe login
powerpipe control run azure_compliance.control.sql_server_azure_ad_authentication_enabled --share

with sever_with_ad_admin as (
  select
    distinct a.id
  from
    azure_sql_server as a,
    jsonb_array_elements(server_azure_ad_administrator) as ad_admin
  where
    ad_admin ->> 'type' = 'Microsoft.Sql/servers/administrators'
)
select
  a.id as resource,
  case
    when s.id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when s.id is not null then a.name || ' azure AD authentication enabled.'
    else a.name || ' azure AD authentication disabled.'
  end as reason
  
  , a.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_server as a
  left join sever_with_ad_admin as s on a.id = s.id
  left join azure_subscription sub on sub.subscription_id = a.subscription_id;