Connecting to a Oracle 11g database using Windows authentication

I am new to Oracle 11g and I have been trying to connect to my test Oracle 11g database using Windows authentication.

I have been successful in connecting to my test Oracle database using Windows authentication locally on the server.  But when I try to connect to the database remotely from a client I get an error.

Here is the syntax that I am using.

To connect locally I open a command prompt on the server and I type the following.

SET ORACLE_SID=ORCL
SQLPLUS /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 18 10:11:36 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

But, when I try to connect to the database remotely from my workstation I get the following error.

ERROR
ORA-12560: TNS:Protocol adapter error

Enter user-name:

I am not sure if it has to do with the syntax that I am using or if it has to do with the tnsnames.ora file.  I would appreciate some Expert Exchange knowledge transfer with this problem.

Oh, I almost forgot to mention that I am able to connect to my test Oracle box remotely using an Oracle account (via SQL*Plus) such as SYS.

RayManAaaAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Glad this is finally working.

I have no problem accepting your last post, http:#a34982592 as the solution.  The points are nice but I'm here mainly to help.

If you want to toss around a few points on the posts you think helped the most, I'm sure they will be appreciated but I would still accept your post as the solution since you provided detailed steps.
0
 
slightwv (䄆 Netminder) Commented:
You need to specify the add alias from the tnsnames.ora file:

sqlplus /@someDatabase as sysdba
0
 
RayManAaaAuthor Commented:
When I type that in I get the following...

C:\>sqlplus /@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 18 11:02:06 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

I know that my windows domain account has local admin on the box and sysdba assigned.  

I have obviously missed something, please advise.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
slightwv (䄆 Netminder) Commented:
>>ORA-01031: insufficient privileges

Check your sqlnet.ora file.  

If not already set to this try:
SQLNET.AUTHENTICATION_SERVICES=(NTS)

0
 
slightwv (䄆 Netminder) Commented:
Just for grins check that REMOTE_LOGIN_PASSWORDFILE is set properly in the database.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#i1006853
0
 
RayManAaaAuthor Commented:
I already had this entered in my sqlnet.ora file, do you have another suggestion?
0
 
slightwv (䄆 Netminder) Commented:
I might have been typing the REMOTE_LOGIN_PASSWORDFILE post when you were posting.  Have you checked it?
0
 
RayManAaaAuthor Commented:
I just finished reading the document you sent me.  I am a little confused about creating a password file.  I thought when you are using windows authentication that it was not necessary to create a password file.  Am I missing the point here? Is this step necessary when you want to setup users to connect to a Oracle 11g database using only windows domain authentication?
0
 
slightwv (䄆 Netminder) Commented:
The password file is for 'privileged' database connections.  As soon as Oracle sees 'as sysdba' or one of the other privs, it basically ignores the username and password provided and tries to connect you with that privilege.

To see this in action and assuming you don't have a fred user in the database, log into the database server and try this:

sqlplus fred/flintstone as sysdba

then at the SQL prompt:  show user
0
 
RayManAaaAuthor Commented:
I entered the information you suggested.

sqlplus fred/flintstone as sysdba

then at the SQL prompt:  show user.

And I did get SYS as the user.

I also followed the information in the documentation you sent me.  I first ran the orapwd FILE=acct.pwd PASSWORD=secret ENTRIES=30 command.  I wanted to enter a file path but I was not sure if I needed to do this.

I then entered the following REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in my init.ora file.  Once this was done I restarted the Windows service OracleOraDb11g_home1TNSListener.

I first check to see if I was able to connect to the oracle instance from the test server and I got the following results.

C:\>SET ORACLE_SID=ORCL

C:\>sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 18 13:07:01 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> show user
USER is "OPS$Domain\My_Wiindows_Account"
SQL>


I then tried to connect from my workstation to my oracle test server and I get the following.

C:\>SET ORACLE_SID=ORCL

C:\>SQLPLUS /@ORCL as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 18 13:44:30 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:


0
 
slightwv (䄆 Netminder) Commented:
>>I wanted to enter a file path but I was not sure if I needed to do this.

Sorry, I should have provided the link specific to Windows.

