The Oracle Obstacles: The password file and the sqlnet.ora file
If you are familiar with
pre-9i versions of Oracle, you know that the PWD<SID>.ora file was
necessary to be able to connect internal with a password. The password was
specified as a parameter in the oradim command. Nothing has changed with the
oradim command; in fact, it is still a required step prior to creating a
database. The password file is in ORACLE_HOME\database, and if you inherited a
Windows database and do not know what the "internal" password is, you
have two options.
First, you can forget about
it and use the Services control panel to start and stop your databases
(instances) via an administrator-level group membership. Or, you can fix this
problem once and for all by simply deleting the file and re-creating a new one.
You can use the oradim or orapwd commands. If using oradim, delete the service
and recreate it along with a new password file. Deleting the service will not
harm a database (but shut it down first anyway). The specifics for these
commands can be found in Windows-related Oracle documentation.
Lurking within the
sqlnet.ora file is a parameter named SQLNET.AUTHENTICATION_SERVICES, and a
value such as (NTS). What, exactly, does this parameter do?
Purpose
Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more
authentication services. If authentication has been installed, it is
recommended that this parameter be set to either none or to one of the authentication methods.
Default
None
Values
Authentication
Methods Available with Oracle Net Services:
-
none for no
authentication methods. A valid username and password can be used to access the
database.
-
all for all
authentication methods
-
nts for Windows
NT native authentication
Note the last line (taken
from the Net Services Reference Guide) where it says "nts for Windows NT
native authentication." What's that? It is an "authentication method
that enables a client single login access to a Windows NT server and a database
running on the server." What does that mean, aside from Oracle still using
"NT" to refer to Windows?
With your user account added
to the ORA_DBA group, and the sqlnet.ora parameter being set to NTS, you have
sufficient privileges to connect as sysdba. Let's prove or demonstrate this
statement.
Situation1) User
is not in the ORA_DBA group, and the parameter is none
Attempt to connect as sysdba
fails as shown below.
Situation 2) User
is a member of the ORA_DBA group, and the parameter is still set to none
Attempt to connect as sysdba
fails as shown below.
Situation 3) User
is out of the ORA_DBA group, and the parameter is set to NTS
Again, the attempt to
connect as sysdba fails.
Situation 4) User belongs to ORA_DBA and
the parameter is set to NTS
Success!
Did the password file ever
come into play?
As you can see, with OS and
database settings set appropriately, you do not even need the password file (or
knowledge of what the password is). In fact, as the screenshot above shows, you
can dummy up the username and password they really do not matter with you
being in the ORA_DBA group and the sqlnet.ora parameter being set to NTS.
What if a non-database
user/non-ORA_DBA group member needs to connect as sysdba? That is where the
password file comes into play.
In Closing
This article has shown you
two ways to overcome the ORA-01031 error. Be honest now, how many times have
you encountered this error? Without a deeper understanding of the "how do
I authenticate thee, let me count the ways?" relationship between the
operating system and Oracle, you are doomed to encounter this error time and
time again. Now that you know the secret codes for Very Happy Ammo and God mode
with respect to connecting with Oracle, you can quickly fix the configuration
errors or problems and move on to solving some of the other great mysteries of
Oracle.
»
See All Articles by Columnist Steve Callan
» 
Comment and Contribute
