ODI and Integrated Security/Authentication with Microsoft SQL Server
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.
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
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.
[big_data_promotion]
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)
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
