--- policy: /ce/ca/azure/sql-database/server-auditing logic: /ce/ca/azure/sql-database/server-auditing/prod.logic.yaml executionTime: 2026-02-10T22:33:36.880065059Z generationMs: 73 executionMs: 959 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: 7BFFE7AE600AE527CEC0B545E5E481D3 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: "Enable 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: C096245468858A9F41067AD8FA5D8D0E 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# Text\n - name: \"CA10__locationName__c\"\ \n value: \n FIELD:\n path: \"CA10__locationName__c\"\n" script: |- CREATE TEMP FUNCTION mock_ExpectedResult() RETURNS ARRAY >> 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 >> DETERMINISTIC LANGUAGE js AS r""" return [ { "context" : { "snapshotTime" : new Date("2024-06-12T19:31:46Z") }, "CA10__disappearanceTime__c" : new Date("2024-06-12T19:31:46Z"), "CA10__auditing__c" : "Enabled", "Id" : "test1" }, { "context" : { "snapshotTime" : new Date("2024-06-12T19:31:46Z") }, "CA10__auditing__c" : "Disabled", "Id" : "test2" }, { "context" : { "snapshotTime" : new Date("2024-06-12T19:31:46Z") }, "CA10__auditing__c" : "Enabled", "Id" : "test3" }, { "context" : { "snapshotTime" : new Date("2024-06-12T19:31:46Z") }, "CA10__auditing__c" : "", "Id" : "test4" } ]; """; CREATE TEMP FUNCTION process_CA10__CaAzureSqlServer__c( obj STRUCT< CA10__disappearanceTime__c TIMESTAMP, CA10__auditing__c STRING, Id STRING >, snapshotTime TIMESTAMP ) RETURNS STRUCT 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)); }; this.containsAll = function(arg, substrings) { if (substrings == null || substrings.length === 0) return false; let normalizedArg = this.normalize(arg); return substrings.every(sub => normalizedArg.includes(this.normalize(sub))); }; this.containsAny = function(arg, substrings) { if (substrings == null || substrings.length === 0) return false; let normalizedArg = this.normalize(arg); return substrings.some(sub => normalizedArg.includes(this.normalize(sub))); }; }(); 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: "Enable 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.CA10__disappearanceTime__c AS CA10__disappearanceTime__c, sObject.CA10__auditing__c AS CA10__auditing__c, sObject.Id AS Id, process_CA10__CaAzureSqlServer__c( STRUCT( sObject.CA10__disappearanceTime__c AS CA10__disappearanceTime__c, sObject.CA10__auditing__c AS CA10__auditing__c, sObject.Id AS Id ), sObject.context.snapshotTime ) as result FROM UNNEST(mock_CA10__CaAzureSqlServer__c()) AS sObject ) sObject ON sObject.Id = expectedResult.Id;