Trouble connecting to Oracle database with sqlplus and OS authentication

Question

I am a read-only user to an oracle database, and the DBA gave me two accounts, one with a specific user name and password, and another with OS authentication. The OS authentication is supposed to let me connect by authenticating with my active directory user account that I use to login to my laptop, where I am then running sqlplus.

I can connect and start querying just fine with this command, which shows it is picking up my tnsnames file:

sqlplus username/password@database

However, the version to connect with OS authentication doesn't work:

sqlplus /@database
ERROR: ORA-01017: invalid username/password; logon denied

Am I doing something obviously wrong? I checked in powershell that my user and domain name are the user and domain name that was set up for OS authentication for this database.

I did another test, this time using powershell to do the connection and again with my AD login that should work:

Add-Type -Path "C:\Windows\Microsoft.NET\Oracle.ManagedDataAccess\Oracle.ManagedDataAccess.dll"
 
#This connection string works for the normal authenticated account
$connectionString = "User Id=$username;Password=$password;Data Source=$datasource"
 
#But this one gives me ORA-01017 invalid username/password
$connectionString = "User Id=/;Data Source=$datasource"
 
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()

I also made sure that SQLNET.AUTHENTICATION_SERVICES= (NTS) is in the sqlnet.ora file

Any thoughts?

• anon: In your connection string, make sure you're appending Integrated Security=yes as part of your connection string
• miracle173: Here is a link to Oracle Base explaining OS-Authentication. You can see that your dbuser should be created this way "CREATE USER "OPS$ORACLE-BASE.COM\TIM_HALL" IDENTIFIED EXTERNALLY;" All in all I think this is a problem your dbadmin should try to solve. Are there other OsAuthenticated user in this database that already can login? "sqlplus /@database" is ok if 'database' points to the right database.
• Balazs Papp: Is this really OS authentication? To use that kind of login, usually Kerberos authentication is configured. To have sqlplus /@database working with Kerberos authentication, you need to set some extra parameters in the client sqlnet.ora (enable Kerberos authentication, enable the use of Windows credential cache), which the DBA should be able to provide. First clarify that.
• datadawg2000: I tried adding Integrated Security=yes to my connection string but it isn't a valid parameter for my type of connection, thank you though. Also, I don't have a lot of insight to the DBA side of the configuration. The DBA says it is good to go so I figured the problem was on my side with how I'm connecting. And good point on Kerberos, I don't know specifically other than that it is set up for "external users", I will ask about Kerberos specifically.

Answer 1

For anything looking for an answer: I was connecting the correct way. On the backend, remote_os_authent was set to false, which would not allow this kind of authentication.

• Balazs Papp: remote_os_authent=true. So I guess cybersecurity and audits are of no concern at all at your organization.
• pmdba: I agree: setting remote_os_authent=true is one of the worst possible security holes your system could have... i hope this is just for training purposes and not for anything real.