Re: W2000 connect / as sysdba problem

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 01 May 2004 00:32:12 +1000
Message-ID: <40926365$0$25012$afc38c87@news.optusnet.com.au>

Kenneth Koenraadt wrote:

[snip]

>>>>>If you logon to
>>>>>the server  *remotely* with e.g. a Domain user account, which is also
>>>>>a member of the local ORA_DBA group  you *won't* be able to "connect /
>>>>>as sysdba". I guess that's why it is called
>>>>>"remote_login_passwordfile" and not "local_login_passwordfile"
>>>>
>>>>Well, since it's a remote connection, you won't be able to connect / as 
>>>>sysdba *at all* because there needs to be a tnsnames alias in there 
>>>>somewhere (somewhere I can never get right in any case: sqlplus "/@win92 
>>>>as sysdba" isn't doing it for me!).
>>>
>>>
>>>Yes you will.
>>>You logon to the server with a domain user being a member of the local
>>>ORA_DBA group. With R_L_P=NONE, and sqlnet.ora properly set, I can
>>>connect / as sysdba easily. Have done it hundreds of times.
>>
>>Define "properly setting" sqlnet.ora

>
>
> SQLNET.AUTHENTICATION_SERVICES= (NTS)
Which doesn't obviate the necessity for specifying which remote database you want to connect to, does it? Setting ORACLE_SID on the client doesn't do it, because then you simply get protocol adapter errors.

Viz:

H:\>set ORACLE_SID=win92

H:\>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Sat May 1 00:05:09 2004

ERROR:
ORA-12560: TNS:protocol adapter error

But:

H:\>sqlplus /@win92 as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Sat May 1 00:09:27 2004

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

If you are doing a remote connection, truly remote, you will need a tnsnames alias in the connect string.

(Incidentally, this happens to be a 10g client, and that rather nicely obviates the necessity of putting quotes around the thing. But it works identically with a 9iR2 client, too).

>
> Guess it should, but I want to know the whole truth.
> I am not able to demonstate, since the only W2k-system I work on now
> is my laptop.
>
> Maybe someone out there can reveal it :
>
> You have an oracle 8i+ on a W2K server called DBS. It participates in
> a domain DOM with a Domain admin user USR also being member of the
> local ORA_DBA group.
>
> We assume
> SQLNET.AUTHENTICATION_SERVICES= (NTS)
> present in sqlnet.ora.
>
> You now login to the domain DOM with user USR from DBS.
>
> And I claim : In order to "connect / as sysdba" to a DB on DBS, that
> DB *cannot* have R_L_P = EXCLUSIVE or SHARED.
>
> False or true ?? I believe true, it is what I experienced through 1½
> year and hundreds of logins.

False, I'm afraid.

My test setup is not *exactly* as you describe, and I'm not about to re-configure it to make the point. But I have a W2K (Advanced) server hosting domain dizwell.local. I have a domain-member laptop running XP. The 8i database is on a participating server called HAYDN, the 9i database is on a participating server called MOZART and the 10g database is on a participating server called BRITTEN. And I can log on to all of 'em, as a privileged user, with RLP set to EXCLUSIVE, having first logged on to my laptop as me (a Domain Admin, natch, and where Domain Admins as a group has been added to each server's ORA_DBA group). Precisely the case I posted earlier.

If I knew Linux better than I do, I'd fiddle my /etc/group to see whether I can do it on a Linux box, too. With the Linux box "visible" on the domain, but not actually sort-of integrated into it, I'm not sure how to go about adding me, the Windows domain user, in as a member of the oinstall or dba group. That's my Linux ignorance for you.

Oh, just for the hell of it then, here's a 9i XP client connecting to the 8i database:

H:\>sqlplus "/@win81 as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Sat May 1 00:29:39 2004

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> show parameter remote_login

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
remote_login_passwordfile            string      EXCLUSIVE

Although the quotes are now obligatory, the answer remains 'false'.

HJR Received on Fri Apr 30 2004 - 09:32:12 CDT