How to have jobs connect to a Microsoft SQL DB with Windows Authentication accounts

Question

How to have jobs connect to a Microsoft SQL DB with Windows Authentication accounts?

Answer

Configure by following these steps:

  1. Download the JTDS driver, extract the contents, and find the ntlmauth.dll.
  2. Move the ntlmauth.dll file to one (or both) of these locations: C:\Program Files\Java\JDK_version\bin\ntlmauth.dll or the Windows folder (C:\Windows\System32\ntlmauth.dll).
  3. Format the connection string in your new database connection (General JDBC) as follows:

    jdbc:jtds:sqlserver://<HOSTNAME>:<PORT>/<REPO_NAME>;user=XXX;password=YYY;useNTLMv2=tru‌e;domain=yourdomain

     

    Note: Ensure that the login, password, server, port, and database fields are blank. If you are doing Windows Authentication, you need to specify them in the connection string, not in the fields.

Version history
Revision #:
4 of 4
Last update:
‎09-29-2018 12:17 AM
Updated by:
 
Comments
Employee

Hi,

Wondering why this article doesn't mention the use of a MSSQLServer component or how to do that with the metadata wizard instead of using a Generic JDBC connector.

Would it work with the Talend SQL Server components as well?

Employee

which version of SQL Server is that solution compatible with as well? Thank you.

Six Stars

Did you try to just add the extra parameter integratedSecurity=true / IntegratedSecurity=SSPI (with the Database and schema) ?

 

Works with the latest Windows SQL Server 2012 to 2017 as specified by MS documentation:

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017

 

On top of that, when you deploy to your JobServer you need to be careful that the user that own the service exist in your database otherwise you will have some surprise (as of version 6.5 of Talend the default user of JobServer is local system user).

 

@adrienlacombe, integratedSecurity solution works fine Metadata Wizard or tXXConnection or directly tXXInput