Filebeat MSSQL ingest pipeline can't parse authentication messages

[leweafan]

Describe the enhancement:

Filebeat MSSQL ingest pipeline can't parse authentication messages.

Describe a specific use case for the enhancement or feature:

Failed Authentication

2021-08-26 10:15:12.23 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.10.10.10]
2021-08-24 11:49:50.34 Logon       Login failed for user 'DD\username'. Reason: Failed to open the database 'DWH' configured in the session recovery object while recovering the connection. [CLIENT: 10.10.10.10]
2021-08-24 11:40:34.01 Logon       Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.10.10.10]
2021-08-25 06:35:26.16 Logon       Login failed for user 'username'. Reason: Failed to open the explicitly specified database 'DWH'. [CLIENT: 10.10.10.10]

Successful Authentication

2021-08-26 14:35:26.26 Logon       Login succeeded for user 'DD\usrWEM'. Connection made using Windows authentication. [CLIENT: 10.10.10.10]

[elasticmachine]

Pinging @elastic/security-external-integrations (Team:Security-External Integrations)

[jamiehynds]

@r00tu53r could you take a look and confirm if our MSSQL agent integration supported these authentication events? https://github.com/elastic/integrations/tree/main/packages/microsoft_sqlserver

[r00tu53r]

@leweafan I see you're referring to the MSSQL beats module. There is a Fleet based integration package available for MSSQL that supports all the available event types. Would you be able to use that instead https://docs.elastic.co/en/integrations/microsoft_sqlserver

[r00tu53r]

@leweafan I tested the authentication messages above and I see the pipeline is able to process those messages successfully. I have pasted a sample event that was ingested from the log sample above.

{
  "_index": "filebeat-7.17.0-2022.06.27-000001",
  "_type": "_doc",
  "_id": "SCpdpIEBz_kTFNHZvp50",
  "_version": 1,
  "_score": 1,
  "_source": {
    "container": {
      "id": "github.com"
    },
    "agent": {
      "hostname": "MacBook-Pro-2.local",
      "name": "MacBook-Pro-2.local",
      "id": "d11daa87-eb68-4a67-a5d4-a6fa11448920",
      "ephemeral_id": "25041482-6de3-4f2c-8f8e-aab883ea519d",
      "type": "filebeat",
      "version": "7.17.0"
    },
    "log": {
      "file": {
        "path": "/Users/saikiran/go/src/github.com/r00tu53r/beats/x-pack/filebeat/module/mssql/log/test/test.log"
      },
      "original": "2021-08-26 14:35:26.26 Logon       Login succeeded for user 'DD\\usrWEM'. Connection made using Windows authentication. [CLIENT: 10.10.10.10]",
      "offset": 2416
    },
    "fileset": {
      "name": "log"
    },
    "message": "Login succeeded for user 'DD\\usrWEM'. Connection made using Windows authentication. [CLIENT: 10.10.10.10]",
    "input": {
      "type": "log"
    },
    "@timestamp": "2021-08-26T14:35:26.260+05:30",
    "ecs": {
      "version": "1.12.0"
    },
    "service": {
      "type": "mssql"
    },
    "host": {
      "hostname": "MacBook-Pro-2.local",
      "os": {
        "build": "21F79",
        "kernel": "21.5.0",
        "name": "macOS",
        "type": "macos",
        "family": "darwin",
        "version": "12.4",
        "platform": "darwin"
      },
      "ip": [
        "192.168.1.12"
      ],
      "name": "MacBook-Pro-2.local",
      "id": "55EC5714-03E0-5CA2-9509-AAB45FCFA57A",
      "mac": [
        "ad:dd:48:00:11:22"
      ],
      "architecture": "x86_64"
    },
    "event": {
      "ingested": "2022-06-27T08:53:35.987453379Z",
      "timezone": "+05:30",
      "kind": "event",
      "module": "mssql",
      "category": [
        "database"
      ],
      "type": [
        "info"
      ],
      "dataset": "mssql.log"
    },
    "mssql": {
      "log": {
        "origin": "Logon"
      }
    }
  },
  "fields": {
    "event.category": [
      "database"
    ],
    "host.os.name.text": [
      "macOS"
    ],
    "host.hostname": [
      "MacBook-Pro-2.local"
    ],
    "host.mac": [
      "ad:dd:48:40:11:22"
    ],
    "container.id": [
      "github.com"
    ],
    "host.os.build": [
      "21F79"
    ],
    "service.type": [
      "mssql"
    ],
    "host.ip": [
      "fe80::aede:48ff:fef0:1122"
    ],
    "agent.type": [
      "filebeat"
    ],
    "event.module": [
      "mssql"
    ],
    "host.os.version": [
      "12.4"
    ],
    "host.os.kernel": [
      "21.5.0"
    ],
    "host.os.name": [
      "macOS"
    ],
    "agent.name": [
      "MacBook-Pro-2.local"
    ],
    "host.name": [
      "MacBook-Pro-2.local"
    ],
    "host.id": [
      "55EC5714-03E0-5CA2-9509-AAB45FCFA57A"
    ],
    "log.original": [
      "2021-08-26 14:35:26.26 Logon       Login succeeded for user 'DD\\usrWEM'. Connection made using Windows authentication. [CLIENT: 10.10.10.10]"
    ],
    "event.kind": [
      "event"
    ],
    "event.timezone": [
      "+05:30"
    ],
    "host.os.type": [
      "macos"
    ],
    "fileset.name": [
      "log"
    ],
    "input.type": [
      "log"
    ],
    "log.offset": [
      2416
    ],
    "agent.hostname": [
      "MacBook-Pro-2.local"
    ],
    "message": [
      "Login succeeded for user 'DD\\usrWEM'. Connection made using Windows authentication. [CLIENT: 10.10.10.10]"
    ],
    "host.architecture": [
      "x86_64"
    ],
    "event.ingested": [
      "2022-06-27T08:53:35.987Z"
    ],
    "@timestamp": [
      "2021-08-26T09:05:26.260Z"
    ],
    "agent.id": [
      "d11daa87-eb68-4a67-a5d4-a6fa11448920"
    ],
    "ecs.version": [
      "1.12.0"
    ],
    "host.os.platform": [
      "darwin"
    ],
    "event.type": [
      "info"
    ],
    "log.file.path": [
      "/Users/saikiran/go/src/github.com/r00tu53r/beats/x-pack/filebeat/module/mssql/log/test/test.log"
    ],
    "mssql.log.origin": [
      "Logon"
    ],
    "agent.ephemeral_id": [
      "25041482-6de3-4f2c-8f8e-aab883ea519d"
    ],
    "agent.version": [
      "7.17.0"
    ],
    "host.os.family": [
      "darwin"
    ],
    "event.dataset": [
      "mssql.log"
    ]
  }
}

