Data Integration 004: Talend, Connect to MSSQL

I ran into loads of problems trying to connect to a Microsoft SQL Server (2008 R2) using my Talend job to update a DB.

(Disclaimer: there was no network issues, the server was definitely reachable.)

The MSSQL Connector that does not work

First, I tried to use tMSSqlConnection to connect to the server, but the job could not even compile due to a missing library mssql-jdbc.jar. Fine, typically the open studio will prompt me to install the additional required libraries after I agree to some terms and conditions, but No! This time round the “download and install” button for said library was totally greyed out, there was no way I could download it from within the studio. After taking advise from forums and Stack Overflow, I decided to manually download the required Java Archive file and install it. Honestly, I am not even sure if it is the right file. As expected, it could not work.

Next, I turned to the tOleDbConnection as recommended by some of the posts in the forums. Nice! It managed to build and run but it threw up an error. I could not tell what was wrong with the connection from the open studio console, so I started to look at the access logs from the SQL Server Management Studio (SSMS). “The login is a SQL Server login and cannot be used with Windows Authentication”. It turns out that my SQL server was configured for Windows Authentication only, but the Talend connector inherently do not support Windows Authentication.

Some online sources had recommended that changing the authentication mode was simple enough, except that you need to restart the server. It was between this option and trying to enable Windows Authentication on the Talend connector, which I assumed was not going to be simple as it was not provided out-of-the-box. I was like, let’s go for the easy way.

Mixed Authentication Mode

And here is where the nightmare begins. After changing the setting to mixed authentication mode, the Talend connector could successfully connect to the server. However, at the same time all other existing applications that connects to DBs in the server all broke. Man, I did not see that coming. The “Mixed Authentication” had misled me into thinking that restarting the server was a good idea. There was simply too many applications for me to troubleshoot alone. The best way out of this is to revert back to the original configuration. (I did perform a clone of the server VM in the event that the development doesn’t go well, I will restore the entire server instance from the clone.)

For some weird reasons, I could not log in to the server using SSMS no matter how I tried. Then it dawn on me that the admin account was a Windows account, and if the Windows Authentication mode had been denied, I would need to use another admin account permitted for SQL Server Authentication mode. Nope. There is no such admin account (not that I know of. Did not assume there was a default admin account and that people were lazy enough to have kept that account accessible.). At this point I wished for a detonation button to wipe the server off the face of the Earth.

So there is no way for me to change the authentication mode at all. There is only one last thing to do. Restore the entire server instance to some point back in time.

And that is how the episode ended. Eventually I had to stick with using tOleDbInput with Windows Authentication. This requires the janet-win<32 or 64>.dll (depends on whether you have a 32bit or a 64bit system) to be available in the java.library.path of your JVM. I am very thankful for this guide providing all the required information.

Update or Insert

The job worked well. I wanted to modify a table in the DB on the condition that if a record do not exist, a new record shall be inserted, else the existing record will be updated. This is when I come across the convenient option:

tOleDbOutput.JPG

In my scenario, I will not often have to insert a new record, therefore the “update or insert” option is more efficient. More difference between the options are explained here.

Build and Export

After completing the job, I built and exported it as a Windows batch file (.bat) to run on a production system. That it where I run into another roadblock. After triggering the batch script with the Windows Task Scheduler, the console kept throwing an error, “UnsatisfiedLinkError: somepath\janet-win64.dll: Can’t find dependent libraries”. This does not mean that the system cannot find janet-win64.dll, as I have also imported that, but those Dynamic-Link Libraries (DLLs) that janet-win64 depends on that were missing.

That is just crazy to me, as my development environment was made from an exact VM clone of the production instance. How could there ever be anything missing? I googled so hard for two hours, then it finally hit me. I installed SSMS in the development server. And that installation, which the Talend job does not depend on, had also installed the latest Visual C++ Redistributable packages, which enabled janet-win64.dll to function and in turn enabled the Talend job. It totally blew my mind that these unrelated actions enabled the success of the Talend job. There was also no way for me to discover this dependency during development as I had installed SSMS before starting work on the Talend job.

TL; DR

Do not change the SQL server authentication mode if there are live connectors running. Always keep track of changes made in a server even though it may be unrelated to the development.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s