From the link above, scroll up a little:

FILE
This parameter sets the name of the password file being created. You must specify the full path name for the file. The contents of this file are encrypted, and the file cannot be read directly. This parameter is mandatory.

The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. For name and location information for the Unix and Linux operating systems, see Administrator's Reference for UNIX-Based Operating Systems. For Windows, see Platform Guide for Microsoft Windows. For other operating systems, see your operating system documentation.

The link for orapwd in Windows is:
http://download.oracle.com/docs/cd/E11882_01/win.112/e10845/admin.htm#i1006252


>>in my init.ora file.

I hope you are using the SPFile and not an init.ora.

connect locally 'as sysdba' then do:  show parameter REMOTE_LOGIN_PASSWORDFILE

Exclusive is the 'default'.  I just want to make sure it hasn't changed.


>>C:\>SQLPLUS /@ORCL as sysdba

I have to ask:  Your windows client has ORCL configured in the tnsnames.ora file and it pointing to the test server.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
the TNS configuration has been done in your workstation for more info
http://www.oracle-base.com/articles/misc/OracleNetworkConfiguration.php

SET ORACLE_SID=ORCL
SQLPLUS  "/ as sysdba"

0
 
slightwv (䄆 Netminder) Commented:
>>SQLPLUS  "/ as sysdba"

Just setting ORACLE_SID does not tell a remote connection what alias to use from the tnsnames.ora file.

You can set LOCAL on Windows or TWO_TASK on Unix for a default remote connection.
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

on the server that hosts the instance add your NT user to the ORADBA builtin group
that should do it.
0
 
slightwv (䄆 Netminder) Commented:
The correct group for Windows is ORA_DBA (dba for unix).

Not what I've reread this question I think I got way off topic.

I originally read you wanted to connect 'as sysdba'.  Now that I reread this question, I have to ask if this is what you are after or are you just wanting to connect with OS authentication as a normal everyday user?

If a normal everyday user just remove the 'as sysdba' but you still need to add the alias from the tnsnames.ora file:  sqlplus /@ORCL
0
 
RayManAaaAuthor Commented:
Sorry, I did not get back to everyone, I wanted to continue doing my research since I only been using Oracle 11g for about a week now.

Let me first say that I am a Microsoft SQL database administrator and I have been working with MS SQL since V6.5.  If you are familiar with MS SQL you will know what I mean when I say that I want to give a windows domain account access to a SQL database vs. a SQL account.

What I have been trying to do is to get a windows domain account (domain\username) to connect to a Oracle 11g database not a Oracle account.  When I use an Oracle account which I created via PL SQL or just using the Oracle Enterprise Manager I am able to connect to the Oracle database via my workstation.

I don't think that this is a tnsnames.ora file issue since I am able to connect from my workstation using an Oracle account.  I also added my domain account to the ORA_DBA group on the local server group.  I know that this little bit of info should not make a difference but I thought I should mention this anyway that I am the local admin on the server.  

All my attempts to connect to my Oracle 11g database via my windows domain account have failed.  

Since this is only a virtual test box I plan on scrapping the whole project and rebuild the VM test server again.  I have been making so many changes that I feel that I may have cause more damage then good.  In any case I can chalk this up as a learning exercise.
0
 
RayManAaaAuthor Commented:
I am not sure which step I did resolved the windows domain authentication to Oracle database issue.  I did several things which could have contributed to the resolution to the original problem.    First of all I rebuilt the Oracle server, meaning I reinstalled Oracle 11g on the VM server.  

After I did this I made some changes to the init.ora and sqlnet.ora files.

For the init.ora file I added the following lines.

remote_login_passwordfile = none
remote_os_authent=true
OS_ROLES=TRUE

For the sqlnet.ora file I made sure that the following was in the file.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

For my workstation I added an additional component that I did not have before.   I added the Oracle 11G client to my workstation win32_11gR1_client.zip.  Once I did all of these steps I was able to connect to my Oracle 11G database via my workstation using windows domain authentication.

I am not 100% sure which step resolved the problem, what do you think?

I will award the 500 points to the best answer, please keep in mind I now consider myself somewhat of an expert on this topic. ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.