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

Question

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 ?

Answer 1

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!

Answer 2

It works correctly for me (Db2-LUW v11.5.5.0).

It also works correctly on Win10 with Db2-LUW v11.5.4.0.

I used the same code as in the IBM provided sample program dbconn.c (available on github, from the Db2 Knowledge Centre, and in the samples directory of the Db2-LUW instance). It shows three methods including SQLDriverConnect (which is the one that I tested locally).

I notice what when it works the connection-string reported by printf() is for example: DSN=sample; UID=; PWD=; AUTOCOMMIT=0; CONNECTTYPE=1;

So my suggestion for you is to try getting the IBM example program built and working ( samples/cli/dbconn.c - slight modifications to files/build may be necessary as IBM seems to not keep their sample code current in the samples directory, maybe different on github). IBM provides sample scripts to build these programs (bldapp etc) located in the same directory tree.

For troubleshooting, double check how the alias to which you are connecting is defined. Specifically examine both the catalog and the (for IBM supplied drivers) db2dsdriver.cfg contents, manually creating the latter if necessary. The SQL30082N reason 3 will result for a non-local database.

You can also take a CLI trace to observe what is happening under the covers, refer to the Db2 Knowledge Centre for details of running the db2trc command, dumping its output, formatting its binary output into readable text etc.