Solved

Connecting to a Oracle 11g database using Windows authentication

Posted on 2011-02-18
17
1,272 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:RayManAaa
17 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34926827
You need to specify the add alias from the tnsnames.ora file:

sqlplus /@someDatabase as sysdba
0
 

Author Comment

by:RayManAaa
ID: 34926971
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927026
>>ORA-01031: insufficient privileges

Check your sqlnet.ora file.  

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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927075
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
 

Author Comment

by:RayManAaa
ID: 34927099
I already had this entered in my sqlnet.ora file, do you have another suggestion?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927144
I might have been typing the REMOTE_LOGIN_PASSWORDFILE post when you were posting.  Have you checked it?
0
 

Author Comment

by:RayManAaa
ID: 34927185
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34927219
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:RayManAaa
ID: 34928569
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34928665
>>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
 
LVL 12

Expert Comment

by:praveencpk
ID: 34928680
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34928711
>>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
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34931848
hi

on the server that hosts the instance add your NT user to the ORADBA builtin group
that should do it.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34934465
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
 

Author Comment

by:RayManAaa
ID: 34942836
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
 

Author Comment

by:RayManAaa
ID: 34982592
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34983030
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now