Matthew Shepherd · Jan 6, 2022

Unable to refresh data connection -

I use Microsoft Query via Excel to query an Intersystems database. 

Since upgrading from CACHE to Intersystems, lots of reports fail to refresh twice - by which I mean I can open the report (which may have several separate queries) and query the database once, and then it will refuse to reconnect.

I get:
[IRIS ODBC][State : S1000][Native Code 417] .... Access denied

If I then close and reopen excel, I can run the query again.

If I test the odbc connection between refresh attempts, the connection is successful.

To make matters more confusing, this happens on almost all devices, but not a few high spec laptops.

So - The query works, the connection works (but only once), and on some devices there are no issues at all.
Any idea on how to fix it?

Product version: IRIS 2019.1
$ZV: 2020.
0 143
Discussion (6)0
Log in or sign up to continue

Hi Matthew,
Can you enable auditing, enable the system event 'login failure' and check for login failures in the audit database ?
Do you have enough license available?

Thanks for the response - definitely had enough licenses, we have 250 and haven't gone over 200 since the last reboot. With enabling auditing, is there any change in server response times? The database (and software that uses the database) is in constant use.

Hi Matthew,
This should not add a lot of extra trafic - especially if you only enable the events you need to monitor (e.g. only enable login failure).

I would try to install the Caché client on these laptops, some machines at my old company had a similar problem and that solved the problem.  

Since the server version is Iris, it is better to use the latest ODBC driver from Iris ...

This is a long shot, but is the ODBC connection defined in User DSN or System DSN? We had an issue after a recent round of Windows updates where Excel suddenly wasn't always correctly seeing the System DSN connection, and setting it up under User DSN instead resolved that issue.