Talend MSSQL connection using windows authentication

SQL — 21/10/2022 · 21 Comments



#cannyinformatics #talend #mssql
Step by step tutorial to connect MS SQL Server to Talend Open Studio using windows authentication.

Tutorial Prepared by Canny Informatics, http://www.CannyInformatics.com

Tutorial Summary
Solution –
1. Download jtds- SQL server database driver from https://sourceforge.net/projects/jtds/
– Copy jtds-1.3.1.jar to ‘plugins’ of Talend folder
– From X64 – SSO folder ntlmauth.dll to jdk1.8.0_102 – bin folder –
– select folder according to the windows version

2. In SQL Server Network Configuration set all Protocols to Enabled.
– Copy TCP dynamic ports value from TCP/IP protocols property from the end of the page.
–
3. In Talend Metadata – DB Connection;
– DB Type = Microsoft SQL Server
– DB Version = Open Source JTDS
– Specify your Server name and Database
– In additional parameters = integratedSecurity=true “most important”
– In SQL Server Network Configuration set all Protocols to Enabled.
– Use TCP dynamic ports from TCP/IP protocols property.
– For me, Port number: 52890 us this port in ‘port’ property
– Check Connection

► If you need help growing your business using data analytics check out Canny Informatics and Contact us at http://www.cannyinformatics.com/contact for reporting, BI and ETL and data warehousing services.

► Visit our blog at http://www.cannyinformatics.com/blog for more technical articles on ETL, BI, Reporting and Data Warehousing.

21 Comments

  9. In this video we receive comments that they are not able to connect Microsoft SQL Server; even by the following video.
    We request them to CONTACT US on our website e.g. http://www.cannyinformatics.com, we have chat-box available there and we will surely support them. Thanks.

  10. Great tutorial! Although I came into a problem. I have followed your tutorial very closely but at the end I recieve an error message saying: "sso failed native sspi library not loaded. check the java.library.path system property"
    Do you by any chance know how to solve it?

  11. Hi, Thank you so much for this video. I'm unable to find the TCP dynamic port add, as it is showing blank in my case. Could you help me why it is so?

  12. Many thanks, works also with Talend 7.x in BigData version (TAC + nexus) (need to adapt a bit but the core thing was perfectly described). You saved me some hours in my company, you deserve a "+1"

  14. Thank You, it worked for me after following all the steps though I don't see the additional parameters in the Connection window but it still validated connection as successful.

  17. Never save the dynamic port in the connection string. The port will change when SQL server is restarted. If the port is dynamic then ideally you should not be specifying port at all when connecting, just make sure that SQL Server Browser is running. if the driver doesn't support that and you must to provide the port then set it to static in SQL Server Configuration Manager.

    No need to enable ALL the protocols if you are only going to use TCP/IP.

  18. Please can you make another video, have spent days trying to connect with this steps but it wouldn't. Keeps bringing error. My port number wouldn't show in the SQL configuration IP address section

  21. So I got this working with the latest edition of SQL server (the free developer edition). Here is the trick that worked for me after following all of those instructions. When you download the zip file for the jtds jar file and unzip, you need to go into the properties of those two files, the jar file in plugins and the ntlmauth dll and UNBLOCK them. That is all. Works great on the latest version of Talend Open studio 7.1 and sql server 2017 dev edition. Thanks for the help in this tube!

Leave a Reply

© 2023 53GB