Microsoft SQL

Microsoft JDBC Driver for SQL Server

DataSource

Transaction Type DataSource Class
NO_TRANSACTION, LOCAL_TRANSACTION 
com.microsoft.sqlserver.jdbc.SQLServerDataSource
XA_TRANSACTION 
com.microsoft.sqlserver.jdbc.SQLServerXADataSource

SSL Settings

Specify the SSL parameters in the Other Properties field in the following format: 

encrypt=value;
trustStore=Truststore path;
trustStorePassword=Truststore password
  • encrypt value is true or false depending on the encryption settings in the Microsoft SQL server.
  • trustStore value is the path of the trusted certificate store.
  • trustStorePassword value is the password used to protect the TrustStore data.
Note: If you have configured SSL connections between Adapter for JDBC and Microsoft SQL Server, enter the TCPS port number of the Microsoft SQL Server.

Kerberos Authentication

Specify the Kerberos authentication parameters in the Other Properties field in the following format: 
integratedSecurity=true;
authenticationScheme=NativeAuthentication
  • NativeAuthentication is specific to the Windows platform and uses the following files:
    • The library sqljdbc_auth.dll for Microsoft JDBC driver version 7 or earlier.
    • The library mssql-jdbc_auth-x.x.x.xxx.dll for Microsoft JDBC driver version 8 or later.
  • For Windows 32 or Windows 64, copy the appropriate library (sqljdbc_auth.dll or mssql-jdbc_auth-x.x.x.xxx.dll) to Integration Server_directory\instances\instance_name\lib directory.

Authentication

The user name and password you configure for a connection must be the same as those used to create the tables you use with a specific notification. Otherwise, an exception is generated at runtime.

Other Properties

Specify the parameters in the Other Properties field:

Parameter Description
selectMethod For XA_TRANSACTION only: 
selectMethod=cursor

Required Connection Property Fields

Driver Name Server Name User Password Database Name Port Number Network Protocol
Microsoft JDBC Driver Version 2.2.0019 for Microsoft SQL Server 2000 Yes Yes Yes No Yes No
Microsoft JDBC Driver Version 1.0.809.102 for Microsoft SQL Server 2005 Yes Yes Yes No Yes No

For more information about limitations, see Limitations.

DataDirect Connect

DataSource

Transaction Type DataSource Class
NO_TRANSACTION , LOCAL_TRANSACTION and XA_TRANSACTION 
com.wm.dd.jdbcx.sqlserver.SQLServerDataSource

JDBC Jars

Based on your Microsoft SQL Server architecture, use the required DLL and stored procedure folder found in dd-cjdbc.jar file. For details, see DataDirect Connect documentation.

Required Connection Property Fields

Driver Name Server Name User Password Database Name Port Number Network Protocol
DataDirect Connect for JDBC with Microsoft SQL Server 7 Yes Yes Yes No Yes No

For more information about limitations, see Limitations.

Limitations

Driver Database/

Adapter IS Operating System/

Platform Affected

Limitation Description
Microsoft JDBC Driver Version 2.2.0019 for Microsoft SQL Server 2000 Microsoft SQL Server 2000
  • When running the SelectSQL adapter service using the "not null real" type, the following error appears:

    Value cannot be converted to requested type.

    This is a driver issue for both the DataDirect Connect for JDBC and the Microsoft SQL Server 2000 Driver for JDBC.

  • This driver does not support retrieving table names from a database when the database’s name contains special characters.
  • This driver must have Oracle's JDK 1.3 package javax.sql.* in the Integration Server CLASSPATH before you can enable the adapter connection. If this package is missing, the following error appears:

    unable to configure connection manager javax/sql/DataSource.

Microsoft JDBC Driver Version 1.0.809.102 for Microsoft SQL Server 2005 Microsoft SQL Server 2005
  • This driver returns incorrect data type TEXT, IMAGE, and NTEXT for MS SQL data types VARCHAR(max), VARBINARY(max) and NVARCHAR(max) respectively.
  • This driver returns invalid JDBC data type for MS SQL UNIQUEIDENTIFIER data type.
DataDirect Connect Microsoft SQL Server 2000
  • When running the SelectSQL adapter service using the "not null real" type, the following error appears:

    Value cannot be converted to requested type.