[r00tu53r]

I'll close this issue as I don't see the problem. Please feel free to reopen the issue if you still think this is a problem.

[leweafan]

@r00tu53r There is no parsing at all. The following fields are missing:

• user.name : usrWEM
• user.domain : DD
• source.ip : 10.10.10.10
• event.category = "authentication"
• event.action = "logged-in”
• event.outcome = "success"

These fields should be present according to ECS guide and needed for SIEM rules.

P.S. Seems I can't reopen the issue myself.

[r00tu53r]

@leweafan unfortunately yes the pipeline is very basic and doesn't parse them into useful fields. Sorry I mistook the issue to indicate a broken pipeline.

Are you using a standalone filebeat setup with modules or if you have a fleet managed agent setup I would recommend trying the Microsoft SQL Server integration package as the pipeline supports all the documented SQL Server audit event types and processes messages from windows event log.

[leweafan]

@r00tu53r thanks for reopening this issue. Yes we are using filebeat cause elastic agent and fleet do not support Kafka. I wonder why ingest pipelines for the same log are different for filebeat and fleet.

[r00tu53r]

@leweafan The direct output from Elastic Agent to Kafka is still in plan (elastic/elastic-agent#152).

Am not sure if it helps your use case. However there might be a way to use elastic agent in standalone mode and send the output to logstash and then to kafka. More information is available

• https://www.elastic.co/guide/en/fleet/master/logstash-output.html
• https://www.elastic.co/guide/en/logstash/current/output-plugins.html

Enhancements to pipelines and addition of new integrations are usually only planned for Elastic Agent based integrations.

cc: @jamiehynds

[r00tu53r]

Also, @leweafan -

@andrewkroh has a gist here - https://gist.github.com/andrewkroh/c253717ebe82f2ec47fe003eda99c1dc that illustrates the steps to migrate to standalone Elastic Agent and configure filebeat by hand to use the fleet managed data streams.

The difference being that there is no need to set _raw_index from Beats. On the Logstash side enable data_stream => true for this data so that it sends it to the correct data stream based on the data_stream.dataset field (see https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-data_stream

I hope this helps

[leweafan]

@r00tu53r thanks for your concern. I think we can configure filebeat to use the fleet managed data streams using provided instruction but seems current ESC compliance is very poor and we will not benefit much from it. I checked fleet packages and only 34 of 121 have event.category = authentication and seems event.action not standardized. I have a post about my concerns here https://discuss.elastic.co/t/why-filebeat-pipelines-disappoint-or-siem-missing-authentication-patterns/307308.

[r00tu53r]

@leweafan Thank you for sharing the discuss post.

Once you've been able to configure filebeat to use fleet managed data stream you could use the newer Microsoft SQL Server integration package.

The Microsoft SQL Server integration package available for Elastic Agent supports / identifies authentication messages. At the time of writing support was added for all the available event/class types here and actions here. The package categorises parses SQL Server fields to ECS fields.

Authentication events parsed by the integration -

• Login Succeeded here
• Login Failed here

I would be happy to make fixes to this package if you're finding something missing / needs fixing.