---
policy: /ce/ca/azure/sql-database/server-auditing
logic: /ce/ca/azure/sql-database/server-auditing/prod.logic.yaml
executionTime: 2025-04-24T23:45:48.43627662Z
rows:
  - id: test1
    match: true
    status:
      expected: DISAPPEARED
      actual: DISAPPEARED
    conditionIndex:
      expected: 99
      actual: 99
    conditionText:
      expected: isDisappeared(CA10__disappearanceTime__c)
      actual: isDisappeared(CA10__disappearanceTime__c)
    runtimeError: {}
  - id: test2
    match: true
    status:
      expected: INCOMPLIANT
      actual: INCOMPLIANT
    conditionIndex:
      expected: 199
      actual: 199
    conditionText:
      expected: extract('CA10__auditing__c') == 'Disabled'
      actual: extract('CA10__auditing__c') == 'Disabled'
    runtimeError: {}
  - id: test3
    match: true
    status:
      expected: COMPLIANT
      actual: COMPLIANT
    conditionIndex:
      expected: 299
      actual: 299
    conditionText:
      expected: extract('CA10__auditing__c') == 'Enabled'
      actual: extract('CA10__auditing__c') == 'Enabled'
    runtimeError: {}
  - id: test4
    match: true
    status:
      expected: UNDETERMINED
      actual: UNDETERMINED
    conditionIndex:
      expected: 101
      actual: 101
    conditionText:
      expected: CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()
      actual: CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()
    runtimeError: {}
