B4J Question [SOLVED] Using Windows Auth to connect to a MSSQL db

[alwaysbusy]

Just returned from holiday and I seem to have forgotten a lot of stuff. For some reason, I'm unable to start a jServer app from the command prompt, while it runs fine in the IDE (release mode). Breaking my head now for a couple of hours over this, when the solution is probably very obvious...

EDIT: The part on alpn-boot-8.1.7.v20160121.jar doesn't have anything to do with this error. The same happens if I start the jServer in http modus.

EDIT 2: See https://www.b4x.com/android/forum/t...h-to-connect-to-a-mssql-db.82346/#post-521438 for the solution

Log when running in the IDE (Release mode)

#Region  Project Attributes
   #CommandLineArgs:
   #MergeLibraries: True
   #AdditionalJar: jtds-1.3.1.jar
   ' for 1.8.0.74 very important!
   #VirtualMachineArgs: -Xbootclasspath/p:alpn-boot-8.1.7.v20160121.jar
#End Region

aug 01, 2017 1:31:58 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
aug 01, 2017 1:31:58 PM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
aug 01, 2017 1:31:58 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge4qv9p17pw2omdqu3ol|11d7fff, debugUnreturnedConnection...
Start B4J Analyse!
When an error occurs, check the B4JAnalyse.log file in the Objects folder to see the last B4J line it was working on.
Collecting data from B4J source files... (1/2)
ABMShared.bas
DBM.bas
ABMApplication.bas
ABMCacheScavenger.bas
ABMPageTemplate.bas
ABMSessionCreator.bas
ABMUploadHandler.bas
AgendaPage.bas
ProspectPage.bas
ReferentiesPage.bas
StartPage.bas
Geolocator.bas
OneTwo.b4j
Analysing data from B4J source files... (2/2)
GZipping HTML, JavaScript and CSS files...
Building core.min.css...
2017-08-01 13:32:08.603:INFO::main: Logging initialized @10252ms to org.eclipse.jetty.util.log.StdErrLog
2017-08-01 13:32:08.805:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT
2017-08-01 13:32:08.850:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2017-08-01 13:32:08.850:INFO:oejs.session:main: No SessionScavenger set, using defaults
2017-08-01 13:32:08.852:INFO:oejs.session:main: Scavenging every 660000ms
2017-08-01 13:32:08.893:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@aa2946{/,file:///Z:/DATA/Ideas/ABMaterial/OneTwo/Objects/www/,AVAILABLE}
2017-08-01 13:32:08.976:INFO:oejs.AbstractNCSARequestLog:main: Opened Z:\DATA\Ideas\ABMaterial\OneTwo\Objects\logs\b4j-2017_08_01.request.log
2017-08-01 13:32:08.996:INFO:oejs.AbstractConnector:main: Started ServerConnector@ece37d{HTTP/1.1,[http/1.1]}{0.0.0.0:51042}
2017-08-01 13:32:08.996:INFO:oejs.Server:main: Started @10645ms
2017-08-01 13:32:09.004:INFO:oejs.AbstractConnector:main: Stopped ServerConnector@ece37d{HTTP/1.1,[http/1.1]}{0.0.0.0:51042}
2017-08-01 13:32:09.004:INFO:oejs.session:main: Stopped scavenging
2017-08-01 13:32:09.006:INFO:oejsh.ContextHandler:main: Stopped o.e.j.s.ServletContextHandler@aa2946{/,file:///Z:/DATA/Ideas/ABMaterial/OneTwo/Objects/www/,UNAVAILABLE}
2017-08-01 13:32:09.009:INFO:oejs.Server:main: jetty-9.4.z-SNAPSHOT
2017-08-01 13:32:09.011:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@aa2946{/,file:///Z:/DATA/Ideas/ABMaterial/OneTwo/Objects/www/,AVAILABLE}
2017-08-01 13:32:09.026:INFO:oejs.AbstractNCSARequestLog:main: Opened Z:\DATA\Ideas\ABMaterial\OneTwo\Objects\logs\b4j-2017_08_01.request.log
2017-08-01 13:32:09.027:INFO:oejs.session:main: DefaultSessionIdManager workerName=node0
2017-08-01 13:32:09.027:INFO:oejs.session:main: No SessionScavenger set, using defaults
2017-08-01 13:32:09.027:INFO:oejs.session:main: Scavenging every 600000ms
2017-08-01 13:32:09.028:INFO:oejs.AbstractConnector:main: Started ServerConnector@ece37d{HTTP/1.1,[http/1.1]}{0.0.0.0:51042}
2017-08-01 13:32:09.028:INFO:oejs.Server:main: Started @10677ms
2017-08-01 13:32:09.029:INFO:oejs.session:main: Scavenging every 900000ms

Log when running from the command prompt (in the Objects folder):

Z:\DATA\Ideas\ABMaterial\OneTwo\Objects>java -Xbootclasspath/p:alpn-boot-8.1.7.v20160121.jar -jar OneTwo.jar

aug 01, 2017 1:38:25 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
aug 01, 2017 1:38:25 PM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
aug 01, 2017 1:38:25 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge4qv9p17q4dbnl0st0j|383534aa, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> net.sourceforge.jtds.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge4qv9p17q4dbnl0st0j|383534aa, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:jtds:sqlserver://ELROND/ONETWO;databaseName=OneTwoDB, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
dbm._getsql (java line: 212)
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
  at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
  at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
  at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690)
  at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
  at anywheresoftware.b4j.object.ConnectionPool.GetConnection(ConnectionPool.java:45)
  at com.onetwo.dbm._getsql(dbm.java:212)
  at com.onetwo.main._appstart(main.java:67)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
  at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
  at com.onetwo.main.main(main.java:29)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@17550481 -- timeout at awaitAvailable()
  at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
  at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
  at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
  at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
  at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
  ... 11 more

Java version (matches the alpn-boot-8.1.7.v20160121.jar for 0_74):

Z:\DATA\Ideas\ABMaterial\OneTwo\Objects>java -version
java version "1.8.0_74"
Java(TM) SE Runtime Environment (build 1.8.0_74-b02)
Java HotSpot(TM) 64-Bit Server VM (build 25.74-b02, mixed mode)

Fails on this line (where the database is a Microsoft SQL server)

Return pool.GetConnection  ' in globals: Private pool As ConnectionPool

 

[Erel]

Might be an anti-virus issue. Try to disable it.

 

[alwaysbusy]

Does not seem to have an impact. Even on a pc without a virus scanner installed I have the same issue. Only with this app, but on every machine I do it. Even on the same machine (my develop machine) it runs in the IDE flawless, running it from the command prompt simply as 'java -jar OneTwo.jar' gives this error.

I was out of office for 3 weeks, so maybe our network engineer made some changes I'm unaware of. I suspect he did something as just creating an ODBC connection in windows to this db gives this error:

He is back in a week or two, I'll know more then...

Still, a bit puzzled why it does work in the B4J IDE. Some hidden backdoor build in maybe?

 

[alwaysbusy]

Just for reference if someone has the same problem:

1. It is for MSSQL databases only
2. You are using windows authentication to login in the database

Download: https://sourceforge.net/projects/jtds/files/jtds/1.3.1/

There is a 32bit and 64bit version of the ntlmauth.dll file in the download.

copy the correct version of the dll to your java folder. If it is the jdk, copy it in the \bin folder + in the jre\bin folder. If it is the jre, you only have to copy it in its \bin folder.

- in c:\Program Files\java\ are the 64bit versions of java
- in c:\Program Files (x86\java\ are the 32bit versions of java

If a new version of java is installed, you have to repeat this. This is what happened in my case: our system engineer had installed a newer version of java during my holidays, and those .dll files had to be copied to the new folders too! Took me about a day to find out...