topic With build script I suddenly get "java.sql.SQLException: Access denied for user" in Talend Studio

With build script I suddenly get "java.sql.SQLException: Access denied for user"

apletschette — Sat, 16 Nov 2024 00:12:37 GMT

Hello,

I have a running script in "Talend Open Studio 7.3.1" which does nothing else then get MSSQL data to transfer it to a MySQL database. In the studio everything works fine and so I build my script.

All connection data is put in Contexts and in the build menu I did choose the following parameters below.

When I run the script "importTicketing_run.sh" it on my windows-development machine with "mintty 2.7.3" it also connects to the database just fine. When I run the same script on the target Linux-server I do get the following exception:

[root@vm-***** plan]# sh /home/plan/Talend/importTicketing/importTickekting_run.sh

Exception in component tDBOutput_1 (importTickekting)

java.sql.SQLException: Access denied for user 'talend'@'vm-*****' (using password: YES)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:926)

at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1748)

at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1288)

at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2506)

at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2539)

at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2321)

On the other side I do have running talend-scripts running on this host based on talend 7.1. Also I can connect to the mysql-database with the login-name above:

[root@vm-**** plan]# mysql -u talend -p************************

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1148191

Server version: 5.5.56 MySQL Community Server (GPL) by Remi

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

I actually have no clue what could went wrong ...

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

gjeremy1617088143 — Thu, 29 Apr 2021 12:53:28 GMT

on the build job menu you choose default context instead of prod, is that ok for you ?

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

apletschette — Fri, 30 Apr 2021 07:52:17 GMT

thx for pointing thix, but yes "default" should be "prod" :

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

gjeremy1617088143 — Fri, 30 Apr 2021 12:47:44 GMT

when you choose default in build windows ,you use default name context not prod name context (as you say you use prod as default, but you have also a default name context).

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

apletschette — Fri, 30 Apr 2021 13:07:37 GMT

I did test this, but same problem, it's really weird

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

apletschette — Wed, 05 May 2021 13:36:57 GMT

The problem was quite simple:

I migrated from Talend7.1to Talend7.3 and I did not update my "lib" directory on the target server, so some ".jar" file seems no longer been compatible. I know updated the directory and everything is fine.

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Mon, 27 Sep 2021 13:51:46 GMT

hi , the problem happens to me still in 2021 with talend 7.3 . i build my job after testing it and it works totally fine in the studio. but when I run the build .bat file , it crashes and says the same exact error as it showed for you.

Also, i added the user with same name and password on HOST localhost in my MySQL database to ensure it exists and gave it all privileges but yet it does not work in the cmd of windows 10 !!!

@Xiaodi Shi any ideas about this please. I never been able to make even 1 job work after building it! I want to make scheduled tasks but this thing is blocking me from progress. 🙂

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

Anonymous — Tue, 28 Sep 2021 07:55:00 GMT

Hello,

Is the build job from the same machine where Talend studio is installed?

Have you tried to change the datatype of password in context from "password" to "String" and try once to see if this issue still reprp? Any issue on Context value?

Best regards

Sabrina

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Tue, 28 Sep 2021 09:20:57 GMT

thx a lot @Xiaodi Shi

It was all due to password i always type password in the field of password but it seems it does not save it well , now i used context variable named password and type String and value is my password ! it worked in CMD as a charm 😍

anyway i tried these before and it ouput this next

yes it is build in same machine , i created 3 users to ensure it gets the specific access to the DB when running ,i created localhost ,my fixed ip ,my domain user name as 3 hosts available .

they work in Talend as i tested today , but they don't work in cmd and it says :

java.sql.SQLInvalidAuthorizationSpecException: Could not connect to address=(host=MY_IP)(port=3306)(type=master) : Access denied for user 'root'@'MY_MACHINE_USER' (using password: YES)

and also it says for the Ip as host :

java.sql.SQLException: Access denied for user 'root'@'MY_MACHINE_USER' (using password: YES)

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

