Hi there!
Did anyone already tested the oracle db logon with windows authentification in Clarion?
I tried “/@service” (as owner-string) like in sqlplus but it doesn’t seem to work.
Thank you!
Michele
I’m still fighting with that…
no one has any idea?
I dont use Oracle, but MS SQL had/has windows authentication and SQL server authentication.
Windows authentication is using the windows login accounts to access db’s and tables and the sql server authentication is setting up an account on the sql server to access db’s and tables.
Basically the credentials, ie username and password are stored in different locations, but the credentials still need to be mapped to database(s), tables and the appropriate read write access for tables and records, stored procedures and views.
So this link explains how your clarion app will be using a trusted connection.
SQL connect string - questions - ClarionHub
and this looks like the Oracle way of doing it.
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;
OSAuthent=1 appears to be the equivalent of Trusted_Connection=Yes from the MS SQL context, so once your windows login accounts are mapped to the oracle db, tables and permissions they need, the clarion app will just need a connection string that uses the windows logins that are mapped.
I dont know if this is more secure or not considering how easy it is to get software which can pull out the windows usernames and passwords from a machine. II found one which was a windows app and MS defender treats it as a virus or malware, but it did it, so the windows login account to access a db is convenient, but it puts another egg in one basket for security. Put another way, having another username and password which has to be typed into the app in order to access the db is another hurdle for users and hackers.
Both methods still need to be monitored for failed login attempts though. As the db authentication route would also typically involve a network server, network traffic analysis should make it easier to spot malware coming from a workstation attempting to access the db once they are on a workstation which would probably mean also have access to someone’s accounts.
Lots of possibilities, but I always think if its easy for me, its easy for a hacker.
fwiw
Hello Richard.
Thanks for your response!
But your connectionstring is for ODBC, isn’t it?
I work with Oracle-Driver, there is the owner-property for user, pass and service.
I understand your considerations about that but it’s a customer requirement.
I tried “OSAuthent=1” but it doesn’t work in owner or driver options property.
Thanks!
So it looks like you have been using this method?
ORACLE-BASE - OS Authentication
Have you seen forum threads like this?
Windows Authentication for Oracle — oracle-tech
I suppose for now I’d probably be looking for a command line interface to the db or some other app to connect to the oracle db from the workstation to eliminate any firewall problems or maybe be going through any oracle logging to see where the connection is failing.
Maybe this is of use?
Connect to the Oracle Database Using Windows Authentication - BizTalk Server | Microsoft Learn
TLDR I’d find a non clarion way to connect to the oracle db from the workstation, once that can be done, then trying the clarion app, or maybe even switch on the driver trace in clarion and see what appears there.
I had already read all this articles.
With sqlplus.exe it works like it should. There I can connect like that “/@db” and I’m log in as Windows User.
The tracing says just “Connecting @db Return Code: -1017 Time Taken:0.08 secs” (ORA-01017 invalid username/password logon denied). I think the problem is that the Clarion-Runtimes remove the slash and connect without username and password but username should be “/” (even doubling doesn’t work)
Because of the filter as explained here and its hard to know what results you see and I see: Eli Pariser: Beware online “filter bubbles” | TED Talk
I’ll post this link to stack overflow, ie is it a case sensitivity thing?
oracle - ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client - Stack Overflow
a login with username and password works fine.
I tried it with VB.net and it works like it should, so I would say the system config should be ok.
I guess Clarion can’t do the windows auth with oracle.
Have you tried appending
;Integrated Security=yes
to your connection/owner string. Don’t specify a user name and password.
@JonW Have you seen this thread? Any suggestions?
It’s been so many years (15 years) since I worked with Oracle on a regular bases, I’ve forgotten most everything.
Yes I did in driver options and owner string with comma and semikolon, with yes, true, 1…nothing worked.
And OSAuthent too, same behaviour.
The main problem seems to be that the runtime removes the slash, if the trace shows it right.
Did you try doubling up the slash?
Yes, first thought.
I quoted it too and I tried with \x002F and \u002F and escape it with backslash and so on…nothing worked.
Everything before “/” is interpreted as username and everthing after as password.
Maybe Jon with chime in. He definitely knows his Oracle.
I would listen to the melody
I can’t be a lot of help, because I’ve never tried to use Windows authentication, and my work database is not set up for it, so I’d have to try it on my home machine.
The only thing I can say is that the / business is a red herring. If you log on totally normally, say with a connecting string:
myuser/mypassword@mydb
the trace file will report:
Connecting myuser@mydb Return Code: 0 Time Taken:1.11 secs
That’s because in writing the log file, the password (including the slash) is removed so that it is not sitting there in plain text for snooping eyes to see. So when all you have for user/password is a slash, that goes.
Hello Jon.
Thanks for your response.
I tried it with “/@mydb” but it’s not working.
I hope I can get help from Softvelocity.
I had a few minutes to play today using my laptop. Couldn’t get logged on just using SQLPUS until I read this and it clicked “When you install Oracle Database, a special Windows local group called ORA_DBA is created …, and your Windows user name is automatically added to it. Members of local group ORA_DBA automatically receive the SYSDBA privilege” here https://docs.oracle.com/cd/E11882_01/win.112/e10845/authen.htm#NTQRF327
I was getting the 1017 error message but it wasn’t the authentication that was the problem, it was that since I was part of the dba group I had to log on to sqlplus / as sysdba.
I haven’t got around to setting up another user on the machine to see if I can log on as a normal user from there. Just in case you are in a similar situation I thought I should mention it.
Hi Jon,
Perhaps using CREATE USER … IDENTIFIED EXTERNALLY clause?
Yes I did set up a user identified externally, like
create user "c##OPS$<windows user> identified externally;
but because I was on the account that owned the database I had to do the as sysdba logon.
I did set up another account that was not part of the ORA_DBA group, but I didn’t manage to log onto it. The first part is working fine: I can look at failed login attempts and see that my Testing user (the OS_user) is attempting to login as the matching oracle user I set up, but I still get the 1017 error. Since it’s not something I really care about getting to work, I think I’m giving up.
You don’t need the prefix OPS, it would add automatically. In my situation I deactivated the prefix over init.ora and it works without sysdba.
You hate to adjust the sqlnet.ora with authentication NTS.