Highlighted
Visitor.
1774 views

Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Hello,

I have been trying to set up a smart connector Microsoft SQL Server Multi instance Audit DB on a remote Windows 2008R2 SP1 host which will connect to a SQL host and read the trace files.

I've tried 4 different scenarios with no luck:

  1. JDBC/ODBC driver : sun.jdbc.odbc.JdbcOdbcDriver

URL: jdbc:odbc:arcsight

User: local SQL user

Passes validation but in the agent log I see the following


WARN ][default.com.arcsight.common.config.AgentPropertiesFileConfiguration][getBaseAgent] No ID and no Connectors configured

[WARN ][default.com.arcsight.agent.v.g][getFCPVersionSubFolder] Could not identify connector from agent id [null]. Will use the default value [0] of the parameter [fcp.version] to load FCP content.

Followed by

[WARN ][default.com.arcsight.agent.loadable.agent._MultiSQLServerTraceAuditingAgent][processQuery()] Failed to process query [[select TOP 1 * FROM ::fn_trace_gettable('z:\namq1d102_dev_59.trc', 1)]] on database URL[jdbc:sqlserver://sqlserverhost.domain:1433;DatabaseName=master], bitmechanic URL[jdbc:bitmechanic:pool:Eu+bGD4BABCABsRNPkYNRQ==]

  1. JDBC/ODBC driver : sun.jdbc.odbc.JdbcOdbcDriver

URL: jdbc:odbc:arcsight

User: domain user

3. JDBC/ODBC driver : com.microsoft.sqlserver.jdbc.SQLServerDriver

URL: jdbc:sqlserver://sqlserverhost.domain:1433

User : local SQL user

Passes validation

4. JDBC/ODBC driver : com.microsoft.sqlserver.jdbc.SQLServerDriver

URL: jdbc:sqlserver://sqlserverhost.domain:1433

User : domain user

Doesn’t pass validation


Although my connector host is a Windows machine I've tried with jdbc driver (made sure the necessary jar file was copied to the user/agent/lib location)


With the first 3 scenarios,I get same WARNs in the agent log and no trace file is being processed. In agent.out.wrapper.log I get device connection to the jdbc or odbc up and the db vesrion has been determined.


I installed another connector on the SQL 2008R2 SP2 server host and I had no problem processing the trace file using jdbc driver with local SQL user.


Has any of you managed to get this type of connector functioning on a remote host?


Many thanks

Nina



0 Likes
1 Solution

Accepted Solutions
Absent Member.
Absent Member.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

I battled with this connector for a while but I did eventually get it to work and produce the desired results. Since you are using 2K8 you may want to save yourself some trouble and just send them to the Windows event log where you can grab them with a WUC. If you MUST use this connector here are a few things I had to correct before this connector worked for me...

1. I used local credentials instead of domain credentials. Because the ConnApp was not a domain member authentication would not pass correctly when using domain credentials. I was not about to add my ConnApp to the domain just for this one connector so I used local credentials instead.

2. The Trace file must be at least 1MB before it will see it.

3. You need to use the full UNC path to the trace directory in the connector parameters.

This error indicates the trace is not setup correctly or the permissions for the user account are not setup correctly...

[WARN ][default.com.arcsight.agent.loadable.agent._MultiSQLServerTraceAuditingAgent][processQuery()] Failed to process query [[select TOP 1 * FROM ::fn_trace_gettable('z:\namq1d102_dev_59.trc', 1)]] on database URL[jdbc:sqlserver://sqlserverhost.domain:1433;DatabaseName=master], bitmechanic URL[jdbc:bitmechanic:pool:Eu+bGD4BABCABsRNPkYNRQ==]

I do not think the other errors are very important because I
still see them in my logs even after I got it working perfectly. Also have your
DBA monitor the connector activity on their side and you may get more
intelligence into what isn't working. If all you see it the initial select
command and then nothing follows you definitely have a permissions issue.

View solution in original post

0 Likes
8 Replies
Absent Member.
Absent Member.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

I battled with this connector for a while but I did eventually get it to work and produce the desired results. Since you are using 2K8 you may want to save yourself some trouble and just send them to the Windows event log where you can grab them with a WUC. If you MUST use this connector here are a few things I had to correct before this connector worked for me...

1. I used local credentials instead of domain credentials. Because the ConnApp was not a domain member authentication would not pass correctly when using domain credentials. I was not about to add my ConnApp to the domain just for this one connector so I used local credentials instead.

2. The Trace file must be at least 1MB before it will see it.

3. You need to use the full UNC path to the trace directory in the connector parameters.

This error indicates the trace is not setup correctly or the permissions for the user account are not setup correctly...

[WARN ][default.com.arcsight.agent.loadable.agent._MultiSQLServerTraceAuditingAgent][processQuery()] Failed to process query [[select TOP 1 * FROM ::fn_trace_gettable('z:\namq1d102_dev_59.trc', 1)]] on database URL[jdbc:sqlserver://sqlserverhost.domain:1433;DatabaseName=master], bitmechanic URL[jdbc:bitmechanic:pool:Eu+bGD4BABCABsRNPkYNRQ==]

I do not think the other errors are very important because I
still see them in my logs even after I got it working perfectly. Also have your
DBA monitor the connector activity on their side and you may get more
intelligence into what isn't working. If all you see it the initial select
command and then nothing follows you definitely have a permissions issue.

View solution in original post

0 Likes
Highlighted
Visitor.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Hello Justin,

Many thanks for your post.

I have modified the Connector Data Folder as you suggested , I used the UNC path and when I started the connector, it did process the trace files.

Many thanks again

Nina

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Do you happen to know what permissions were grated to the account?  I am seeing the same warning message.

Example:

[2014-09-29 16:34:51,286][WARN ][default.com.arcsight.agent.loadable.agent._MultiSQLServerTraceAuditingAgent][processQuery()] Failed to process query [[select TOP 1 * FROM ::fn_trace_gettable('//server/trace/test.trc', 1)]] on database URL[jdbc:sqlserver://server:1433;DatabaseName=master], bitmechanic URL[jdbc:bitmechanic:pool:fqNLw0gBABCACMYbYFZTIw==]

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

I'm ecountering the same error as your using a smart conenctor in windows 2012

How did you solve your problem on this?

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Dear All,

I am struggling to set up connection with SQL server from ArcMC.

I am getting error of unable to load JDBC driver though I have installed it on specified connector path.

I am using domain credentials for setting up connection with SQL database and mounted folder trace file folder from sql server to ArcMC appliance, still no success.

Can anyone assist me in resolving this issue ?

Thanks & Regards,

Pratik

0 Likes
Highlighted
Visitor.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Hello Patrick,

Did you add integratedSecurity=true to the JDBC URL entry?

Windows Authentication only works when using the sqljdbc4.jar driver. Additionally, you will need to add ";integratedSecurity=true" to the JDBC URL entry for the connection to your database. Also, you will need to copy the sqljdbc_4.0\enu\auth\x86\sqljdbc_auth.dll file from the driver download to the $ARCSIGHT_HOME/jre/bin directory. For the sqljdbc_auth.dll, the x86 version is used as the connector JVM is only 32-bit.

Example: jdbc:sqlserver://mysqlserver:1433;DatabaseName=mydatabase;integratedSecurity=true

I hope this helps you out.

Regards

Nina

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Dear Nina,

Thanks for your response.

I performed above suggested steps but still no success.

Regards,

Pratik

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

Re: Microsoft SQL Server Multi instance Audit DB Smart connector problem

Jump to solution

Same Error in MS SQL 2012. O MS SQL 2003 all work fine

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.