steampipe plugin install azuresteampipe plugin install azure
azure_ad_groupazure_ad_service_principalazure_ad_userazure_alert_managementazure_api_managementazure_api_management_backendazure_app_configurationazure_app_service_environmentazure_app_service_function_appazure_app_service_planazure_app_service_web_appazure_app_service_web_app_slotazure_application_gatewayazure_application_insightazure_application_security_groupazure_automation_accountazure_automation_variableazure_bastion_hostazure_batch_accountazure_cognitive_accountazure_compute_availability_setazure_compute_diskazure_compute_disk_accessazure_compute_disk_encryption_setazure_compute_disk_metric_read_opsazure_compute_disk_metric_read_ops_dailyazure_compute_disk_metric_read_ops_hourlyazure_compute_disk_metric_write_opsazure_compute_disk_metric_write_ops_dailyazure_compute_disk_metric_write_ops_hourlyazure_compute_imageazure_compute_resource_skuazure_compute_snapshotazure_compute_ssh_keyazure_compute_virtual_machineazure_compute_virtual_machine_metric_cpu_utilizationazure_compute_virtual_machine_metric_cpu_utilization_dailyazure_compute_virtual_machine_metric_cpu_utilization_hourlyazure_compute_virtual_machine_scale_setazure_compute_virtual_machine_scale_set_network_interfaceazure_compute_virtual_machine_scale_set_vmazure_consumption_usageazure_container_groupazure_container_registryazure_cosmosdb_accountazure_cosmosdb_mongo_collectionazure_cosmosdb_mongo_databaseazure_cosmosdb_restorable_database_accountazure_cosmosdb_sql_databaseazure_data_factoryazure_data_factory_datasetazure_data_factory_pipelineazure_data_lake_analytics_accountazure_data_lake_storeazure_databox_edge_deviceazure_databricks_workspaceazure_diagnostic_settingazure_dns_zoneazure_eventgrid_domainazure_eventgrid_topicazure_eventhub_namespaceazure_express_route_circuitazure_firewallazure_firewall_policyazure_frontdoorazure_hdinsight_clusterazure_healthcare_serviceazure_hpc_cacheazure_hybrid_compute_machineazure_hybrid_kubernetes_connected_clusterazure_iothubazure_iothub_dpsazure_key_vaultazure_key_vault_deleted_vaultazure_key_vault_keyazure_key_vault_key_versionazure_key_vault_managed_hardware_security_moduleazure_key_vault_secretazure_kubernetes_clusterazure_kubernetes_service_versionazure_kusto_clusterazure_lbazure_lb_backend_address_poolazure_lb_nat_ruleazure_lb_outbound_ruleazure_lb_probeazure_lb_ruleazure_locationazure_log_alertazure_log_profileazure_logic_app_workflowazure_machine_learning_workspaceazure_management_groupazure_management_lockazure_mariadb_serverazure_monitor_activity_log_eventazure_monitor_log_profileazure_mssql_elasticpoolazure_mssql_managed_instanceazure_mssql_virtual_machineazure_mysql_flexible_serverazure_mysql_serverazure_nat_gatewayazure_network_interfaceazure_network_security_groupazure_network_watcherazure_network_watcher_flow_logazure_policy_assignmentazure_policy_definitionazure_postgresql_flexible_serverazure_postgresql_serverazure_private_dns_zoneazure_providerazure_public_ipazure_recovery_services_backup_jobazure_recovery_services_vaultazure_redis_cacheazure_resource_groupazure_resource_linkazure_role_assignmentazure_role_definitionazure_route_tableazure_search_serviceazure_security_center_auto_provisioningazure_security_center_automationazure_security_center_contactazure_security_center_jit_network_access_policyazure_security_center_settingazure_security_center_sub_assessmentazure_security_center_subscription_pricingazure_service_fabric_clusterazure_servicebus_namespaceazure_signalr_serviceazure_spring_cloud_serviceazure_sql_databaseazure_sql_serverazure_storage_accountazure_storage_blobazure_storage_blob_serviceazure_storage_containerazure_storage_queueazure_storage_share_fileazure_storage_syncazure_storage_tableazure_storage_table_serviceazure_stream_analytics_jobazure_subnetazure_subscriptionazure_synapse_workspaceazure_tenantazure_virtual_networkazure_virtual_network_gateway

Table: azure_ad_service_principal - Query Azure Active Directory Service Principals using SQL

An Azure Active Directory Service Principal is a security identity used by user-created applications, services, and automation tools to access specific Azure resources. It allows these resources to be secured by using Azure AD role-based access control. This identity is used to authenticate to Azure AD and obtain tokens to access resources.

Table Usage Guide

