Oracle OS Authentication only works on certain databases/clients

oracleoracle-10g

We currently face a problem where Oracle OS authentication only works on certain client/server combinations.

The environment consists of a development client with its own database server (excluded for this example), an "integration" client with its own database server (for integration tests) and a "reference" client with its own database server (we keep these identical to our customer client/server).

All servers are Windows 2003 running an Oracle 10.2.0.3 database, all clients are Windows XP running an Oracle 10.2.0.1 client. The development client is physical, everything else is virtualized on the same virtual host. All of the machines use the same network domain.

The same user (from an Active Directory) is used on each machine for tests, and both of the databases have an externally identified user created like

create user "OPS$DOMAIN\USERNAME" identified externally;
grant connect to "OPS$DOMAIN\USERNAME";

Now when trying to connect to a database using

connect /@TNS_NAME

our reference database (which still worked four months ago) declines connections with

ORA-01017: Invalid username/password. Logon denied.

These connections work on the integration database when connecting from the development and integration client (or the integration server itself), but not from the reference client.
Note that all of the clients can connect to both of the servers using e.g. schema owner + password.

The following picture displays the current state of working and failing OS authenticated connections:
Overview of the working and failing connections

Further information:

  • os_authent_prefix is set to "OPS$".
  • os_remote_authent is set to FALSE on both servers, but as connections do work for one server, this should not be the issue.
  • externally identified users are written in all caps.
  • SQLNET.AUTHENTICATION_SERVICES is set to (NTS) on both servers

What could be the problem here? I would suspect a configuration issue for the reference database, but then why can't the reference client connect to the integration database using OS authentication? (Note that regular connections work for that case).
Are there maybe two issues?

PS: I know OS Authentication is kind of insecure, but this is not an issue here.

Best Answer

Fixed the problem:

The source of the problem was that the broken clients and server were referencing a central TNSNames.ora file on a different file server. This was intended; however, I did not know that this also makes those machines search the sqlnet.ora there.
Someone - or some installer or something - placed an empty sqlnet.ora file in that central location. I haven't tried if the clients only ignore their local files if there is a central one, but in this case any changes to the local sqlnet.ora files had no effect.

Adding the line

SQLNET.AUTHENTICATION_SERVICES = (NTS)

to that central sqlnet.ora file obviously fixed it.

Related Question