Trouble with NXlog Enterprise and SQL ODBC audit file.

#1 jacas 4 years ago

Hello NXlog world! I'm having some challenges to pull data from a SQL 2012 (running on W2K16) database using the im-odbc connector. This is my input:

> <Input MSSQL_IN> > Module im_odbc > ConnectionString Driver={ODBC Driver 17 for SQL Server}; Server=XXXXXXXX;
> Trusted_Connection=yes; DATABASE=XXXXX; > PollInterval 5 > IdType timestamp > SQL SELECT event_time AS 'id', f., a.name AS action_name
> FROM fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\XXXXX\Log\Audit-File.sqlaudit', default,
> default) AS f
> INNER JOIN sys.dm_audit_actions AS a
> ON f.action_id = a.action_id
> WHERE event_time > ? > <Exec> > delete($id); > rename_field($event_time, $EventTime); > </Exec> > > </Input>

The error I'm getting when starting NXlog is as follows:

2019-10-16 13:51:03 INFO nxlog-4.4.4431 started 2019-10-16 13:51:03 INFO im_odbc successfully connected to the database 2019-10-16 13:51:03 ERROR SQLExecDirect failed, 42000:2:300:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CONTROL SERVER permission was denied on object 'server', database 'master'.; 42000:3:297:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The user does not have permission to perform this action. (odbc error code: -1)`

I have tried to enable of the permissions on the database (GRANT) but the CONTROL SERVER is throwing me off. I'm pretty sure is a stupid thing I haven't thought about, but since I am very new to NXlog, and obviously not a SQL admin, I am defeated! for now, so I'm hopeful someone, can help me with some guidance so I can troubleshoot further. TIA and happy hump day!

Permalink

#3 Zhengshi Nxlog ✓ 4 years ago

#1 jacas

Hello NXlog world! I'm having some challenges to pull data from a SQL 2012 (running on W2K16) database using the im-odbc connector. This is my input: > <Input MSSQL_IN> > Module im_odbc > ConnectionString Driver={ODBC Driver 17 for SQL Server}; Server=XXXXXXXX; > Trusted_Connection=yes; DATABASE=XXXXX; > PollInterval 5 > IdType timestamp > SQL SELECT event_time AS 'id', f., a.name AS action_name > FROM fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\XXXXX\Log\Audit-File.sqlaudit', default, > default) AS f > INNER JOIN sys.dm_audit_actions AS a > ON f.action_id = a.action_id > WHERE event_time > ? > <Exec> > delete($id); > rename_field($event_time, $EventTime); > </Exec> > > </Input> The error I'm getting when starting NXlog is as follows: 2019-10-16 13:51:03 INFO nxlog-4.4.4431 started 2019-10-16 13:51:03 INFO im_odbc successfully connected to the database 2019-10-16 13:51:03 ERROR SQLExecDirect failed, 42000:2:300:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CONTROL SERVER permission was denied on object 'server', database 'master'.; 42000:3:297:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The user does not have permission to perform this action. (odbc error code: -1)` I have tried to enable of the permissions on the database (GRANT) but the CONTROL SERVER is throwing me off. I'm pretty sure is a stupid thing I haven't thought about, but since I am very new to NXlog, and obviously not a SQL admin, I am defeated! for now, so I'm hopeful someone, can help me with some guidance so I can troubleshoot further. TIA and happy hump day! JC

Hey! no worries on not being an expert. I am pretty good with NXLog, but not as strong on the SQL Server side.
That being said, its definitely a permissions issue. Seems like a few roles can provide the permissions you need. Check out the following link that describes permissions.
There is a PDF under List of Permissions that breaks it down pretty well, although still kind of confusing.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-ver15

Hope that helps!