topic Re: Database connect failure even after MSSQL-JDBC jar install in Installing and Upgrading

Database connect failure even after MSSQL-JDBC jar install

fogc1011 — Sat, 16 Nov 2024 00:03:58 GMT

Even after following these directions:

https://community.talend.com/s/feed/0D53p00007vCoLACA0# M1065

and successfully installing the SQL Server jdbc jar:

I still am getting the error message "Connection failure. You must change the Database Settings."

I know the database server is listening at the specified port, I have a valid jdbc jar that is consistent with the version of java I'm running, and I can easily connect to the database in question using SSMS using the login details, etc. specified in the UI. I'm not sure where to go next. Anyone have any ideas?

Re: Database connect failure even after MSSQL-JDBC jar install

fogc1011 — Mon, 14 Jun 2021 15:48:21 GMT

Here's the output of the errors...it appears like there's an issue with my user but, as I mentioned, I can connect via SSMS just fine. I don't have to have anything more than read permissions to the database do I?

Re: Database connect failure even after MSSQL-JDBC jar install

fogc1011 — Mon, 14 Jun 2021 18:27:45 GMT

Ok, adding more detail as I troubleshoot and uncover. I've realized my mistake..... I was connecting SSMS to DB via Windows Auth and when I tried SQL Server Auth using the same credentials in SSMS I wasn't able to connect. After briefly discussing this with DBA, I realized I need to use Windows Auth to connect to TOC (I don't have a SQL Server Auth account available.)

So, I've read through several posts in the community to determine how to go about doing this. According to this post, I've removed all login, database, server, etc. detail from the lower half of the DB Connection form and, instead, added the detail into the String of Connection area on the form using the format MS has specified. All looks good but when I test the connection, I get the following error:

"Connection failure. You must change the Database Settings

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:3a8ec008-d671-44b7-b24d-8ad6af71db63...."

I am still using the Microsoft jdbc because other posts like this one reference a JTDS driver that, according to their SourceForge page is only compatible up to SQL Server 2012.

Should I still use the Microsoft version of jdbc or should I swap over to JTDS even though my server is newer than 2012? Also I assume that because I'm using Windows auth, I don't need to specify more than the server and db in my connection string. Is that accurate too?

Thanks for your help!

Re: Database connect failure even after MSSQL-JDBC jar install

Anonymous — Tue, 15 Jun 2021 04:23:13 GMT

Hello,

In Talend Studio, you can create a SQL Server metadata connection with integrated authentication.

Could you please have a look at this article to see if it helps? https://community.talend.com/s/article/How-do-I-configure-integrated-authentication-for-a-Microsoft-SQL-Server-connection-in-Studio-b1dvv

Best regards

Sabrina

Re: Database connect failure even after MSSQL-JDBC jar install

fogc1011 — Tue, 15 Jun 2021 16:40:08 GMT

Thanks Sabrina - that worked! Based on the articles I referenced earlier, I'd already placed the dll in the System32 folder, but mistakenly thought I had to leave all connection property boxes blank and, instead, manually provide them in the String of Connection box. Filling out the connection properties in their assigned boxes on the form allowed me to connect.

Re: Database connect failure even after MSSQL-JDBC jar install

fogc1011 — Tue, 15 Jun 2021 16:49:03 GMT

Follow up question for clarification and future installs for other users in my org.

Do we need to both:

Install the MSSQL-JDBC jar AND
Place the sqljdbc_auth.dll file

To make this work or do we only really need to do #2?

Thanks again!

Re: Database connect failure even after MSSQL-JDBC jar install

Anonymous — Wed, 16 Jun 2021 02:39:31 GMT

Hello,

What's java version are you using? Check in your Java if you have dlls for sql server. You should have sqljdbc_auth.dll when you are using integratedSecurity=true.

- Downloaded JDBC Driver package and moved sqljdbc_auth.dll to System 32

- Defined integratedSecurity=true in Additional parameters

Please restart your studio to apply the setting.

Best regards

Sabrina