Archives

Tagged ‘jdbc‘

ODI and Integrated Security/Authentication with Microsoft SQL Server

Setting up integrated security with the SQL Server JDBC driver can be tricky. You often end up with the message “This driver is not configured for integrated authentication”. Below are the steps that you need to perform to get this to work.

Note: This will only work where the ODI agent has been installed on a Windows OS.

1. Download the Microsoft SQL Server JDBC Driver 2.0 from the Microsoft website. This is compatible with versions 2000, 2005, and 2008 of MS SQL. You will need to be up and running on JDK 5.0 or later with your ODI installation. If you haven’t done so already now is the time to install JDK 5.0 or even better JDK 6.0.
2. Unzip the content of the download.
3. The driver comes with a separate dll (sqljdbc_auth.dll) that will allow you to connect to SQL Server via your Windows login. Copy the sqljdbc_auth.dll to the \oracledi\drivers folder.
4. Copy the sqljdbc4.jar file to the \oracledi\drivers folder
5. Open odiparams.bat and add the following entry: set ODI_ADDITIONAL_JAVA_OPTIONS=”-Djava.library.path=\oracledi\drivers”, e.g. set ODI_ADDITIONAL_JAVA_OPTIONS=”-Djava.library.path=c:\oracle\oracledi\drivers”

odi_mssql1

6. If you have set up the ODI agent as a Windows service you need to re-install the agent. Stop the agent and uninstall the agent with the agentservice –r command. Next you need to edit the “snpsagent.conf” file in the \tools\wrapper\conf directory.

odi_mssql2

7. Re-install the ODI agent with the “agentservice -i” command.
8. Open Topology Manager module and create Physical Server for MS SQL technology. Leave username and password blank

odi_mssql3

Go to JDBC tab and enter

jdbc:sqlserver://<name_of_server>:<port>;databaseName=<db_name>;integratedSecurity=true;

9. One last thing. Of course, your ODI agent must not run as the Local System account but use a network account that has the appropriate MS SQL privileges to log into the SQL Server.

odi_mssql5

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Losing timestamp in ODI when using ojdbc14.jar

The ojdbc14.jar version of the JDBC driver is losing timestamp information when returning DATE datatypes from Oracle. As per Oracle JDBC driver FAQ this is a known issue. As a workaround it is suggested to set the oracle.jdbc.V8Compatible JDBC parameter to true. You can set this parameter in ODI when defining a Topology:

odi_jdbc

However, a better solution for this is to use a 1.5 JDK in combination with the 11.1 ojdbc5.jar driver. This is supported for both Oracle 10G and 11G. Refer to note 807235.1 on Metalink for  information which JDBC and JVM/JDK versions work together.

In a first step stop all ODI agents and download the 1.5 JDK from the SUN website. Next install and point the ODI_JAVA_HOME environment variable to point to the JDK, e.g. E:\Program Files\sun\jdk\jdk.

Next go to the ODI drivers folder and rename the ojdbc14.jar to ojdbc14.jar.bak. We now need to download the 11.1 driver from the Oracle website. Once downloaded put the ojdbc5.jar into the ODI drivers folder.

We are now ready to test this. Log into the ODI Designer module. Expand the Projects folder and right click Variables and select Insert Variable. Give the variable a name and change to the Refreshing tab. From the Schema dropdown select an Oracle Schema. In the Select Query textbox type the following:

SELECT sysdate FROM dual

Click the Refresh button and change to the History tab. Next click the Refresh icon and you should get timestamp information.

odi_jdbc_timestamp

One more thing: You will need to re-install your agents as windows services.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Troubleshooting ODI connectivity to SQL Server Express 2005

It took me an hour or two today to connect to an SQL Server Express 2005 instance. This was mainly due to some default settings in SQL Server Express 2005.

Here is what you need to do to get this to work.

1. Make sure that SQL Server and SQL Server Browser services are started

Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager > SQL Server 2005 Services

Start any services that are stopped

2. Remote connections need to be enabled

Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration > Remote Connections

Make sure that the Local and remote connections radio button is selected:

odi_mssql1

3. JDBC driver not installed in ODI drivers folder

Download the driver from: http://www.microsoft.com/downloads/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en

As far as I understand it you need to download the JDBC 1.2 driver. The JDBC 2.0 driver does not support JRE 1.4.2.

Next stick the sqljdbc.jar file into the odi drivers folder: \oracledi\drivers

4. Set TCP port for IP addresses

Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager > SQL Server 2005 Network Configuration > Right Click TCP/IP > IP addresses tab

odi_mssql2

5. Configure Data Server in ODI

In the screenshot below we are connecting the Data Server to the Adventure Works sample database (via Topology Manager). Fill out the fields as per screenshot.

Note: In a production environment you should not use the sa user to connect to a SQL Server database. Instead create a separate user for each database.

odi_mssql3

Change to the JDBC tab

JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL: jdbc:sqlserver://;selectMethod=cursor;databaseName=;integratedSecurity=false

odi_mssql4

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)