11

With Oracle SQL Developer I can put / -character to Username and leave password empty and I get connected. I have OP$MYWINDOWSUSERNAME user created in database.

EDIT: SQL Developer does not work if I check OS Authentication-checkbox (empties and disables username + pwd). Moreover Preferences->Database->Advanced->Use Oracle Client is unchecked so I guess what SQL Developer does or doesn't has very little to do with my System.Data.OracleClient.OracleConnection problem.

However when I try to form connection string like this: 

string.Format("Data Source={0}; user id=/;password=;Integrated Security=yes", dbName);

I get ORA-01017: invalid username/password: logon denied

with 

string.Format("Data Source={0}; user id=/;password=;Integrated Security=no", dbName);

I get ORA-01005.

With 

string.Format("Data Source={0};Integrated Security=yes", dbName);

I get ORA-01017: invalid username/password: logon denied

With 

string.Format("Data Source={0}; user id=/;", dbName);

I get ORA-01005

With

string.Format("Data Source={0};User Id=/;Integrated Security=yes;", dbName);

I get ORA-01017

Both OracleConnection in my program and Oracle SQL Developer work when I specify Username and password.

EDIT: This works with 

string.Format("Data Source={0};Integrated Security=yes", dbName);

when sqlnet.ora line

SQLNET.AUTHENTICATION_SERVICES= (NTS)

is changed to 

SQLNET.AUTHENTICATION_SERVICES= (NONE)

If somebody writes short answer what is happening and why, i'm happy to grant bounty to him/her.

char m
  • 7,840
  • 14
  • 68
  • 117
  • Just remove the `user id` & `password` tokens – Alex K. Mar 16 '17 at 13:15
  • this did not work. this was my 1st iteration. – char m Mar 16 '17 at 13:17
  • Ah apparently its `Data Source=myOracleDB;User Id=/;` - https://www.connectionstrings.com/net-framework-data-provider-for-oracle/windows-authentication/ – Alex K. Mar 16 '17 at 13:18
  • i tried that from the same source also – char m Mar 16 '17 at 13:20
  • Have you tried Oracle's own ODP.Net? System.Data.OracleClient is completely deprecated for 8 years now: https://msdn.microsoft.com/en-us/library/system.data.oracleclient.aspx – Simon Mourier Mar 20 '17 at 21:19
  • @SimonMourier: i know it's deprecated and i am aware of ODP.NET. i just need to solve this. – char m Apr 07 '17 at 12:47
  • 1
    If you want to use the OS username, then the proper connection string for System.Data.OracleClient is `Data Source=INSTANCE;Integrated Security=yes` w/o user and w/o password (and possibly add Unicode=true). Why this doesn't work is specific to your configuration and difficult to reproduce w/o more information. For example: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/171079fd-d836-4fb8-a7ba-fed9916fbb62/cannot-connect-to-oracle-using-integrated-security?forum=sqlreportingservices – Simon Mourier Apr 20 '17 at 09:11
  • For last edit, is your DB remote or it's local on the machine you trying to connect, if it's remote what authentication protocol used on server and what is server side OS ? – Seyran Apr 26 '17 at 15:17
  • db is remote. server is windows server 2012 R2, client machine win7. unfortunately I can't get authentication protocol information at the moment, cause I don't know how to (this is way out of my comfort zone). – char m Apr 27 '17 at 06:50
  • I would like to grant bounty to best answer. I still have 23 hours, but my knowledge is so limited that I really can't tell which one is best. The clearest would be "missing workgroup/domain from OP$USER"-answer, but not sure if it's correct. I have no way to test it in this time. Any comments on that would be appreciated. – char m Apr 27 '17 at 06:58

4 Answers4

2

SQL Developer has sometimes ununderstandable behavior. It's not reliable for this "OS authentication" feature.

You should remove id completely from the authentication string:

string.Format("Data Source={0}; Integrated Security=yes;", dbName);

Edit

The fact that it works only when your remove the NTS option could mean the expected service isn't running, or that NTS doesn't support OS authentication for all users, but only for SYS. Found here some explanation:

The NTS service is used in windows environments to make the Sys user authentication based on the o/s level authentication. So if you are not willing to supply the password of the Sys user each time, you should set this .

And here someone who uses something you could try in its sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES =(NONE, NTS)

It may also depend of your Oracle version; here it says NTS is not supported with Oracle 12c

Starting with Oracle Database 12c Release 1 (12.1), the NTS authentication adapter no longer supports the use of NTLM to authenticate Windows domain users. Thus the NTS cannot be used to authenticate users in old Windows NT domains or domains with old Windows NT domain controllers. However, local connections and Oracle Database services running as a Windows Local User continues to be authenticated using NTLM.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
2

Your problem not in connection string, but in your Active Directory structure, and Oracle settings for ability to use LDAP DB of AD, whats why if you try your code on local DB, for example OracleXE, without using active directory you probably will be succeed, but at the same time on the distributed Oracle server you can get authorization error, so find a strong administrator which will setup your AD and Oracle proper.

Seyran
  • 711
  • 4
  • 8
2

In short, it's the Windows native operating system authentication which is indicated by NTS.

Once NTS is specified, oracle client identifies the username as workgroup\username or domain\username which does not match your OP$MYWINDOWSUSERNAME database user

In order to have it working with NTS option, you need to include domain/workgroup name in your db username:

CREATE USER "OPS$<DOMAIN_NAME>\<OS_USERNAME>" IDENTIFIED EXTERNALLY;

oracle support document 750436.1 confirms this with detailed steps.

lsalamon
  • 788
  • 7
  • 12
1

your problem is that you cannot use / as user id from outside the database machine itself. also in some cases / cannot be used to connect to the database unless it is specified "/ as sysdba". therefore in case you want to connect through a client library like ADO.NET you have to specify a username and password or it will fail to connect.

if you want to have authentication to access the database using an LDAP user you can also use OAM and ORACLE SSO and OUD/EUM

Wissam HANNA
  • 63
  • 14
  • but i said "With Oracle SQL Developer I can put / -character to Username and leave password empty". I mean from same machine that tries ADO.NET connection. so / works from another machine. – char m Apr 07 '17 at 12:52