azure.sql-server

SQL Server Resource

example:

This policy will find all SQL servers with average DTU consumption under 10 percent over the last 72 hours

policies:
  - name: sqlserver-under-utilized
    resource: azure.sqlserver
    filters:
      - type: metric
        metric: dtu_consumption_percent
        op: lt
        aggregation: average
        threshold: 10
        timeframe: 72
        filter: "ElasticPoolResourceId eq '*'"
        no_data_action: include

example:

This policy will find all SQL servers without any firewall rules defined.

policies:
  - name: find-sqlserver-without-firewall-rules
    resource: azure.sqlserver
    filters:
      - type: firewall-rules
        equal: []

example:

This policy will find all SQL servers allowing traffic from 1.2.2.128/25 CIDR.

policies:
  - name: find-sqlserver-allowing-subnet
    resource: azure.sqlserver
    filters:
      - type: firewall-rules
        include: ['1.2.2.128/25']

Filters

• advisor-recommendation

• auditing

• auditing-policies

• azure-ad-administrators

• cost

• diagnostic-settings

• event

• failover-group

• firewall-bypass

• firewall-rules

• list-item

• marked-for-op

• metric

• offhour

• onhour

• policy-compliant

• reduce

• resource-lock

• security-alert-policies

• transparent-data-encryption

• value

• vulnerability-assessment

advisor-recommendation

Filter resources by Azure Advisor Recommendations

Select all categories with ‘all’

example:

policies:
  - name: disks-with-cost-recommendations
    resource: azure.disk
    filters:
      - type: advisor-recommendation
        category: Cost
        key: '[].properties.recommendationTypeId'
        op: contains
        value: '48eda464-1485-4dcf-a674-d0905df5054a'

properties:
  category:
    type: string
  default:
    type: object
  key:
    type: string
  op:
    enum:
    - eq
    - equal
    - ne
    - not-equal
    - gt
    - greater-than
    - ge
    - gte
    - le
    - lte
    - lt
    - less-than
    - glob
    - regex
    - regex-case
    - in
    - ni
    - not-in
    - contains
    - difference
    - intersect
    - mod
  type:
    enum:
    - advisor-recommendation
  value:
    oneOf:
    - type: array
    - type: string
    - type: boolean
    - type: number
    - type: 'null'
  value_from:
    additionalProperties: 'False'
    properties:
      expr:
        oneOf:
        - type: integer
        - type: string
      format:
        enum:
        - csv
        - json
        - txt
        - csv2dict
      headers:
        patternProperties:
          ? ''
          : type: string
        type: object
      query:
        type: string
      url:
        type: string
    required:
    - url
    type: object
  value_path:
    type: string
  value_regex:
    type: string
  value_type:
    enum:
    - age
    - integer
    - expiration
    - normalize
    - size
    - cidr
    - cidr_size
    - swap
    - resource_count
    - expr
    - unique_size
    - date
    - version
    - float
required:
- category
- type

firewall-bypass

Filters resources by the firewall bypass rules.

example:

This policy will find all SQL Servers with enabled Azure Services bypass rules

policies:
  - name: sqlserver-bypass
    resource: azure.sqlserver
    filters:
      - type: firewall-bypass
        mode: equal
        list:
            - AzureServices

properties:
  list:
    items:
      enum:
      - AzureServices
    type: array
  mode:
    enum:
    - include
    - equal
    - any
    - only
  type:
    enum:
    - firewall-bypass
required:
- mode
- list
- type

transparent-data-encryption

Filter by the current Transparent Data Encryption configuration for this server.

example:

Find SQL Server with TDE details

policies:
  - name: sql-server-tde
    resource: azure.sql-server
    filters:
      - type: transparent-data-encryption
        key_type: CustomerManaged

properties:
  key_type:
    enum:
    - ServiceManaged
    - CustomerManaged
    type: string
  type:
    enum:
    - transparent-data-encryption
required:
- type
- key_type
- type

Actions

• auto-tag-date

• auto-tag-user

• delete

• lock

• logic-app

• mark-for-op

• notify

• set-firewall-rules

• tag

• tag-trim

• untag

• webhook

set-firewall-rules

Set Firewall Rules Action

Updates SQL Server Firewall configuration.

By default the firewall rules are replaced with the new values. The append flag can be used to force merging the new rules with the existing ones on the resource.

You may also reference azure public cloud Service Tags by name in place of an IP address. Use ServiceTags. followed by the name of any group from https://www.microsoft.com/en-us/download/details.aspx?id=56519.

- type: set-firewall-rules
      bypass-rules:
          - AzureServices
      ip-rules:
          - 11.12.13.0/16
          - ServiceTags.AppService.CentralUS

example:

Configure firewall to allow: - Azure Services - Two IP ranges

policies:
    - name: add-sql-server-firewall
      resource: azure.sqlserver
      actions:
        - type: set-firewall-rules
          bypass-rules:
              - AzureServices
          ip-rules:
              - 11.12.13.0/16
              - 21.22.23.24

properties:
  append:
    default: true
    type: boolean
  bypass-rules:
    items:
      enum:
      - AzureServices
    type: array
  ip-rules:
    items:
      type: string
    type: array
  prefix:
    maxLength: 91
    type: string
  type:
    enum:
    - set-firewall-rules
  virtual-network-rules:
    items:
      type: string
    type: array
required:
- type