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

Hello,

I hope someone can help me. I need to pick application logs from a few MSSQL database tables. 3 of 4 works perfect -there are no issues getting the data, but one table is causing issues.

Below you can see input I am using. As you can see, there is one column name with the Danish character, however, I am not sure if only this is causing trouble. The error I receive is:

ERROR SQLExecDirect failed, 42000:2:102:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Ą'.; 42000:3:319:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a s (odbc error code: -1)

However, If I remove this column from SELECT, another error occurs:
ERROR id column not found or its type is unsupported.

I received a similar error than I left some additional spaces in the query, but after that, I used notepad++ to make sure I don't leave any hidden characters.

=========================================================
</Input>

<Input odbc_besked>
Module im_odbc
ConnectionString Driver={SQL Server Native Client 11.0};Server=SERVERNAME\mssql2014,port;Database=database_name;Trusted_Connection=yes;
SQL SELECT BeskedType, \
Oprettet, \
InternBeskedId, \
SvarPåInternBeskedId, \
Retning, \
BeskedArt, \
SoapBeskedId, \
BeskedId, \
HttpStatusCode, \
Fra, \
Til, \
Failover, \
EnvelopeStartTag, \
SoapHeaderElement, \
IndeholderSoapFault, \
Stack \
FROM table_name WITH (NOLOCK) WHERE InternBeskedId > ?

</Input>