Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I would like to use trusted connection when connecting to our DB2 database. As far as we know it should be possible... But I have a hard time finding info on how to do it.

The code below works when I supply my pwd and uid but not trusted connection.

My question is are we wrong when thinking we can use trusted connection and that's why it fails or is the syntax wrong and it "might" succed with correct syntax?

I got tons of results when looking for SQL-Server but DB2 is more of a black box...

Code :

library(RODBC)
kSysName <- "MySys"
kDbName <- "MyDB"
contst <- odbcDriverConnect("Driver=iSeries Access ODBC Driver;
                            System = kSysName; Database=kDbName; 
                            Trusted_Connection = Yes")
Error code:
  
ERROR: state 28000, code 8015
This works:
uid <- "MyUserName"
pwd <- "MyPwd"
contst <- odbcDriverConnect("Driver=iSeries Access ODBC Driver;
                             System = kSysName; Database=kDbName; 
                             uid = uid; pwd = pwd")
Edit:  We're on a iSeries/IBM for this application, other stuff uses SQL-Server
                I am guessing you are using domain Security in Windows. Do you have single signon set up with IBM i?
– jmarkmurphy
                Aug 16, 2017 at 11:53
The term IBM uses for trusted connection is single sign-on. Once you have single sign-on set up with IBM i, you will be able to use your windows login across ODBC to access DB2. That is what is happening with Windows and Active Directory. Until you do that, IBM i has nothing to trust.  Here is a manual that explains it in more detail:
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzamz/rzamzpdf.pdf
Trusted_Connection is a DB2 Connect driver keyword. It is not supported by the IBM i Access ODBC driver. If you want to connect without a username/password using the IBM i Access ODBC driver, you will need to use Kerberos. You can signal this using SIGNON=4 in the ODBC DSN properties or on the connection string eg,
contst <- odbcDriverConnect("Driver=iSeries Access ODBC Driver;
                            System = kSysName; Database=kDbName; 
                            SIGNON=4")
The IBM i Access ODBC driver connection keywords are documented here: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzaik/connectkeywords.htm
To set up Kerberos, refer to the PDF in jmarkmurphy's answer.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.