Table: azure_sql_server

An Azure SQL server is a relational database management system. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).

Examples

List servers that have auditing disabled

select
  name,
  id,
  audit -> 'properties' ->> 'state' as audit_policy_state
from
  azure_sql_server,
  jsonb_array_elements(server_audit_policy) as audit
where
  audit -> 'properties' ->> 'state' = 'Disabled';

List servers with an audit log retention period less than 90 days

select
  name,
  id,
  (audit -> 'properties' ->> 'retentionDays')::integer as audit_policy_retention_days
from
  azure_sql_server,
  jsonb_array_elements(server_audit_policy) as audit
where
  (audit -> 'properties' ->> 'retentionDays')::integer < 90;

List servers that have advanced data security disabled

select
  name,
  id,
  security -> 'properties' ->> 'state' as security_alert_policy_state
from
  azure_sql_server,
  jsonb_array_elements(server_security_alert_policy) as security
where
  security -> 'properties' ->> 'state' = 'Disabled';

List servers that have Advanced Threat Protection types set to All

select
  name,
  id,
  security -> 'properties' -> 'disabledAlerts' as security_alert_policy_state
from
  azure_sql_server,
  jsonb_array_elements(server_security_alert_policy) as security,
  jsonb_array_elements_text(security -> 'properties' -> 'disabledAlerts') as disabled_alerts,
  jsonb_array_length(security -> 'properties' -> 'disabledAlerts') as alert_length
where
  alert_length = 1
  and disabled_alerts = '';

List servers that do not have an Active Directory admin set

select
  name,
  id
from
  azure_sql_server
where
  server_azure_ad_administrator is null;

List servers for which TDE protector is encrypted with the service-managed key

select
  name,
  id,
  encryption ->> 'kind' as encryption_protector_kind
from
  azure_sql_server,
  jsonb_array_elements(encryption_protector) as encryption
where
  encryption ->> 'kind' = 'servicemanaged';

Query examples

Control examples

.inspect azure_sql_server

Azure SQL Server

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
administrator_login	text	Specifies the username of the administrator for this server.
administrator_login_password	text	The administrator login password.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
cloud_environment	text	The Azure Cloud Environment.
encryption_protector	jsonb	The server encryption protector.
firewall_rules	jsonb	A list of firewall rules fro this server.
fully_qualified_domain_name	text	The fully qualified domain name of the server.
id	text	Contains ID to identify a SQL server uniquely.
kind	text	The Kind of sql server.
location	text	The resource location.
minimal_tls_version	text	Minimal TLS version. Allowed values: '1.0', '1.1', '1.2'.
name	text	The friendly name that identifies the SQL server.
private_endpoint_connections	jsonb	The private endpoint connections of the sql server.
public_network_access	text	Whether or not public endpoint access is allowed for this server.
region	text	The Azure region/location in which the resource is located.
resource_group	text	The resource group which holds this resource.
server_audit_policy	jsonb	Specifies the audit policy configuration for server.
server_azure_ad_administrator	jsonb	Specifies the active directory administrator.
server_security_alert_policy	jsonb	Specifies the security alert policy configuration for server.
server_vulnerability_assessment	jsonb	Specifies the server's vulnerability assessment.
state	text	The state of the server.
subscription_id	text	The Azure Subscription ID in which the resource is located.
tags	jsonb	A map of tags for the resource.
tags_src	jsonb	Specifies the set of tags attached to the server.
title	text	Title of the resource.
type	text	The resource type of the SQL server.
version	text	The version of the server.
virtual_network_rules	jsonb	A list of virtual network rules for this server.

turbot/azure