AD FS Database Local SQL Statements

id: 81fab62b-ef92-487a-9c35-a91a116309e6
name: AD FS Database Local SQL Statements
description: |
  'This hunting query uses Application events from the "MSSQL$MICROSOFT##WID" provider to collect SQL statements run against an AD FS database (e.g Windows Internal Database (WID)).
  A threat actor might want to extract the AD FS data configuration settings with a SQL statement or modify it with an UPDATE SQL statement.
  In order to use this query you need to create a server audit and database audit specification on your AD FS database.
  Reference: https://github.com/Azure/SimuLand/blob/main/3_simulate_detect/credential-access/exportADFSTokenSigningCertificate.md
  Reference: https://o365blog.com/post/adfs/
  '
severity: Medium
requiredDataConnectors: []
tactics:
  - Collection
relevantTechniques:
  - T1005
tags:
  - SimuLand
query: |
  Event
  | where EventLog =~ 'Application'
  | where Source == 'MSSQL$MICROSOFT##WID' and EventID == '33205'
  | where RenderedDescription has_all ('database_name:AdfsConfigurationV4','schema_name:IdentityServerPolicy','object_name:ServiceSettings')
  | extend action_id = extract("action_id:([\\S]+)", 1, RenderedDescription)
  | extend session_server_principal_name = extract("session_server_principal_name:([\\S]+)", 1, RenderedDescription)
  | extend server_principal_name = extract("session_server_principal_name:([\\S]+)", 1, RenderedDescription)
  | extend HostCustomEntity = Computer, AccountCustomEntity = split(server_principal_name, '\\')[1]
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
  - entityType: Host
    fieldMappings:
      - identifier: FullName
        columnName: HostCustomEntity
version: 1.0.0