1

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?

Improve this question
4
  • 1
    In your connection string, make sure you're appending Integrated Security=yes as part of your connection string
    – AMtwo
    Jul 28, 2020 at 6:32
  • 1
    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.
    – miracle173
    Jul 28, 2020 at 7:13
  • 1
    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.
    – Balazs Papp
    Jul 28, 2020 at 8:37
  • 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.
    – datadawg2000
    Jul 28, 2020 at 17:24

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 1
    remote_os_authent=true. So I guess cybersecurity and audits are of no concern at all at your organization.
    – Balazs Papp
    Jul 28, 2020 at 20:26
  • 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.
    – pmdba
    Jul 28, 2020 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.