|
Good day for you! After creating DSN record (odbcad32.exe) and checking connection i have "connection successful" answer. But if i trying to connect with vbs, i have the next error: "Microsoft OLE DB Provider for ODBC Drivers: [SAP][ODBC Driver][SQL Anywhere]Invalid user ID or password" cat odbc_test.vbs Code: Set conn = CreateObject("ADODB.Connection")
conn.Open "DSN=TEST;UID=dba;PWD=sql;"
WScript.Echo "Connected."
What am I doing wrong? The error is reproduced on windows 10 x64 and windows 11 arm. Other details (added): sajdbc4.jar + java and the same credentials works fine java + jconn4.jar - "Invalid user id" python + JayDeBeApi + sajdbc4.jar - "Invalid user id" odbc drivers - "Invalid user id" (python/vbs) Github issue: link |
|
Another very very very wild guess: Is there a particular login_procedure defined for that particular database? - Simply because such a procedure could be coded to check the usage of particular applications or APIs based on checking connection_property('AppInfo'), connection_property('ClientLibrary') or the like and reject undesired access (even with correct credentials) via SQLE_INVALID_LOGON... I think this is very close to the truth. Because the difference between "java + sajdbc4.jar" and "python + JayDeBeApi (middle layer for jdbc) + sajdbc4.jar" is only in the executable. I'll try to clarify this point with the administrator of database with problem Thank you very much!
(09 Mar '23, 13:24)
mrV
Maybe you know a way to test this guess? As an example: change any connection string argument and make java+sajdbc4.jar also unusable.
(09 Mar '23, 14:22)
mrV
Replies hidden
Well, when successfully connected, you can check whether the login_procedure is set or not. If it is then you might check the source of the according procedure in SYSPROC (unless access is restricted or code is hidden). If it is and you aren't allowed or able to get the procedure'code, well, then you are probably limited to black box testing because... well, it could do whatever the coder has made it do... If you suspect it checks the application's path/name, varying this with working vs. currently not working apps would seem worthwhile to test. Note, I have never needed such tests, so what do I know:)
(09 Mar '23, 15:18)
Volker Barth
Assuming you can connect, this one line command will tell you if a login_procedure is set. dbisql -c "uid=DBA;PWD=sql" select connection_property('login_procedure') The default is "sp_login_environment".
(09 Mar '23, 15:48)
JBSchueler
|
If you can successfully connect while creating the ODBC DSN, you either have to specify valid credentials within the DSN or rely on the environment variable SQLCONNECT to supply those.
Could it be the DSN TEST has different credentials than those supplied via VBS?
Just to add: What kind of DSN is this – User DSN or System DSN? Note, those behave differently when run on different bitness (System DSNs need to be created for the according bitness whereas User DSNs are automatically "shared" between 32 and 64 bit apps...)
Credentials are the same.
I think if the bitness of the system did not match, there would be a different error.
Yes, but that was not my point: If you were using System DSNs, 32 bit and 64 bit clients would use different DSNs (as System DSNs are not shared), so their individual settings might be different and thereby lead to different behaviour.
As you are apparenlty using a User DSN, this issue should not apply here.
I'd suggest to add the LOG connection parameter to get connection debug messages. (And of course we do not know the exact settings of your ODBC DSN and your database server configuration and therefore cannot tell whether there is something missing or not. But with the LOG parameter, it should tell you what's happening...)
I would also check your connection parameters using dbping as follows: dbping -d -m -c "DSN=Test;UID=dba;PWD=sql;"
-d make a database connection, -m use ODBC
Make sure to use bin32 dbping, since you are using 32-bit cscript.
If that works, I can't see why your cscript connection wouldn't.
I used 64 bit сscript. dbping works fine.
You may have used 64-bit cscript, but your screen snap shows 32-bit cscript: c:\windows\SysWOW64\cscript.exe test.vbs. So please also try bin32 dbping.
Well, apparently the credentials do not fit.
The log should intitially list the full connection string with values extracted from the DSN, such as
Do the credentials fit? And are they different when you connect in one of the methods/APIs that do succceed?
As stated, without knowing your connection string parts and your environment (engine locally or on antoiher machine, auto start...), it's mostly guesswork on our part.
Well, Jack asked for the 32 bit call (from bin32)...
FWIW, you can easily use the LOG connection parameter with dbping. Do the 32 bit and 64 bit dbping calls lead to similar LOG contents?
And of course you may think about getting rid of DSNs altogether, as SQL Anyhwere has allowed DSN-less connections for years...
One additional caveat: Could it be there are system and user DSNs with the same name for the according user? This is allowed (and not uncommon on my own machines :)) but might lead to ambiguity which DSN is actually used, and in case their settings are not identical, could lead to surprising behaviour.
If you want to try DSN-less connections, then you would have to use Driver=. That would change your simple test to:
Set conn = CreateObject("ADODB.Connection")
conn.Open "Driver=SQL Anywhere 17;UID=dba;PWD=sql;"
WScript.Echo "Connected."
Or you could use the SQL Anywhere OLEDB Provider, rather than MS OLEDB for ODBC. That would change your simple test to:
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SAOLEDB;UID=dba;PWD=sql;"
WScript.Echo "Connected."
I thought that using cscript from SysWOW64 (not from system32) means using exactly the 64-bit version of cscript. If not, then how to run the 64-bit version of cscript?
dbping from bin32 also works fine
I don't want to use dsn-less connections. I was tried to connect like this.
DSN-less connections from start message:
java + jconn4.jar - "Invalid user id"
java + sajdbc4.jar and the same credentials works fine
python + JayDeBeApi + sajdbc4.jar - "Invalid user id"
I use DSN to show that absolutely identical connections depend only on the executable
SysWOW64 means roughly "system for windows on windows-64". That is 32-bit Windows on 64-bit Windows. When 64-bit Windows was developed, system32 became the repository for 64-bit DLLs, this despite the "32" in the folder name. I guess Microsoft decided that they were stuck with everyone being used to "system32". So that meant coming up with a "system32" equivalent for 32-bit applications, which they called "SysWOW64".
So to run 64-bit cscript, you normally just type "cscript" since C:\Windows\system32 is usually in your PATH.
On a side note, if you are ever browsing a part of the registry that is under "WOW6432Node" then you are looking at entries for 32-bit applications. For example, HKLM\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI.
But getting back to your problem. I really can't say what the issue is. What happens when you try 64-bit cscript or switch to SAOLEDB instead of OLEDB for ODBC?
Actually, just two more thoughts. I noticed that your DSN uses Encode password. What happens if you try a plain text password (not that I recommend this) in the DSN. That would be Encode password: None on the "Login" page. You'll have to retype the Password field.
Also, it might be useful to see what "dbdsn -gu TEST" shows. Also what "dbdsn -gs TEST" shows. And last but not least, what "c:\...\bin32\dbdsn -gs TEST" shows.
I recall one time someone had defined both user and system DSN with the same name, leading to lots of confusion.
Yes, as stated in a different comment here, if there are both user and system DSNs with the same name, it seems undefined which one is actually used (and I don't know whether it might even change between calls...), so unless both have identical settings, that could easily lead to unexpected behaviour. But using dbdsn -gu resp. -gs for all three possible DSN types (user, system 32-bit, system 64-bit) should clarify this.
I have only one User DSN and no one System DSN.
Thank you for the clarification.
A very strange decision to preserve backwards compatibility.
OK, so it's verified that there are no system DSNs, as even 64-bit system DSNs would be displyed in the 32-bit ODBC Administrator.
As you are using a user DSN, is it possible that the different tests run with different accounts like LocalSystem (which might use different user DSNs)? - I'd still suggest the LOG connection parameter should help to identify whether calls from the different tools/APIs use the same connection parameters and credentials...
I already checked the logs. And the connection, guaranteed, is doing with the same DSN Moreover, checks have shown that this problem is only for this database. Everything works fine with other databases.