Configure Talend Job to Connect to SQL Server with Windows Integrated Authentication

The main way to access MS SQL Server in Talend is SQL Server authentication when user name and password must be supplied. SQL Server credentials are compromised as password is in plain text. In some cases, this is only a way to connect to SQL Server. There is another option to use Windows Integrated authentication. It's safe way because Talend jobs don't contain either user name or password. Credentials are provided by Windows Operation System or another service in background. Windows Integrated authentication is common in Windows environment as it's used Active Directory.

1. Download jTDSSQL Server and Sybase JDBC driver

2. Extract ntlmauth.dll library from the driver package.

As Talend is 64 bit now, it has to be 64 bit library as well.

jtds-1.3.1-dist
│
└───x64
    └───SSO
         ntlmauth.dll

3. Place the extracted library to Talend root folder

4. Set Up tMSSqlInput Talend Component

There are 2 options. The first one is to set up tMSSqlInput component directly and another one uses tMSSqlInput component in conjunction with database connection.

Option #1.

• tMSSqlInput component settings. Leave Username and password empty with double quotes. The advanced settings has to include IntegratedSecurity=true in double quotes.

Option #2

• Create a new database connection. Leave Username and password blank. Add IntegratedSecurity=true to Additional parameters.

• MSSqlInput component settings with the database connection.

5. Testing

• Create 2 jobs. Each job contains 2 components: (1) tMSSqlInput and (2) tJavaRow.

• Change settings of tMSSqlInput component as per Set Up tMSSqlInput Talend Component accordingly.

• Change settings of tJavaRow component as below.