Test Search

[dhaertel]

So I've been tasked to run a mock search as if one of our users breached a database just to see if we are collecting enough of and the correct data. By searching the user and the database server I'm presented almost 40K results, if I eliminate logoffs, that gets me down to 20K, still pretty large and it also doesn't seem to include much of anything in those returned results. Can anyone suggest some key search items to add to this search string to narrow down access, maybe even to the database level?

[somesoni2]

What type of database logs you're collecting?

[dhaertel]

Apparently not enough, looks like just connection, initiation and that's about it. We have GreenSQL as a connection firewall but it's not returning anything other than application level access, so no user information is being gathered. We've also been unsuccessful in getting GreenSQL to pass activity logs to the Splunk server. We get the syslog data but not activity, so I'm forced to try and mesh them up manually, which hasn't been successful.

[richgalloway]

What is your current search?

[dhaertel]

Right now it's just the SQL server host, user account, connection port, and successful and unsuccessful logons/logoffs. I can see the user access the SQL server but nothing past that. So apparently there's some auditing that needs to be enabled inside the SQL server in order to get to DB level access. This is new territory for me.

[dhaertel]

sqlxxxx1 src_user=jxxxxx NOT 18149 NOT SQLxxxxx1 "Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe"

the NOT 18149 just eliminates the logoffs for narrowing purposes and the NOT SQLxxxxx1 just eliminates a particular server that the DBA uses to launch RDP from to get to the DB server, I'm looking at specifically when he launches/connects from his workstation. At this point, I'm ok with just knowing the logon events.

[richgalloway]

IMO, it's important to know what users are doing after they log on. This is especially true for admins and other users with enhanced permissions.

[dhaertel]

That's true and if I can filter it further down I can add the logoffs back on but there are so many connection entries that I just needed to try and narrow the results down to see if I could get down to file level or database instance level.

Unfortunately, we're missing the auditing on the database side of things. We were supposed to be catching logs from our SQL firewall, GreenSQL but they were never setup correctly so I'm back to square one while we search for a replacement for GreenSQL.

We could setup the Microsoft auditing tool but everything hitting the database should be going through the firewall and it'll just increase the load on the server which is already over 90 percent RAM utilization with a 256 Gig load.