ODAC Authentication

[pnookala]

Hi All,

We have been using ODAC components for a while now and we need to do similar stuff to Horace ie., authenticate whether the user is a part of valid group on ADSI and then pass on a connection string to connect to the Oracle database.

Any ideas on how to do this, the connection string part and the windows authentication to oracle part ?

Many thanks,
pnookala.

[Horace]

Maybe we could reverse engineer the method that the OCI is using for authentication and help the Devart Team add it to the NET mode so the Oracle Bloatware does not have to be installed in order ro accomplish this essential functionality.

I understand that thin app is the main feature of the NET mode....

[alex.santanna]

Horace, you can use your WIN API to check if the user is authenticated in the AD Domain and then get the user login, now in the oracle part you can set a password generated using the user name for example.

Like that:

AD Domain Login : horace

Oracle Login: horace
oracle password: 103111114097099101 (Ascii Codes of your name)

You can use any other way to generate the password.

Hope this helps

[Horace]

Cute, but insecure ;)
Although, thanks for trying to help Alex.

Also, in the method Alex proposes, the authentication credentials are kept in two separate places and they need to be synchronized :/

What needs to be done is this:
"When the Oracle wants to authenticate a user, it needs to ask the ActiveDirectory to vouch for it".

The trust between Oracle Server and the ActiveDirectory can be established separately, but one time only.

I don't know if there is mechanism in Oracle that can ask the ActiveDirectory for this, but if the OCI autherntication is done well it must use this mechanism and there is no need to reinvent the wheel. We just need to learn to use that wheel....

Also it would be nice if Oracle was asking the SAM/NTLM on a stand-alone Windows workstation to vouch for a user.

[Horace]

The URL below seems to mention something on that subject, but I still don't have enough details how to make it work with ODAC.

http://dbaforums.org/oracle/index.php?showtopic=16673
http://www.oracle.com/technetwork/testc ... 132600.pdf

I don't think I would be able to convince many network administrators to authenticate users through my Oracle Server (Oracle Authentication Services for Operating Systems).

It needs to be the other way around! - the Oracle Server needs to authenticate a user through the Network Servers running some Operating System with Active Directory / LDAP / Kerberos (or even a SAM)

Horace

[Horace]

I Googled this:

Windows authentication by Oracle
Allegedly, Oracle already supports the Windows-based authentication via NTLM/Kerberos, out of the box. Oracle does this by using the WinAPI: AcceptSecurityContext()

• Create an Oracle user:
CREATE USER "DOMAINNAME\USERNAME" IDENTIFIED EXTERNALLY
• Make sure the user can make a session:
GRANT CREATE SESSION TO "DOMAINNAME\USERNAME"
• A ComputerName of the Windows workstation can be substituted for the DOMAINNAME
• The application users do not need to provide their password, if they are already logged in to their Windows workstation / Domain.

For example, the application users can use the following command to log in to the Oracle server without a password.
SQLPLUS /@connectionstring

Also these configuration options should be set correctly for this to work.

• • The DOMAINNAME and USERNAME must be all in uppercase on Oracle, even if they are in lowercase on the Windows Workstation / Domain.
  • SQLNET.AUTHENTICATION_SERVICES = (NTS)
  • os_authent_prefix = ""
  • remote_os_authent = TRUE
  • remote_login_passwordfile = NONE
  • os_roles = FALSE
  • remote_os_roles = FALSE
  • Windows service "NT LM Security Support Provider" is started
  • The ORACLE_HOME of the database should be the first one in the order. ( set using the Universal Installer / Installed Products / Environment Variables )
  • The following Registry entry must exist on the Oracle server:
  [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\]
  "OSAUTH_PREFIX_DOMAIN"="TRUE"

Supposedly these options above are set correctly by default but it never hurts to research and check them.

Could somebody please verify this with ODAC in NET mode, as I am currently on vacation and don't have remote access to my servers.

[verpies]

So ODAC in Direct (NET) Mode has supported authentication via Windows Domain/Workstation all along ?

Why isn't this mentioned in ODAC documentation ?

[AlexP]

Hello,

In the direct mode the OS authentication feature is not available, it is available only in the OCI mode.
See the Connecting in Direct Mode topic in the ODAC help for more information.