Solved SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

General discussion forum about all databases other than SqlBase.
markkeogh

SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by markkeogh » 01 Nov 2016, 22:59

According to the documentation if I want to connect to Oracle using OS authentication all I do is
os.png

But this simply doesn’t work as advertised. I have a user defined as IDENTIFIED EXTERNALLY but with the following trimmed down SQL.INI

Code: Select all

[win32client.dll]
comdll=sqlora32

[win32client.ora32]
log=c:\temp\sql.log

[oragtwy]
remotedbname=database_name,@tns:database_name (BOTH FAIL)
remotedbname=database_name2,@database_name2   (BOTH FAIL)

longbuffer=3021996
substitute=SYSSQL.,
fetchrow=200

[win32client]
clientname=Win32User
Here my extremely complex example code ;-)

Code: Select all

!!CB!! 134
Function: g_Test
                Description:
                Returns
                Parameters
                Static Variables
                Local variables
                                Sql Handle: hSqlConnection
                Actions
                                Set SqlDatabase = 'database_name'
                                Set SqlUser = 'dummy'
                                Set SqlPassword = ''
                                !
                                Call SqlConnect( hSqlConnection )
And I always end up with a 401 cannot open database.

If I use the same code with a credentialed oracle user, i.e. proper username and password this code works perfectly.
EVERY OTHER SINGLE PIECE OF SOFTWARE ON MY PC WORKS AS EXPECTED FROM SQLPLUS TO EXCEL
You do not have the required permissions to view the files attached to this post.
Last edited by markkeogh on 02 Nov 2016, 22:30, edited 5 times in total.

Mike Vandine

Re: SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by Mike Vandine » 02 Nov 2016, 02:59

Hmmmmmmm. The 401 is always a message that says it can't *find* the database, not that the password is incorrect or user unknown, etc.

So there must be something wrong in the sql.ini setup (or that's not the sql.ini file it's actually using). Does anyone else who uses Oracle regularly have any suggestions as to what might be going wrong?

Mark, to make sure that this sql.ini file is the one actually being used, modify the ini file thus:

[win32client]
xxx=xxx

If this sql.ini file is being used, you should get an error immediately. If it isn't the one being used, just open a command prompt and type in sql.ini. Notepad (or wordpad) should open the 'default' sql.ini file.

Best regards,

markkeogh

Re: SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by markkeogh » 02 Nov 2016, 03:07

Yeah been through all that, but this is the one and only SQL.INI file we've been using for 20 years ;-)

If I connect using a username and password it connects fine
If I connect via sqlplus like this then all is fine

Code: Select all

sqlplus /@database-name


Since moving to 6.3 we have to change SQL.INI entries from remotedbname=database_name,@tns:database_name to remotedbname=database_name,@database_name as leaving the tns: in creates a massive slow down for each connection.
Last edited by markkeogh on 02 Nov 2016, 22:27, edited 1 time in total.

markkeogh

Re: SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by markkeogh » 02 Nov 2016, 03:37

So further results

Code: Select all

valid_usr/valid_pwd@database        OK!
invalid_usr/invalid_pwd@database    ORA-01017: invalid username/password; logon denied
dummy/@database                     401 Cannot open database
dummy/dummy@database                401 Cannot open database

markkeogh

Re: SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by markkeogh » 02 Nov 2016, 06:20

So if there's anyone out there who's even vaguely interested.

If you have multiple databases defined in your SQL.INI it fails if the database you're looking for isn’t the first one defined. This is obviously not good where you have an SSO environment whereby you have multiple databases, which I'm assuming would be most of them given the requirements of SSO in the first place.
SNAG_Program-0143.png
You do not have the required permissions to view the files attached to this post.

User avatar
Charlie
Canada
Posts: 595
Joined: 07 Mar 2017, 18:52
Location: Fredericton, New Brunswick, Canada

Re: SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by Charlie » 02 Nov 2016, 11:44

Good sleuthing, markkeogh.

Although we are still on TD5.2, this is very good info to keep in mind when we get around to upgrading to a latest version of TD.

Thanks !

markkeogh

Re: SOLVED(ish): Oracle user IDENTIFIED EXTERNALLY connection

Post by markkeogh » 02 Nov 2016, 22:18

Charlie wrote:Good sleuthing, markkeogh.

Although we are still on TD5.2, this is very good info to keep in mind when we get around to upgrading to a latest version of TD.

Thanks !
My pleasure, but I'm hoping the ever so good looking, tall, muscular gods gift to world Mike Vandine, ;-), might put a rocket up someone to get us a patch fix or at least get a fix into the next SP as I'm guessing the solution is very low impact.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests