1
answer

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

AskedOctober 16, 2019 - 8:58pm

Answer (1)

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!

Comments (1)

  • jacas's picture

    Thanks a lot! Yes it was permissions but them MS documentation is so deep and wide that it was a pain to find. As I stated the solution was going to be so easy I feel stupid. lol

    On the nxlog service, it has to has permissions as administrator. So under services right click on it and under properties find the logon change the default Local System account to the Administrator account, give it the password and boooom!!! nxlog is now working!
    I think this is not very clear for me on the NXlog documentation, but I think this is specially useful for SQL via ODBC. If we are logging to the event manager, this permission is not needed. Hopefully this will help another noob, Thanks for the direction Zhengshi.

    Over and out!