How give AUTHENTICATION parameter to engine url ?

[aegleinformatiquemedicale]

Hello,

Works fine with ibm_db or ibm_db_dbi : ConStr = "DATABASE=Mybasename;HOSTNAME=MyIpServer;PORT=MyPort;AUTHENTICATION=SERVER;PROTOCOL=TCPIP;UID=MyUser;PWD=MyPassword"

_conn = ibmdb.connect(ConStr, "", "")

=> OK

But with sqlalchemy taht doesnt work because i don't know how give the AUTHENTICATION=SERVER

_engine = create_engine('ibm_dbsa://MyUser:MyPassword@MyIpServer:MyPort/MyBaseName')

I tried with ?AUTHENTICATION=SERVER at the end of URL

conn = engine.connect()

=> Connection failed (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001 SQLCODE=-30082

[amukherjee28]

Hi @aegleinformatiquemedicale

With Alchemy the correct way to pass parameter along with Connection String is using ?Parameter_name along with the Connection String URL.

Engine(ibm_db_sa://USER>:***@<HOSTNAME:/?Authentication=SERVER) => Creating Table demoTab1

2020-07-28 19:58:12,362 INFO sqlalchemy.engine.base.Engine SELECT 'test plain returns' AS anon_1 FROM SYSIBM.SYSDUMMY1 2020-07-28 19:58:12,362 INFO sqlalchemy.engine.base.Engine () 2020-07-28 19:58:12,914 INFO sqlalchemy.engine.base.Engine SELECT 'test unicode returns' AS anon_1 FROM SYSIBM.SYSDUMMY1 2020-07-28 19:58:12,915 INFO sqlalchemy.engine.base.Engine () =>>>> DBMS VERSION = 11.05.0100 =>>>> DBMS_NAME = DB2/LINUXX8664

In your case the error message states use of invalid username or password. Please check the whether you are passing proper arguments in the connection string URL.

Thanks.

[aegleinformatiquemedicale]

Hello,

So i retried with this url :

_engine = create_engine('ibm_dbsa://MyUser:MyPassword@MyIpServer:MyPort/MyBaseName?AUTHENTICATION=SERVER')

I put some print trace in ../Virtualenvs/alembic-py369/lib/python3.6/site-packages/ibm_db_sa/ibm_db.py In create_connect_args function (Line 158)

_DEBUG create_connect_args DEBUG url=ibm_dbsa://MyUser:MyPassword@MyIP:MyPort/MyBaseName?Authentication=SERVER DEBUG url.host exist => dsn=DRIVER={IBM DB2 ODBC DRIVER};DATABASE=MyBaseName;HOSTNAME=MyIP;PROTOCOL=TCPIP;PORT=MyPort;UID=MyUser;PWD=MyPassword;

=> (ibm_db_dbi.OperationalError) ibm_db_dbi::OperationalError: [IBM][CLI Driver] SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001 SQLCODE=-30082

I wrote in code (same function, create_connect_args) : dsn_param.append('AUTHENTICATION=SERVER')

This time everything is OK

So this part of url is not taken : ?Authentication=SERVER Any cleaner solution to resolve this problem ?

Thanks

[imavo]

Be certain that the target Db2 instance/subsystem is configured for server authentication!

Instead of ?, use semicolon ; both before and after the database-name like so:

engine = create_engine('ibm_db_sa://MyUser:MyPassword@MyIpServer:MyPort/MyBaseName;AUTHENTICATION=SERVER;')

[aegleinformatiquemedicale]

This works

engine = create_engine('ibm_db_sa://MyUser:MyPassword@MyIpServer:MyPort/MyBaseName;AUTHENTICATION=SERVER')

Thanks