ODI and Integrated Security/Authentication with Microsoft SQL Server

Uli Bethke MS SQL, Oracle Data Integrator (ODI)

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes

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 oracledidrivers folder.
4. Copy the sqljdbc4.jar file to the oracledidrivers folder
5. Open odiparams.bat and add the following entry: set ODI_ADDITIONAL_JAVA_OPTIONS="-Djava.library.path=oracledidrivers", e.g. set ODI_ADDITIONAL_JAVA_OPTIONS="-Djava.library.path=c:oracleoracledidrivers"


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 toolswrapperconf directory.


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


Go to JDBC tab and enter


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.


Teach me Big Data to Advance my Career

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

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


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

Jython Essentials (O'Reilly Scripting)

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.