Back in June, I posted on how to Use SQL Server with Pentaho Data Integrator. That article covered using Microsoft SQL Server (MSSQL) authentication to connect to your MSSQL databases. This time around, we’ll look at the other type of authentication that you can encounter when working with MSSQL – Active Directory authentication (AD for short).
If you are not familiar with AD, its a centralized authentication mechanism allowing access to the various hardware and services in the network. By centralizing the authentication process, the same user account can be used to access multiple resources, and it eliminates some of the setup needed to enable those users on various systems. Most DBA’s prefer to use AD authentication for those reasons, and if you will be using PDI to access multiple MSSQL systems, you’ll probably want to become familiar with setting it up.
- Although Microsoft provides their own JDBC driver, which I covered in the previous post, this time around we will be using an open source driver called jTDS. You can download the driver from SourceForge using this link. Download the most current version (at this writing it was version 1.3.1).
- Extract the archive file and open it. Copy the jtds-1.3.1.jar file to the Pentaho lib folder on your system. On my system that is currently /opt/pentaho.data-integration/lib.
- In the folder where you extract the archive, locate the subfolder matching your systems architecture (x64, x86 or IA64). Open it, and open the SSO subfolder.
- Copy the ntlmauth.dll file to a folder on your path. (On CentOS from a command prompt enter: ECHO $PATH$ to see the current path). On my system, I copied the file (as root) to the /usr/local/bin folder.
- Open the Pentaho DI GUI (aka Spoon) and start a new job. Click on the VIEW tab in the Explorer panel.
- Right click on Database Connections, and choose NEW to open the Database Connection window.
- Enter a name in the Connection Name box to identify it.
- Scroll down in Connection Type and choose MS SQL Server.
- In the Access panel, make sure Native (JDBC) is selected.
- In the Settings panel, enter your server’s hostname or IP address, the database you want to connect to, the port SQL Server is using (by default its 1433), and the user name and password in the appropriate fields. You can leave Instance Name empty unless your DBA tells you the server is using a named instance. It should look something like this:
- In the left most panel, select Options. The right panel will refresh, and will probably only have one value entered: “instance”. Leave the value as is.
- Add a parameter called “integratedSecurity” (watch the text case), and set the value to true.
- Add another parameter called “domain” and set the value to your network’s domain name. (You can use the full domain name or the shorthand one).
- Click the TEST button at the bottom of the screen, and you should be rewarded with a successful connection window. Click OK and you are done.
Pentaho is a trademark of Pentaho, Inc.