Search code examples
db2db2-luwdataflex

Implicit local connection (passwordless) using CLI SQLDriverConnect fails with SQL30082N reason 3 PASSWORD MISSING

I am coding using Db2 CLI/ODBC API interface. Windows 10

I can make SQLDriverConnect function to work properly, and can connect to any local or remote db, if I provide username and password (UID / PWD) key in the connection string. This part is working fine.

Ex. If my connection string is something like "DSN=SAMPLE;UID=xxxxx;PWD=yyyyy", the connection works fine.

But per documentation, Db2 should support implicit local connection (when app runs at the same host as the db server), and you should be able to connect to a local DB without providing user/pwd information.

The server code detects whether a connection is local or remote. For local connections, when authentication is SERVER, a user ID and password are not required for authentication to be successful.

Locally, using CLP CONNECT command, it also works implicitly db2 connect to sample without passing the password. which proves the server is properly configured. (authentication is set to SERVER)

But, when trying the same approach using the application CLI code, I receive SQL30082N reason 3 PASSWORD MISSING error, if I just provide the DSN (db name) in the connection string. I tried several variants like:

DSN=SAMPLE;
DSN=SAMPLE;UID=;PWD=;
DSN=SAMPLE=UID=;
DSN=SAMPLE=UID='';PWD='';
DSN=sample; UID=; PWD=; AUTOCOMMIT=0; CONNECTTYPE=1;

All of them fails with the same SQLstate. password missing.

What am I missing ?

--- PS ----

Just for sake of testing, I can make this work ok using a different programming interface, like Powershell using IBM.Data.Db2 .Net provider

# $dbFactory = [System.Data.Common.DbProviderFactories]::GetFactory('IBM.Data.DB2')
# $connection = $dbFactory.CreateConnection()
# $connection.ConnectionString = "Database=SAMPLE" 
# $connection.Open()

# $connection 
...
ConnectionString                     : Database=SAMPLE
Database                             : SAMPLE
IsOpen                               : True
...
DataSource                           : SAMPLE
UserId                               : Samuel
ClientUser                           :
ClientWorkStation                    : Samuel-ALW
InternalClientWorkStation            : Samuel-ALW
...

and my implicit connection from powershell is listed as application at db2 db:

# db2 list applications for db sample

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
SAMUEL   powershell.exe 5316       *LOCAL.DB2.210103171916                                        SAMPLE   1

Why the same is not working from CLI using SQLDriverConnect ?

Solution

  • 1st, Thanks @mao for the assistance. I performed another clean install in a different workstation and it worked there. So, something was "wrong" on my original system.

    After days/hours, I finally found the issue with assistance from @Mitchell Waite

    The cli driver was providing a default username to this database, but no password, hence the PASSWORD MISSING error.

    The default UID was coming from a db2cli.ini file which had this content:

    [SAMPLE]
DBALIAS=SAMPLE
UID=samuel

    I don't recall creating it manually, but I guess this got there when I was playing around with ODBC DSN windows GUI making some tests..

    Anyway, removing the UID from this entry, or deleting the entire [SAMPLE] section, it started to work again, as expected!