Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: Connection to DB2 using logged on user

    Hi all,
    I would like to make an ODBC Connection to DB2 without passing any username and password but letting it use the currently logged on user's credentials.

    This is possible with SQL Server, is there a way to do it with DB2 also?

    Thanks a lot

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    What is your DB2-client version and fixpack, and DB2-server version and fixpack ?

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Server is DB2 ESE 9.7.4.
    Also client is 9.7.4.

    For completeness the connection string I use for SQL Server is
    "Driver=SQL Server;Server=localhost;Database=master;UID={};PWD ={}"

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Depending on which flavour of client package you are using, see:
    PWD CLI/ODBC configuration keyword - IBM DB2 9.7 for Linux, UNIX, and Windows

  5. #5
    Join Date
    Mar 2012
    Posts
    120
    Hi db2mor,
    the only hint I see there is to use password phrase in db2cli.ini when accessing DB2® for z/OS® servers, which is not my case.

    Is there anything else I should look at?

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Read it again.
    A password is a different thing to a password-phrase.
    Then (subject to your client package) try it out.
    Of course, if it works, this is not a secure mechanism, as the page points out.

  7. #7
    Join Date
    Mar 2012
    Posts
    120
    Sorry I didn't mention I'm not interested in z/OS, I have servers on Linux and clients on Windows/Linux.

    As far as I understand the passphrase can be used just for z/OS servers.

    And evebn if it works, what I would like to have is a way to tell the DB2 CLI: "Please use the credentials of the currently logged on user" without writing his password in whatever place

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The password-phrase functionality is specific to Z/OS.
    But the password is not , and works for LUW servers that have authentication on server-side.
    If you want to avoid (quite rightly) having the password in a file then checkout Kerberos authentication.

  9. #9
    Join Date
    Mar 2012
    Posts
    120
    How is it that db2cmd is able to access db2 locally without entering a password?
    Also the create database command, if you don't specify user/using takes the logged on user as default.

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    When the CLP is local to the DB2-server, you are already authenticated (assuming DB2-server authentication value is Server or Server_Encrypt or any server based value) *and* CLP is designed to work that way (i.e. it can derive the userid to use for the auth-id and not require a password). This is documented somewhere in the infocenter, though I can't find it right now.

    But from a Windows-client (i.e a remote client), with the DB2-server configured for authentication=server or server_encrypt etc), the db2-client software cannot know the user password (unless it gets told it via command-line, or SQLConnect or SQLDriverconnect, or db2cli.ini etc).

    Hence if your ran your Windows application directly on the DB2-server (i.e. on same box) I would expect the behaviour to be consistent with the CLP *only if* the same CLI packages get used under the covers.

    Perhaps SQL-Server can use kerberos protocol (or some other shared-key mechanism) by default on remote clients, but DB2 does not have this as default behaviour, although DB2 can be configured to operate in that manner.

    You could ask for SQL-server implementation details on its forum (i.e. how it allows passwordless connections to *remote* databases).

    That's my 2 eurocents!

  11. #11
    Join Date
    Mar 2012
    Posts
    120
    Ok that sounds interesting, it could be enough for me to connect via ODBC locally.

    You say:

    Quote Originally Posted by db2mor View Post
    Hence if your ran your Windows application directly on the DB2-server (i.e. on same box) I would expect the behaviour to be consistent with the CLP *only if* the same CLI packages get used under the covers.
    Now I'm running from the same application:
    - db2cmd to create the database without credentials
    - the odbc SQLDriverConnect passing a connection string without UID and PWD

    The first goes fine, the second fails with SQL30082N (wrong username/password)

  12. #12
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    On my Win7 x64 with DB2 v9.7.5, the odbcad32 gui lets me connect to a local DB2 database without either a userid or a password...

    Ditto for db2ca ->selected -> test connection -> obdc (only) - no uid/pwd needed for local DB2 databases.

    Your application might have a problem...

  13. #13
    Join Date
    Mar 2012
    Posts
    120
    Yes...
    the problem of my application is that I don't know which connection string to pass to SQLDriverConnect to have the locally logged on user connect to my db.

    I tried simply omitting UID/PWD, setting them as empty strings, using "Trusted_Connection=Yes", but no way.

    Is there anyone who knows the correct connection string to use in this case?

    Thanks

  14. #14
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The IBM-supplied sample program called clihandl.c (when built) connects OK to a local database without a userid/password.
    You might want to study clihandl.c and utilcli.c and compare the code with your own application.
    You might not need any special attributes (no trusted connection)

  15. #15
    Join Date
    Mar 2012
    Posts
    120
    Thanks db2mor, this hint has been very helpful!

    I discovered that utilcli.c uses SQLConnect instead of SQLDriverConnect, and implementing it in my application works fine.

    Anyway, SQLConnect has some limitations compared to SQLDriverConnect, so I really appreciate to have same functionality with SQLDriverConnect.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •