External/kerberos authentication not working

[teancom]

We're attempting to use kerberos authentication to connect to an Oracle 12.x database, using go-oci8 commit id 96a3284b269c. We can connect to the database using go-oci8 with username+password, and we can connect using sqlplus+kerberos, but when we try to use go-oci8+kerberos, it fails with:

Failed to connect to DB: ORA-01017: invalid username/password; logon denied

This problem persists whether we use a connect string directly or a host defined in our tnsnames.ora.

After some digging, we were able to successfully connect by messing around with dsn.externalauthentication. By reversing the test at https://github.com/mattn/go-oci8/blob/master/oci8.go#L323 such that it does not execute that block when dsn.externalauthentication is set to false, the connection works and we're able to proceed normally.

Now to the meat of our question: what is the purpose of dsn.externalauthentication? Based on the name, we presume it's when the library itself isn't handling authentication via username and password. However, it appears to be somewhat overloaded, as setting it to true requires not just the username and password fields to be empty, but also the dsn.Connect field (https://github.com/mattn/go-oci8/blob/master/oci8.go#L125). And to reinforce that having an empty hostname is required for external auth, down at line https://github.com/mattn/go-oci8/blob/master/oci8.go#L279 there is another test for dsn.externalauthentication where, if it's true, the database server to use is set to nil.

If dsn.externalauthentication is to be used for our use-case (kerberos authentication), how do we pass in the name of the server to connect to? And if it's not for our use case, then how to we configure our connection string to work?

Finally, we're happy to provide a pull request that converts "external authentication" to only requiring username and password to be empty, without also requiring the hostname to be empty, if that sounds reasonable. We're also happy to hear what we're doing wrong as we're not actually well versed in kerberos authentication (this being our first attempt at using it), and if we're missing something obvious, that doesn't require patching go-oci8, that's even better.

Thanks for your time and I'll be glad to answer any follow-up questions you have.

David

Example code that we're using to test our connection:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-oci8"
)

func main() {
    // WORKS
    db, err := sql.Open("oci8", "username/password@TEST")
    // DOESN'T WORK
    // db, err := sql.Open("oci8", "/@TEST")
        // NOR DOES
    // db, err := sql.Open("oci8", "@TEST")

    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()
    if err = db.Ping(); err != nil {
        fmt.Printf("Error connecting to the database: %s\n", err)
        return
    } else {
        fmt.Printf("Connected successfully\n")
    }
}

where TEST is defined as a server in our tnsnames.ora:

TEST =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = test.foo.us-west-1.rds.amazonaws.com)(PORT = 1521))
 (CONNECT_DATA =
   (SID = ORCL)
 )
)

[MichaelS11]

I have not used external authentication or Kerberos authentication, so not sure how much I will be able to help you, but I will try.

Did you setup your sqlnet.ora file and other config files?

From a Goggle search, found this link:

https://www.ateam-oracle.com/configuring-your-oracle-database-for-kerberos-authentication

Which seems to point to that a lot of the configuration (like which server to connect to) is done with config files and okinit.

[teancom]

Yeah, as mentioned we have a setup that works great when using sqlplus, the Oracle CLI. Our tnsnames.ora, sqlnet.ora, and krb5.conf are all configured correctly and working. It's only when using go-oci8 with that configuration that doesn't work. But removing the requirement to not have the hostname configured makes go-oci8 work just fine. Do you happen to know what the reasoning behind tying those two things together was?

[MichaelS11]

Could you post (edit out what you need to) your sqlnet.ora and krb5.conf files? I am curious.

[teancom]

Our sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES=(KERBEROS5)
SQLNET.KERBEROS5_CONF=/tmp/krb5/krb5.conf
SQLNET.KERBEROS5_CC_NAME=/tmp/krb5/kerbcache
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=/tmp
TRACE_FILE_CLIENT=nettrace
TRACE_LEVEL_CLIENT=16

Our krb5.conf:

[libdefaults]
    default_realm = AD.EXAMPLE.COM
    default_ccache_name = /tmp/krb5/kerbcache
    kdc_timesync = 1
    ccache_type = 4
    forwardable = true
    proxiable = true
    fcc-mit-ticketflags = true
[realms]
 ad.example.com = {
  kdc = ad.example.com
  admin_server = ad.example.com
 }
[domain_realm]
 .instabase.com = EXAMPLE.COM

[MichaelS11]

See what you are talking about, working on a PR.

[teancom]

We have one cooking here, if it helps. https://github.com/heymian/go-oci8/pull/1/ It works for us, but we haven't added tests or productionized anything.

[MichaelS11]

https://github.com/mattn/go-oci8/pull/359

[MichaelS11]

@teancom All good?

[teancom]

We're about to test it out this morning, but it certainly looks like exactly what we were looking for. Thank you ever so much!

[MichaelS11]

Welcome

Could you close this?

[teancom]

It works in our test environment, but we want to test it against the real thing, which we hopefully should be able to do tomorrow. I will absolutely update/close this at that point. Thank you!

[teancom]

I'm diving into this more right now, but as an update this is not working for us. And more specifically, because I'm not very bright, though I thought I'd tested in our test environment, I hadn't (I was still using our branch). More to come as I figure it out.

[MichaelS11]

Any luck?

[teancom]

Alright, so after working a bit I was able to confirm that it's working on our test environment. Unfortunately, the production env is actually controlled by another company and it's, well, taking a while. I'm going to say that given that it's doing what we wanted in test, we can close this ticket. If it breaks in prod and we can figure out why (i.e., reproduce in our test environment) and narrow it down to a problem with go-oci8, then I'll open another issue with that information.

Thank you again for being so super-responsive and helpful. It is truly wonderful of you, and I couldn't be happier. ❤️

[MichaelS11]

@teancom Most welcome :)