IRIS ODBC Connection String format
We are updating a C# module which creates consumes an OdbcConnection from System.Data.Odbc. This module used to create a DSN-less connection through the InterSystems Cache driver, and we are updating it to call the IRIS ODBC35 driver following migration of the target database.
The Cache connection was formed formatted as follows:
"DRIVER={InterSystems ODBC};SERVER=[server_address];DATABASE=[cache_namespace];UID=$uid$;PWD=$pwd$;"When trying to simply update the connection string to replace the DRIVER value to "{InterSystems IRIS ODBC35}" we get the following error:
Access Denied ERROR [HY000] [Iris ODBC][State : HY000][Native Code 417] [our_debug_path.exe] Access Denied ERROR [HY000] [Iris ODBC][State : HY000][Native Code 417] [our_debug_path.exe] Access Denied
We have proven connectivity and user credentials via a DSN with the same values provided and a successful DSN test. So I have two working theories:
1) The DSN-less connection string format is wrong
2) We can authenticate, but there is something amiss with the user account permissions so fail authorisation and don't get a session.
I don't have access to the IRIS server or audit to investigate theory 2), but my IRIS colleague is confident we are not authenticating at all.
Comments
I didn't find in the documentation the line {InterSystems IRIS ODBC35}:
Try
Driver=InterSystems ODBC Driver;...
or
DRIVER={InterSystems ODBC35};...Thanks for this. We have tried that and get the following error.. While that documentation appears to be IRIS specific, I fear it is inaccurate...?
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
The registry lists the following keys for ODBC Drivers, so I presume we're using a registry key lookup (into the 32-bit WOW namespace, given the app is still 32-bit)
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers] "InterSystems ODBC"="Installed""InterSystems Legacy ODBC"="Installed" "InterSystems IRIS ODBC35"="Installed"
You are right, this is a mistake in the documentation.
Here is part of my code for IRIS 2023.2:
var connection =
new OdbcConnection(
"DRIVER={InterSystems IRIS ODBC35};SERVER=localhost;PORT=1972;DATABASE=USER;UID=_system;PWD=SYS;Unicode SQLTypes=1;Query Timeout=1");
Connection and display of the query result were successful.
thanks for all the support. It transpires that the format of the connection string was, and always had been correct. There was an error in the application code where the credentials were not being updated correctly in the connection string, hence the Access Denied error. We've patched that logic and are now connecting successfully.