In order to connect to Oracle running on Windows using an operating system authenticated account, there are surprisingly few steps to take:
1. Ensure the sqlnet.ora file has a required entry
2. Verify (or set) a database/instance parameter
3. Create a database user
When connecting to the database using operating system authentication, you do not need to specify the "normal" user id -- instead you simply use a "/" character (without the quotes) and no password (the whole point!). Oracle allows for two classes of operating system authenticated users: enterprise users and external users. I am illustrating how to create and connect as an external user (i.e. everything is on the same machine). Creating and using an enterprise user requires a directory server and additional coordination between the database administrator and the operating system/directory server administrator.
The sqlnet.ora file Entry
When Oracle is initially installed and configured on the Windows platform the sqlnet.ora file is created in the %ORACLE_HOME%\network\admin folder. There may be multiple entries in this file; however, in order to connect using o/s authentication the file must have an entry similar to:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
If this entry is not present in the sqlnet.ora file you are using then a connection attempt using o/s authentication will fail.
The Database/Instance Parameter
The os_authent_prefix parameter controls whether or not Oracle adds a prefix to the user id being presented during the o/s authentication process. By default the value of this parameter is "OPS$" though it is not uncommon for it to be changed or simply be set to an empty string (""). However, in order to create the user in the database, the value of this parameter must be known. Fortunately, determining the value is as simple as connecting to the database as a capable of successfully executing the SQL*Plus "show parameter" command:
SQL> show parameter os_authent_prefix
NAME TYPE VALUE
----------------- ----------- -----
os_authent_prefix string OPS$
As you can see, on my system this value has not been changed from the default value. This means that Oracle will prefix the "OPS$" value to the beginning of my Windows user name when I attempt to connect using o/s authentication. The Windows user name presented will be of the form DOMAIN or HOSTNAME\USERNAME. On my laptop (which is not part of a domain) my Windows user name will be presented to Oracle as "LIVERPOOL\MARKWILL".
Creating the Database User
Knowing that the value of the os_authent_prefix parameter is "OPS$" and that my Windows user name is "LIVERPOOL\MARKWILL" a database user named "OPS$LIVERPOOL\MARKWILL" will need to be created in the database:
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Nov 10 14:07:33 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> create user "OPS$LIVERPOOL\MARKWILL" identified externally
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
User created.
SQL> grant connect, alter session to "OPS$LIVERPOOL\MARKWILL";
Grant succeeded.
That's it!
Now I am ready to test my freshly created user that will connect to the database via my Windows-authenticated account (notice the connect string is just the "/" -- I am connecting to the database specified by the ORACLE_SID value on my system):
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Nov 10 14:11:14 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect /
Connected.
SQL> COL USERNAME FORMAT A32
SQL> COL DATABASE FORMAT A24
SQL> SELECT A.USERNAME,
2 B.GLOBAL_NAME DATABASE
3 FROM USER_USERS A,
4 GLOBAL_NAME B;
USERNAME DATABASE
-------------------------------- ------------------------
OPS$LIVERPOOL\MARKWILL LT11GR1.SAND
1 row selected.
If you wish to connect to a database by explicitly specifying a tns alias the connect string would look like:
connect /@tnsalias
For example:
connect /@lt11gr1
Specifying an Operating System Authenticated Account in ODP.NET
Just as the connect string in SQL*Plus consists of a "/" and possibly a tns alias, the connect string in an ODP.NET application can be as simple as:
"User Id=/"
or
"User Id=/; Data Source=mydatasource"
NOTE: There is a behaviour change from ODP.NET versions prior to 11g and the 11g release. In previous versions connection pooling was not used for operating system authenticated connections regardless of the setting of the "pooling" parameter in the connection string. Beginning with the 11g release, connection pooling is now supported using operating system authenticated connections.
Happy connecting!