usedFiles:
  - path: /ce/ca/azure/sql-database/server-auditing/policy.yaml
    md5Hash: FAF43B5189C2DD649FAC8B3BEE46AB9B
    content: |
      ---
      names:
        full: Azure SQL Server Auditing is not enabled
        contextual: Server Auditing is not enabled
      description: Auditing tracks database events and writes them to an audit log in the
        Azure storage account. Enabling auditing at the server level ensures that all existing
        and newly created databases on the SQL server instance are audited.
      type: COMPLIANCE_POLICY
      categories:
        - RELIABILITY
        - SECURITY
      frameworkMappings:
        - "/frameworks/cis-azure-v2.1.0/04/01/01"
        - "/frameworks/cis-azure-v3.0.0/05/01/01"
        - "/frameworks/cloudaware/logging-and-monitoring/logging-and-monitoring-configuration"
      similarPolicies:
        internal:
          - dec-x-36ced3d1
        cloudConformity:
          - url: https://www.trendmicro.com/cloudoneconformity/knowledge-base/azure/Sql/auditing.html
            name: Enable Auditing for SQL Servers
  - path: /ce/ca/azure/sql-database/server-auditing/prod.logic.yaml
    md5Hash: EDBFBB2DAFF8B871B084FC01C6F1D92E
    content: |-
      ---
      inputType: "CA10__CaAzureSqlServer__c"
      testData:
        - file: "test-data.json"
      importExtracts:
        - file: "/types/CA10__CaAzureSqlServer__c/object.extracts.yaml"
      conditions:
        - status: "INCOMPLIANT"
          currentStateMessage: "SQL Server auditing is disabled."
          remediationMessage: "Consider enabling SQL Server auditing."
          check:
            IS_EQUAL:
              left:
                EXTRACT: "CA10__auditing__c"
              right:
                TEXT: "Disabled"
        - status: "COMPLIANT"
          currentStateMessage: "SQL Server auditing is enabled."
          check:
            IS_EQUAL:
              left:
                EXTRACT: "CA10__auditing__c"
              right:
                TEXT: "Enabled"
      otherwise:
        status: "UNDETERMINED"
        currentStateMessage: "Unexpected value in the field."
  - path: /ce/ca/azure/sql-database/server-auditing/test-data.json
    md5Hash: 91EE3E0C8CE9A55D19FE187A761E680E
    content: |-
      [
          {
              "expectedResult": {
                  "status": "DISAPPEARED",
                  "conditionIndex": "99",
                  "conditionText": "isDisappeared(CA10__disappearanceTime__c)",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-12T19:31:46Z"
              },
              "Id": "test1",
              "CA10__disappearanceTime__c": "2024-06-12T19:31:46Z",
              "CA10__auditing__c": "Enabled"
          },
          {
              "expectedResult": {
                  "status": "INCOMPLIANT",
                  "conditionIndex": "199",
                  "conditionText": "extract('CA10__auditing__c') == 'Disabled'",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-12T19:31:46Z"
              },
              "Id": "test2",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "Disabled"
          },
          {
              "expectedResult": {
                  "status": "COMPLIANT",
                  "conditionIndex": "299",
                  "conditionText": "extract('CA10__auditing__c') == 'Enabled'",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-12T19:31:46Z"
              },
              "Id": "test3",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": "Enabled"
          },
          {
              "expectedResult": {
                  "status": "UNDETERMINED",
                  "conditionIndex": "101",
                  "conditionText": "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()",
                  "runtimeError": null
              },
              "context": {
                  "snapshotTime": "2024-06-12T19:31:46Z"
              },
              "Id": "test4",
              "CA10__disappearanceTime__c": null,
              "CA10__auditing__c": ""
          }
      ]
  - path: /types/CA10__CaAzureSqlServer__c/object.extracts.yaml
    md5Hash: E77808BBEDF31D54E009EEA59AC5EF4B
    content: "---\nextracts:\n# Values: Enabled, Disabled. Not Nullable.\n  - name:\
      \ \"CA10__auditing__c\"\n    value: \n      FIELD:\n        path: \"CA10__auditing__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__auditing__c\"\n            currentStateMessage: \"Unable to determine\
      \ Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read\"\
      \n          isEmpty: \"SQL Server auditing settings are not populated yet\"\n\
      # Number field\n  - name: \"CA10__auditingRetentionDays__c\"\n    value: \n\
      \      FIELD:\n        path: \"CA10__auditingRetentionDays__c\"\n        undeterminedIf:\n\
      \          noAccessDelegate:\n            path: \"CA10__auditingRetentionDays__c\"\
      \n            currentStateMessage: \"Unable to determine Server auditing settings.\
      \ Possible permission issue with Microsoft.Sql/servers/auditingSettings/read\"\
      \n# Values: servicemanaged, azurekeyvault. Not Nullable. \n  - name: \"CA10__encryptionProtectorKind__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__encryptionProtectorKind__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__encryptionProtectorKind__c\"\n            currentStateMessage: \"Unable\
      \ to determine Server Encryption Protectors. Possible permission issue with\
      \ Microsoft.Sql/servers/encryptionProtector/read\"\n          isEmpty: \"SQL\
      \ Server Encryption Protectors are not populated yet\"\n# Values: ServiceManaged,\
      \ AzureKeyVault. Not Nullable. \n  - name: \"CA10__encryptionProtectorServerKeyType__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__encryptionProtectorServerKeyType__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__encryptionProtectorServerKeyType__c\"\n            currentStateMessage:\
      \ \"Unable to determine Server Encryption Protectors. Possible permission issue\
      \ with Microsoft.Sql/servers/encryptionProtector/read\"\n          isEmpty:\
      \ \"SQL Server Encryption Protectors are not populated yet\"\n# Nullable. \n\
      \  - name: \"CA10__encryptionProtectorUri__c\"\n    value: \n      FIELD:\n\
      \        path: \"CA10__encryptionProtectorUri__c\"\n        undeterminedIf:\n\
      \          noAccessDelegate:\n            path: \"CA10__encryptionProtectorUri__c\"\
      \n            currentStateMessage: \"Unable to determine Server Encryption Protectors.\
      \ Possible permission issue with Microsoft.Sql/servers/encryptionProtector/read\"\
      \n# Values: ActiveDirectory, null. Nullable. \n  - name: \"CA10__activeDirectoryAdminType__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__activeDirectoryAdminType__c\"\
      \n        undeterminedIf:\n          noAccessDelegate:\n            path: \"\
      CA10__activeDirectoryAdminType__c\"\n            currentStateMessage: \"Unable\
      \ to determine Server AD Administrator type. Possible permission issue with\
      \ Microsoft.Sql/servers/administrators\"\n  - name: \"CA10__firewallRulesJson__c\"\
      \n    value: \n      FIELD:\n        path: \"CA10__firewallRulesJson__c\"\n\
      \        returnType: BYTES\n        undeterminedIf:\n          noAccessDelegate:\n\
      \            path: \"CA10__firewallRulesJson__c\"\n            currentStateMessage:\
      \ \"Unable to determine Firewall Rules. Possible permission issue with Microsoft.Sql/servers/firewallRules/read\"\
      \n  - name: \"caJsonFrom__firewallRulesJson__c\"\n    value: \n      JSON_FROM:\n\
      \        arg: \n          EXTRACT: \"CA10__firewallRulesJson__c\"\n        undeterminedIf:\n\
      \          isInvalid: \"Firewall Rules JSON is invalid.\"\n# Nullable. Values:\
      \ Enabled, Disabled, Can't have no access, retrieved via Microsoft.Sql/servers\n\
      \  - name: \"CA10__publicNetworkAccess__c\"\n    value: \n      FIELD:\n   \
      \     path: \"CA10__publicNetworkAccess__c\"\n"
