Still no support for MSSQL Auditor running on MSSQL Server 2017 ?

As there seems to be little update on progress for MSSQL Auditor support for SQL 2017, I want to ask if we can expect any approximate timeline of when this will happen ?

Even on ticket support, I am left in dark over this, with no timeframe available.

It's frustrating to not have support for a widely adopted SQL server version, which is around for ~2.5 years (since Oct 2, 2016)

Has anyone managed to make this work (maybe with a workaround) ?

If not, what is the alternative way you use to check MSSQL 2017 logs ?

jhynds can you expand on your hint from this thread >

Re: MS SQL Auditor for MS SQL 2017

Find more posts tagged with

mssql auditor

sqlserver2017

Accepted answers

All comments

jhynds

We're currently working on support for SQL 2017/2019, however while we're working that, you could use SQL Extended Events and send them to the Windows Event Log. SEM includes a SQL Application Log connector which would then parse those logs. See here for steps on how to enable logging to Windows Events on the SQL side: How-To Write SQL Server Audit Events To Windows Security Log - TeckLyfe

Does that sound like a reasonable solution to get your SQL 2017 logs into SEM? Out of curiosity, what are the most common events you need to monitor within SQL logs?

valkos

On this feature request, it says it is implemented.

By suggesting us to use SQL Extended Events and marking the above request as implemented, is this an indication that MSSQL Auditor will not be supported anymore ?

The most common events I need tracking are the ones related to:

admin actions from SQL Server Mgmt Studio, services and scheduled jobs;
TSQL statements (Stmt: Completed) which are generated by the internal applications
Audit events on logins and privilege changes etc....

We will check the suggestion of SQL events writing to Windows Events log.

Let's hope this does not have big impacts on performance on SQL server performance.

ralfs

It's only implemented for 2016 yet. The feature request is a little bit unclear here maybe because the feature is requested for both versions and the MS SQL Profiler is deprecated for 2017 which results in a little bit more work.

ralfs

What we need to monitor in a first step:

Login attempts
Changes on the database (DDL commands)
Changes on the data (DML commands)

It would be great if especially step 3 could be filtered before it will be send to SEM (for example: no "select" statements)

Kind Regards,

Ralf

valkos

We started with SQL Extended events.

The SQL logs are now stored in Windows Application logs, but there are several issues with managing these logs in SEM.

1. There are wayyyyy more logs coming in than when we used to get with MSSQL Auditor. This is because with MSSQL Auditor we were able to select the types of events and fields in Profiler that we wanted to be captured.

With SQL Extended Events, it seems like you can't easily do that, and we're overloaded with tons of system noise like: DB health, always on dashboard, native sql queries etc...

2. The MSSQL application log needs to be fixed in order to properly report the fields that it can gather and then add few more critical ones that need to be picked.

The field from Windows events like: database_name, application_name, client_ip, object_name needs to be captured and shown on SEM UI. They're currently not.

ServiceName field should have: application_name

DetectionIP field should have: client_ip

database_name should be stored either in ExtraneousInfo or another field where you can filter easily.

3. It would help if these events would be classified as ObjectAudit events instead of Service Info.

That is because then we can easily create custom reports with the LEM Reports application, due to Object Audit templates readily available.

Below are all the (*anonymized) raw fields that get generated by SQL extended event and stored in Windows Event, but then some of them fail to be collected by SEM.

Ideally the fields in bold should be captured by SEM

Audit event: audit_schema_version:1

event_time:2019-06-03 13:43:31

sequence_number:1

action_id:SL

succeeded:true

is_column_permission:true

session_id:123

server_principal_id:123

database_principal_id:123

target_server_principal_id:123

target_database_principal_id:123

object_id:123123123123

user_defined_event_id:123

transaction_id:123

class_type:U

duration_milliseconds:0

response_rows:123

affected_rows:123

client_ip:123.123.123.123

permission_bitmask:123123123123123123123123123

sequence_group_id:123A-123B-123C-123D-123E-123F

session_server_principal_name:domain\user.name

server_principal_name:domain\user.name

server_principal_sid:q7834tuifhbsdrwetyhghsldk

database_principal_name:sample

target_server_principal_name:

target_server_principal_sid:

target_database_principal_name:

server_instance_name:SQL-SERVER

database_name:dbname

schema_name:xxxx

object_name:xxxxxxx

statement:select xxxxx from xxxxxxxxx

additional_information:

user_defined_information:

application_name:SQL Management Application

Another thing is if default preceeding text be removed from (connector) and then to be shown clean in SEM UI.

Example:

Instead of having this in SEM

statement:select xxxxx from xxxxxxxxx

We should only have

select xxxxx from xxxxxxxxx

I hope this helps, as the support staff suggested I also post this here

jhynds

Thanks for the great feedback!

Did you adjust the types of events you want to monitor via Extended Events? The 'Select Events to Capture' wizard works in a similar way to Profiler to allow you to customize which events you want to monitor.

Screenshot 2019-06-10 at 14.43.05.png

Your other comments should be addressable via an update to our SQL Connector. Would you be able to supply a log sample of the events so we can adjust our connector and start parsing those events correctly?