The azure_ad_service_principal table provides insights into Service Principals within Azure Active Directory. As a Security Engineer, utilize this table to explore details about service principals, including their app roles, display names, and associated metadata. Use it to uncover information about service principals, such as those with specific permissions, their associated application IDs, and the verification of OAuth2 permissions.

Examples

List of ad service principals where service principal account is disabled

Determine the areas in which Azure ad service principals are disabled. This can be useful for identifying potential security risks or troubleshooting access issues.

select
object_id,
object_type,
display_name,
account_enabled
from
azure_ad_service_principal
where
not account_enabled;
select
object_id,
object_type,
display_name,
account_enabled
from
azure_ad_service_principal
where
account_enabled = 0;

List of ad service principals where app role assignment is not required

Identify instances where ad service principals in Azure do not require an app role assignment. This can be useful to streamline access control and reduce unnecessary role assignments.

select
object_id,
display_name,
app_role_assignment_required
from
azure_ad_service_principal
where
not app_role_assignment_required;
select
object_id,
display_name,
app_role_assignment_required
from
azure_ad_service_principal
where
app_role_assignment_required = 0;

Application role info of service principals

Explore the roles assigned to service principals within your Azure Active Directory. This query helps in understanding the permissions and access controls for each service principal, thereby assisting in maintaining secure and efficient system operations.

select
object_id,
approle ->> 'allowedMemberTypes' as allowed_member_types,
approle ->> 'description' as description,
approle ->> 'displayName' as display_name,
approle -> 'isEnabled' as isEnabled,
approle ->> 'id' as id,
approle ->> 'value' as id
from
azure_ad_service_principal
cross join jsonb_array_elements(app_roles) as approle;
select
object_id,
json_extract(approle.value, '$.allowedMemberTypes') as allowed_member_types,
json_extract(approle.value, '$.description') as description,
json_extract(approle.value, '$.displayName') as display_name,
json_extract(approle.value, '$.isEnabled') as isEnabled,
json_extract(approle.value, '$.id') as id,
json_extract(approle.value, '$.value') as id
from
azure_ad_service_principal,
json_each(app_roles) as approle;

Oauth 2.0 permission info of ad service principal

This query is useful for gaining insights into the permissions associated with your Azure advertising service principal. It allows you to assess whether certain permissions are enabled and understand their specific descriptions and display names, helping to maintain proper access control in your Azure environment.

select
object_id,
perm ->> 'adminConsentDescription' as admin_consent_description,
perm ->> 'adminConsentDisplayName' as admin_consent_display_ame,
perm ->> 'id' as id,
perm ->> 'isEnabled' as is_enabled,
perm ->> 'type' as type,
perm ->> 'value' as value
from
azure_ad_service_principal
cross join jsonb_array_elements(oauth2_permissions) as perm;
select
object_id,
json_extract(perm.value, '$.adminConsentDescription') as admin_consent_description,
json_extract(perm.value, '$.adminConsentDisplayName') as admin_consent_display_name,
json_extract(perm.value, '$.id') as id,
json_extract(perm.value, '$.isEnabled') as is_enabled,
json_extract(perm.value, '$.type') as type,
json_extract(perm.value, '$.value') as value
from
azure_ad_service_principal,
json_each(oauth2_permissions) as perm;

Schema for azure_ad_service_principal

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_enabledbooleanIndicates whether or not the service principal account is enabled.
additional_propertiesjsonbA list of unmatched properties from the message are deserialized this collection.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
alternative_namesjsonbA list of alternative names.
app_role_assignment_requiredbooleanSpecifies whether an AppRoleAssignment to a user or group is required before Azure AD will issue a user or access token to the application.
app_rolesjsonbA list of application roles that an application may declare. These roles can be assigned to users, groups or service principals.
deletion_timestamptimestamp with time zoneThe time at which the directory object was deleted.
display_nametextA friendly name that identifies a service principal.
error_urltextAn URL provided by the author of the associated application to report errors when using the application.
homepagetextThe URL to the homepage of the associated application.
key_credentialsjsonbA list of key credentials associated with the service principal.
logout_urltextAn URL provided by the author of the associated application to logout.
oauth2_permissionsjsonbThe OAuth 2.0 permissions exposed by the associated application.
object_idtextThe unique ID that identifies a service principal.
object_typetextA string that identifies the object type.
password_credentialsjsonbA list of password credentials associated with the service principal.
reply_urlsjsonbThe URLs that user tokens are sent to for sign in with the associated application. The redirect URIs that the oAuth 2.0 authorization code and access tokens are sent to for the associated application.
saml_metadata_urltextThe URL to the SAML metadata of the associated application.
service_principal_namesjsonbA list of service principal names.
titletextTitle of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_ad_service_principal