script: |-
  CREATE TEMP FUNCTION mock_ExpectedResult()
  RETURNS ARRAY<STRUCT<
          Id STRING,
          expectedResult STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, runtimeError STRING>
      >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "Id" : "test1",
    "expectedResult" : {
      "status" : "DISAPPEARED",
      "conditionIndex" : "99",
      "conditionText" : "isDisappeared(CA10__disappearanceTime__c)",
      "runtimeError" : null
    }
  }, {
    "Id" : "test2",
    "expectedResult" : {
      "status" : "INCOMPLIANT",
      "conditionIndex" : "199",
      "conditionText" : "extract('CA10__auditing__c') == 'Disabled'",
      "runtimeError" : null
    }
  }, {
    "Id" : "test3",
    "expectedResult" : {
      "status" : "COMPLIANT",
      "conditionIndex" : "299",
      "conditionText" : "extract('CA10__auditing__c') == 'Enabled'",
      "runtimeError" : null
    }
  }, {
    "Id" : "test4",
    "expectedResult" : {
      "status" : "UNDETERMINED",
      "conditionIndex" : "101",
      "conditionText" : "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()",
      "runtimeError" : null
    }
  } ];
  """;

  CREATE TEMP FUNCTION mock_CA10__CaAzureSqlServer__c()
  RETURNS ARRAY<STRUCT<
      Id STRING,
      CA10__disappearanceTime__c TIMESTAMP,
      CA10__auditing__c STRING,
      context STRUCT<
          snapshotTime TIMESTAMP
      >
  >>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
    return [ {
    "context" : {
      "snapshotTime" : new Date("2024-06-12T19:31:46Z")
    },
    "Id" : "test1",
    "CA10__disappearanceTime__c" : new Date("2024-06-12T19:31:46Z"),
    "CA10__auditing__c" : "Enabled"
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-12T19:31:46Z")
    },
    "Id" : "test2",
    "CA10__auditing__c" : "Disabled"
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-12T19:31:46Z")
    },
    "Id" : "test3",
    "CA10__auditing__c" : "Enabled"
  }, {
    "context" : {
      "snapshotTime" : new Date("2024-06-12T19:31:46Z")
    },
    "Id" : "test4",
    "CA10__auditing__c" : ""
  } ];
  """;

  CREATE TEMP FUNCTION process_CA10__CaAzureSqlServer__c(
      obj STRUCT<
          Id STRING,
          CA10__disappearanceTime__c TIMESTAMP,
          CA10__auditing__c STRING
      >,
      snapshotTime TIMESTAMP
  )
  RETURNS STRUCT<status STRING, conditionIndex DECIMAL, conditionText STRING, currentStateMessage STRING, currentStateReferences STRING, remediation STRING, runtimeError STRING>
  DETERMINISTIC
  LANGUAGE js
  AS r"""
      var TextLib = new function () {
          this.normalize = function(arg) {
              return arg == null ? '' : arg.replace(/\s+/g, ' ').trim().toLowerCase();
          };
          this.isEmpty = function(arg) {
              return this.normalize(arg) == '';
          };
          this.isNotEmpty = function(arg) {
              return this.normalize(arg) != '';
          };
          this.equal = function(left, right) {
              return this.normalize(left) == this.normalize(right);
          };
          this.notEqual = function(left, right) {
              return this.normalize(left) != this.normalize(right);
          };
          this.startsWith = function(arg, substring) {
              return this.normalize(arg).startsWith(this.normalize(substring));
          };
          this.endsWith = function(arg, substring) {
              return this.normalize(arg).endsWith(this.normalize(substring));
          };
          this.contains = function(arg, substring) {
              return this.normalize(arg).includes(this.normalize(substring));
          };
      }();
      var references1 = [];
      // condition[0], conditionIndex:[0..99]
      references1.push('Deleted From Azure [CA10__disappearanceTime__c]: ' + obj.CA10__disappearanceTime__c);
      if (obj.CA10__disappearanceTime__c != null) {
          return {status: 'DISAPPEARED', conditionIndex: 99, conditionText: "isDisappeared(CA10__disappearanceTime__c)", currentStateMessage: "Object is deleted in the source", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
      }
      // condition[1], conditionIndex:[100..199]
      function fieldChecked4() {
          if (TextLib.isEmpty(obj.CA10__auditing__c)) {
              throw new Error("UNDETERMINED condition:101", {cause: {status: 'UNDETERMINED', conditionIndex: 101, conditionText: "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()", currentStateMessage: "Unable to determine Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          if (TextLib.isEmpty(obj.CA10__auditing__c)) {
              throw new Error("UNDETERMINED condition:102", {cause: {status: 'UNDETERMINED', conditionIndex: 102, conditionText: "CA10__auditing__c.isEmpty()", currentStateMessage: "SQL Server auditing settings are not populated yet", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          return obj.CA10__auditing__c;
      }
      function extract3() { if (!this.out) { this.out = fieldChecked4(); } return this.out; };
      references1.push('Auditing [obj.CA10__auditing__c]: ' + obj.CA10__auditing__c);
      try {
          if (TextLib.equal(extract3.call(extract3), 'Disabled')) {
              return {status: 'INCOMPLIANT', conditionIndex: 199, conditionText: "extract('CA10__auditing__c') == 'Disabled'", currentStateMessage: "SQL Server auditing is disabled.", currentStateReferences: references1.join('\n'), remediation: "Consider enabling SQL Server auditing.", runtimeError: null};
          }
      } catch (err) {
          if (err.cause && err.cause.status) { return err.cause; } else { throw err; }
      }
      // condition[2], conditionIndex:[200..299]
      function fieldChecked7() {
          if (TextLib.isEmpty(obj.CA10__auditing__c)) {
              throw new Error("UNDETERMINED condition:201", {cause: {status: 'UNDETERMINED', conditionIndex: 201, conditionText: "CA10__auditing__c.delegatedTo(CA10__auditing__c).isEmpty()", currentStateMessage: "Unable to determine Server auditing settings. Possible permission issue with Microsoft.Sql/servers/auditingSettings/read", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          if (TextLib.isEmpty(obj.CA10__auditing__c)) {
              throw new Error("UNDETERMINED condition:202", {cause: {status: 'UNDETERMINED', conditionIndex: 202, conditionText: "CA10__auditing__c.isEmpty()", currentStateMessage: "SQL Server auditing settings are not populated yet", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null}});
          }
          return obj.CA10__auditing__c;
      }
      function extract6() { if (!this.out) { this.out = fieldChecked7(); } return this.out; };
      try {
          if (TextLib.equal(extract6.call(extract6), 'Enabled')) {
              return {status: 'COMPLIANT', conditionIndex: 299, conditionText: "extract('CA10__auditing__c') == 'Enabled'", currentStateMessage: "SQL Server auditing is enabled.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
          }
      } catch (err) {
          if (err.cause && err.cause.status) { return err.cause; } else { throw err; }
      }
      return {status: 'UNDETERMINED', conditionIndex: 300, conditionText: "otherwise", currentStateMessage: "Unexpected value in the field.", currentStateReferences: references1.join('\n'), remediation: null, runtimeError: null};
  """;


  SELECT
    expectedResult.Id as Id,
    IF (
      IFNULL(expectedResult.expectedResult.status, '') = IFNULL(sObject.result.status, '')
      AND IFNULL(expectedResult.expectedResult.conditionIndex, -1) = IFNULL(sObject.result.conditionIndex, -1)
      AND IFNULL(expectedResult.expectedResult.conditionText, '') = IFNULL(sObject.result.conditionText, '')
      AND IFNULL(expectedResult.expectedResult.runtimeError, '') = IFNULL(sObject.result.runtimeError, ''),
      "MATCH",
      "FAIL"
    ) as match,
    expectedResult.expectedResult.status as expectedStatus, sObject.result.status as actualStatus,
    expectedResult.expectedResult.conditionIndex as expectedConditionIndex, sObject.result.conditionIndex as actualConditionIndex,
    expectedResult.expectedResult.conditionText as expectedConditionText, sObject.result.conditionText as actualConditionText,
    expectedResult.expectedResult.runtimeError as expectedRuntimeError, sObject.result.runtimeError as actualRuntimeError
  FROM UNNEST(mock_ExpectedResult()) expectedResult
  LEFT JOIN (
      SELECT
          sObject.Id AS Id,
          sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
          sObject.CA10__auditing__c AS CA10__auditing__c,
          process_CA10__CaAzureSqlServer__c(
              STRUCT(
                  sObject.Id AS Id,
                  sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c,
                  sObject.CA10__auditing__c AS CA10__auditing__c
              ),
              sObject.context.snapshotTime
          ) as result
      FROM UNNEST(mock_CA10__CaAzureSqlServer__c()) AS sObject
  ) sObject ON sObject.Id = expectedResult.Id;
