How to connect to Microsoft SQL Server From NetWare Using a JDBC Channel

(Last modified: 07Jun2005)

This document (10095873) is provided subject to the disclaimer at the end of this document.

goal

fact

Novell Nsure Audit 1.0.3

Novell Netware 6.5 SP2

symptom

Nsure Audit is looking for lgdmssql.nlm while attempting to connect to a Microsoft SQL Server with Nsure Audit 1.0.2

fix

This TID has been modified to include instructions for using jTDS, an open source JDBC driver for Microsoft SQL. This JDBC driver has been found to perform at least ten times faster than the JDBC driver provided by Microsoft on a NetWare server, and includes many enhancements.

Download and extract the jTDS driver from http://jtds.sourceforge.net

Copy the jtds-1.0.3.jar file from the distribution, to SYS:SYSTEM\NAUDIT on the NetWare server with Nsure Audit Installed. Rename the file to jtds.jar. The version number of the file may change.

Open iManager, expand the Nsure Audit Role from the Roles and Tasks frame, and click on Server Configuration. Browse to and select, or enter the name of the SERVERNAME Logging Server object from the Logging Services container, and click ok.

At the Server Configuration screen for the Logging Server object, click on the Channels Tab.

Click the check box next to the channels container, and click the New Channel hyperlink. Enter a name for the channel, and select the channel type JDBC.

On the JDBC Channel configuration, enter the following values:

JDBC Class (case sensitive):
net.sourceforge.jtds.jdbc.Driver

JDBC URL (DatabseName is case sensitive):
jdbc:jtds:sqlserver://sql_server_ip:1433/naudit

Replace naudit with the name of the database you created for Nsure Audit. Replace Sql_server_ip with the IP address or DNS name of the MS SQL Server.

If there is a specific instance on the SQL server that needs to be connected to, refer to the jTDS FAQ and documentation for instructions regarding using an instance name. These instructions will work if there is only a single SQL server at the designated IP address.

For username and password, enter a username and password with rights to write to the naudit database on the MS SQL Server.

JDBC Table:
NAUDITLOG

If you want the name to be something other than NAUDITLOG, provide the actual table name. Do not use spaces, commas, quotes, or other special characters in the table name. Novell recommends using NAUDITLOG for compatibility with the Nsure Audit report tool.

JDBC Create Table SQL:
CREATE TABLE NAUDITLOG ([SourceIP] [int] NULL ,[ClientTimeStamp] [int] NULL , [ClientMS] [int] NULL , [ServerTimestamp] [int] NULL , [SessionID] [int] NULL , [Component] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EventID] [int] NULL , [Severity] [int] NULL , [Grouping] [int] NULL , [Originator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [OriginatorType] [int] NULL , [Target] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TargetType] [int] NULL , [SubTarget] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Text1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Text2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Text3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Value1] [int] NULL , [Value2] [int] NULL , [Value3] [int] NULL , [MIMEType] [int] NULL , [DataSize] [int] NULL , [Data] [image] NULL , [Signature] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )

Copy and paste this command into Notepad, then insert it into iManager. Replace NAUDITLOG with the JDBC table name if NAUDITLOG is not being used.

Click OK to save changes and return to the Server Configuration page. If it is desired to have the JDBC channel be the primary log channel, perform the following steps:

Click on the General Tab

Click on the Configuration Link.

Change the Log Channel attribute to the JDBC channel object. Click ok.

Unload and reload lengine.nlm for changes to take effect.

Optional - Configure iManager to query MS SQL

Copy the jtds.jar to the iManager server.

On NetWare 6 and 6.5 copy this file to SYS:/JAVA/LIB/EXT
On Linux, the file should be copied to /var/opt/novell/tomcat4/common/lib/
On Windows servers running iManager and Tomcat services, copy this jar file to: C:\Program Files\novell\tomcat\common\lib

Restart tomcat after copying this file into place.
On NetWare:
tc4stop - wait for two minutes
tomcat4 - wait for up to 5 minutes

On Windows:
Restart the Tomcat Service using the Services applet in Control Panel.

On Linux:
/etc/init.d/novell-tomcat -restartExpand the Nsure Audit role, and click on Query Configuration

On the databases tab, click New.

On the Edit Database dialog window, enter the following information:

Name:
MSSQL

JDBC Class:
net.sourceforge.jtds.jdbc.Driver

JDBC URL:
jdbc:jtds:sqlserver://sql_server_ip:1433/naudit

Replace naudit with the name of the database you created for Nsure Audit. Replace Sql_server_ip with the IP address or DNS name of the MS SQL Server.

For username and password, enter a username and password with rights to read to the naudit database on the MS SQL Server.

Table:
NAUDITLOG

If you used something other than NAUDITLOG, provide the actual table name, determined when creating the JDBC channel.

For convenience, click Store Password.

document

Document Title:	How to connect to Microsoft SQL Server From NetWare Using a JDBC Channel
Document ID:	10095873
Solution ID:	NOVL100220
Creation Date:	14Dec2004
Modified Date:	07Jun2005
Novell Product Class:	Management Products

disclaimer

The Origin of this information may be internal or external to Novell. Novell makes all reasonable efforts to verify this information. However, the information provided in this document is for your information only. Novell makes no explicit or implied claims to the validity of this information.
Any trademarks referenced in this document are the property of their respective owners. Consult your product manuals for complete trademark information.