Problem connecting to a Remote Server over VPN

[Former Member]

SQLA 11.0.1.2913

We read some data from a Progress database into SQLA via a Remote Server and Proxy Tables. The application continues to run just fine in production, but when I try to make the same Progress connection over OpenVPN for some testing, I receive the error

Connection failed. Unable to connect to server 'AISServer': [Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'AISServer': SQLCODE: -656 SQLSTATE: HY000 SQL Statement:

I can connect successfully from the ODBC Test Connect button:

When I use the Test Connect button in Sybase Central, I receive the same error (above). My remote Server Properties are:

Not sure what else to try.

[regdomaratzki]

Is it possible that your database engine is running as a service and doesn't have the expected permissions or access to network resources that you have from a standard shell where you can successfully test the connection from the ODBC Administrator?

[Former Member]

The production SQLA database runs as a dbsrv11 service, but our test machine would be running it with dbeng11 (started automatically when I fire up ISQL for testing). I'll try running the test SQLA db under dbsrv11 and see if that makes a difference. Thanks for some clues Reg.

[Former Member]

ISQL and Sybase Central must not use ODBC? I started the SQLA databases as a dbsrv11 service, but still no success. I then enabled ODBC Tracing for all users/connections and tried the Test Connection button again. After Stopping ODBC Tracing, there was a log file, but it was empty??

Are there any other steps I can use in SQLA to see what the failure point is?

[regdomaratzki]

My next guess based on this info is that when SQLA is running as a service, it doesn't an the environment needed to load the ODBC driver in question. No driver, no trace.

I'd consider using Sysinternal's Procmon to see if SQLA can load the ODBC driver when running as a service.

[Former Member]

I will try that.

In the meantime, I started the service with the -zr all option and "connected" to one of the 2 running databases in Sybase Central. Should I be concerned about all the "Can not execute" messages in the log file? (Partial sample follows):

I. 01/04 12:47:52. SQL Anywhere Network Server Version 11.0.1.2913

I. 01/04 12:47:52. Workgroup edition I. 01/04 12:47:52. Running Windows XP Build 2600 Service Pack 3 on X86 (X86_64) I. 01/04 12:47:52. Server built for X86 processor architecture I. 01/04 12:47:52. 122604K of memory used for caching I. 01/04 12:47:52. Minimum cache size: 2048K, maximum cache size: 1526108K I. 01/04 12:47:52. Using a maximum page size of 4096 bytes I. 01/04 12:47:52. Starting database "AISInterface" (C:DatabasesAISInterfaceAISInterface.db) at Fri Jan 04 2013 12:47 I. 01/04 12:47:52. Starting database "CfsBestPractice" (C:DatabasesCfsBestPracticeCfsBestPractice.db) at Fri Jan 04 2013 12:47 I. 01/04 12:47:53. Transaction log: AISInterface.log I. 01/04 12:47:54. Transaction log: CfsBestPractice.log I. 01/04 12:47:54. Starting checkpoint of "AISInterface" (AISInterface.db) at Fri Jan 04 2013 12:47 I. 01/04 12:47:54. Finished checkpoint of "AISInterface" (AISInterface.db) at Fri Jan 04 2013 12:47 I. 01/04 12:47:54. Starting checkpoint of "CfsBestPractice" (CfsBestPractice.db) at Fri Jan 04 2013 12:47 I. 01/04 12:47:54. Database "AISInterface" (AISInterface.db) started at Fri Jan 04 2013 12:47 I. 01/04 12:47:55. Finished checkpoint of "CfsBestPractice" (CfsBestPractice.db) at Fri Jan 04 2013 12:47 I. 01/04 12:47:55. Database "CfsBestPractice" (CfsBestPractice.db) started at Fri Jan 04 2013 12:47 I. 01/04 12:47:55. Database server started at Fri Jan 04 2013 12:47 I. 01/04 12:47:55. Trying to start SharedMemory link ... I. 01/04 12:47:55. SharedMemory link started successfully I. 01/04 12:47:55. Trying to start TCPIP link ... I. 01/04 12:47:55. Starting on port 2638 I. 01/04 12:48:00. TCPIP link started successfully I. 01/04 12:48:00. Now accepting requests I. 01/04 12:48:09. <,1,CONNECT I. 01/04 12:48:09. C,1,UID=app_owner I. 01/04 12:48:09. >,1,CONNECT,1 I. 01/04 12:48:09. <,1,PREPARE,SELECT @@version, if 'A'<>'a' then 1 else 0 endif, isnull(property('IsIQ'),'NO'), isnull(connection_property('odbc_distinguish_char_and_varchar'),'Off'), isnull(connection_property('odbc_describe_binary_as_varbinary'),'Off'), connection_property('charset'), db_property('charset') I. 01/04 12:48:09. >,1,PREPARE,65536 I. 01/04 12:48:09. <,1,EXEC,65536 I. 01/04 12:48:09. P,1,[S]DUMMY<seq> I. 01/04 12:48:09. >,1,EXEC I. 01/04 12:48:09. <,1,DROP_STMT,65536 I. 01/04 12:48:09. >,1,DROP_STMT

...omitted...

I. 01/04 12:48:09. <,1,STATUS_CHANGE_NCHAR_CHARSET I. 01/04 12:48:09. >,1 I. 01/04 12:48:09. <,1,PREPARE,SET TEMPORARY OPTION ISOLATION_LEVEL= I. 01/04 12:48:09. >,1,PREPARE,65538 I. 01/04 12:48:09. <,1,DESC_OUT,65538 I. 01/04 12:48:09. >,1,DESC_OUT I. 01/04 12:48:09. <,1,DESC_IN,65538 I. 01/04 12:48:09. >,1,DESC_IN I. 01/04 12:48:09. <,1,EXEC,65538 I. 01/04 12:48:09. >,1,EXEC I. 01/04 12:48:09. <,1,OPEN,65538 I. 01/04 12:48:09. W,1,111,Statement cannot be executed

[Former Member · Accepted solution]

Thanks Reg! Procmon said that scjview could not find the DSN name. I re-created the Progress Server DSN under a different name and now it works! I can't see any differences in the registry except that my new DSN does not have a period in the name (the period works fine on the production machine). But at least we can now blame it on Windows! And more importantly, it is working!