Anonymous — Tue, 28 Sep 2021 10:35:31 GMT

Hello,

I have made a testing from my side and I can't reproduce the issue with v7.3.1, the job works also when I export the job script. As I did, you export all the parameter as context variable.

When you have a context variable whose type is password, you do not need to use single or double quotes for the value, only enter the password.

Best regards

Sabrina

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Tue, 28 Sep 2021 10:46:08 GMT

Hello,

@Xiaodi Shi I was not using any context variable for the database config, i always was typing password each time there is a change in the password , or only once when i create the job at first time .

also i build the job on the desktop, not in Talend folder ,which i think may have caused this issue ,it seems (maybe) the talend component in TOS once you save password it directly created an encrypted value of that password somewhere else in the talend folder so that when you run the job it decrypts it from that position and use it , but when i build it on desktop maybe it loses track of the password to be decrypted when launching the bat file ! (this was what i concluded from inspecting the java file in the build)

for now , i use password with String type and i just type its value with no single/double quote already 😉 .

thank you again for the hint .

Best regards.

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

Anonymous — Wed, 29 Sep 2021 03:01:10 GMT

Hello,

Thanks for your detailed description to repro this issue. I will make a testing on the desktop to see if it loses track of the password to be decrypted when launching the bat file.

There is no issue for you when you launching the bat file in the talend folder?

Best regards

Sabrina

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Wed, 29 Sep 2021 09:03:32 GMT

@Xiaodi Shi hello again ,

i tried building with password like old method directly putting the password in the component TinputDB pass field , and built the project under talend directory and it did not work from bat file , it shows same error as access denied even when i put the build inside

C:\Talend\TOS_DI-Win32-20200219_1130-V7.3.1\workspace\LOCAL_PROJECT\poms\jobs\process\Myjobs\MyDBtask\

where my DBtask is the same project i want to build , i said maybe the password is there somewhere else so after the build it can find out the right pass to decrypt but nothing happened

in the same context i want to ask @Xiaodi Shi , how can i modify the java code behind the job itself? when i try to do it it always erase my modifications from job java file and build it as it was originally created , also even if i modify java code and RUN the job also it erase all what i did .

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Wed, 29 Sep 2021 09:21:50 GMT

@Xiaodi Shi

my last post about this bug

here are the 2 codes from building with context var VS normal process (i added *** just to hide the keys

final String decryptedPassword_tDBInput_1 = routines.system.PasswordEncryptUtil.decryptPassword("enc:routine.encryption.key.v1:fW**9oc/FDn***al7***********2liq+Gv***UfA=");

String dbPwd_tDBInput_1 = decryptedPassword_tDBInput_1;

--------------------------------------------------------------------------------------------------

final String decryptedPassword_tDBInput_1 = context.password;

String dbPwd_tDBInput_1 = decryptedPassword_tDBInput_1;

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

Anonymous — Thu, 30 Sep 2021 02:46:24 GMT

Hello,

Could you please raise a jira issue of DI project on talend bug tracker?

Best regards

Sabrina

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

Anonymous — Thu, 30 Sep 2021 02:50:27 GMT

Hello,

You can not modify the generated Java code of job, the change will be not be saved.

If you want to use own code, please use tjavaRow, tJava... components to implement it or use your own Routines.

Best regards

Sabrina

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Thu, 30 Sep 2021 07:34:16 GMT

i don't really know how to do it .

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

aminegac — Thu, 30 Sep 2021 07:35:14 GMT

i use those possibiliteis already but they cannot for example read hidden variables that are used in other components

Re: With build script I suddenly get "java.sql.SQLException: Access denied for user"

Anonymous — Thu, 30 Sep 2021 07:42:11 GMT

Hello,

Here is talend bug tracker portal:

https://jira.talendforge.org/secure/Dashboard.jspa

You can register this portal by going here if you don't have account for talend bug tracker…..