Table: azure_mssql_managed_instance - Query Azure SQL Managed Instances using SQL

Azure SQL Managed Instance is a fully managed SQL Server Database Engine hosted in Azure cloud. It provides most of the SQL Server's capabilities, allowing you to migrate SQL server workloads to Azure with minimal changes. With built-in intelligence that learns your unique database patterns and adaptive performance tuning based on AI, SQL Managed Instance is a best-in-class database service.

Table Usage Guide

The azure_mssql_managed_instance table provides insights into Azure SQL Managed Instances. As a DBA or a cloud architect, you can explore specific details about these instances, including their settings, configurations, and health status. Use this table to monitor and manage your SQL instances effectively, ensuring optimal performance and resource utilization.

Examples

Basic info

Explore the status and security settings of your managed instances in Azure's SQL service. This query is useful for understanding the licensing and encryption standards used across your instances, helping you maintain compliance and security in your database management.

select
  name,
  id,
  state,
  license_type,
  minimal_tls_version
from
  azure_mssql_managed_instance;

select
  name,
  id,
  state,
  license_type,
  minimal_tls_version
from
  azure_mssql_managed_instance;

List managed instances with public endpoint enabled

Identify instances where Azure's managed SQL servers have their public data endpoint enabled. This helps in assessing the elements within your setup that might be exposed to potential security risks.

select
  name,
  id,
  state,
  license_type,
  minimal_tls_version
from
  azure_mssql_managed_instance
where
  public_data_endpoint_enabled;

select
  name,
  id,
  state,
  license_type,
  minimal_tls_version
from
  azure_mssql_managed_instance
where
  public_data_endpoint_enabled = 1;

List security alert policies of the managed instances

Explore the security alert policies of managed instances to understand their configurations, such as creation time, disabled alerts, and retention days. This can help in assessing the security measures in place and identifying areas for improvement.

select
  name,
  id,
  policy -> 'creationTime' as policy_creation_time,
  jsonb_pretty(policy -> 'disabledAlerts') as policy_disabled_alerts,
  policy -> 'emailAccountAdmins' as policy_email_account_admins,
  jsonb_pretty(policy -> 'emailAddresses') as policy_email_addresses,
  policy ->> 'id' as policy_id,
  policy ->> 'name' as policy_name,
  policy -> 'retentionDays' as policy_retention_days,
  policy ->> 'state' as policy_state,
  policy ->> 'storageAccountAccessKey' as policy_storage_account_access_key,
  policy ->> 'storageEndpoint' as policy_storage_endpoint,
  policy ->> 'type' as policy_type
from
  azure_mssql_managed_instance,
  jsonb_array_elements(security_alert_policies) as policy;

select
  name,
  i.id,
  json_extract(policy.value, '$.creationTime') as policy_creation_time,
  json_extract(policy.value, '$.disabledAlerts') as policy_disabled_alerts,
  json_extract(policy.value, '$.emailAccountAdmins') as policy_email_account_admins,
  json_extract(policy.value, '$.emailAddresses') as policy_email_addresses,
  json_extract(policy.value, '$.id') as policy_id,
  json_extract(policy.value, '$.name') as policy_name,
  json_extract(policy.value, '$.retentionDays') as policy_retention_days,
  json_extract(policy.value, '$.state') as policy_state,
  json_extract(policy.value, '$.storageAccountAccessKey') as policy_storage_account_access_key,
  json_extract(policy.value, '$.storageEndpoint') as policy_storage_endpoint,
  json_extract(policy.value, '$.type') as policy_type
from
  azure_mssql_managed_instance as i,
  json_each(security_alert_policies) as policy;

Control examples

Schema for azure_mssql_managed_instance

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
administrator_login	text		Administrator username for the managed instance.
administrator_login_password	text		Administrator password for the managed instance.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
cloud_environment	text		The Azure Cloud Environment.
collation	text		Collation of the managed instance.
dns_zone	text		The Dns zone that the managed instance is in.
dns_zone_partner	text		The resource id of another managed instance whose DNS zone this managed instance will share after creation.
encryption_protectors	jsonb		The managed instance encryption protectors.
fully_qualified_domain_name	text		The fully qualified domain name of the managed instance.
id	text		Contains ID to identify a managed instance uniquely.
identity	jsonb		The azure active directory identity of the managed instance.
instance_pool_id	text		The Id of the instance pool this managed server belongs to.
license_type	text		The license type of the managed instance.
maintenance_configuration_id	text		Specifies maintenance configuration id to apply to this managed instance.
managed_instance_create_mode	text		Specifies the mode of database creation.
minimal_tls_version	text		Minimal TLS version of the managed instance.
name	text	=	The friendly name that identifies the managed instance.
proxy_override	text		Connection type used for connecting to the instance.
public_data_endpoint_enabled	boolean		Whether or not the public data endpoint is enabled.
region	text		The Azure region/location in which the resource is located.
resource_group	text	=	The resource group which holds this resource.
restore_point_in_time	timestamp with time zone		Specifies the point in time of the source database that will be restored to create the new database.
security_alert_policies	jsonb		The security alert policies of the managed instance.
sku	jsonb		Managed instance SKU.
source_managed_instance_id	text		The resource identifier of the source managed instance associated with create operation of this instance.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text		The state of the managed instance.
storage_size_in_gb	bigint		The managed instance storage size in GB.
subnet_id	text		Subnet resource ID for the managed instance.
subscription_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Subscription ID in which the resource is located.
tags	jsonb		A map of tags for the resource.
timezone_id	text		Id of the timezone.
title	text		Title of the resource.
type	text		The resource type of the managed instance.
v_cores	bigint		The number of vcores of the managed instance.
vulnerability_assessments	jsonb		The managed instance vulnerability assessments.

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_mssql_managed_instance