WebAPI installation Error during WebAPI startup

marzbuzz · November 20, 2019, 8:10pm

Hi Team,

I am trying to install webapi as per the documentation specified in this link:(https://github.com/OHDSI/WebAPI/wiki/WebAPI-Installation-Guide)
but I am getting the below error during WebAPI startup in the webapi.log file under c:\tomcat\logs\ folder.
I’m able to connect to the database server in Java using dbURL below(the same as the settings) jdbc:sqlserver://11.111.55.34;DatabaseName=OHDSI

Nov 20, 2019 1:42:12 PM org.apache.tomcat.jdbc.pool.ConnectionPool init
SEVERE: Unable to create initial connections of pool.
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host serverName, port 1433 has failed. Error: “serverName. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:226)

Below is my settings.xml file. I changed the server IP to a pseudo server IP.

webapi-mssql

<datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
<datasource.url>jdbc:sqlserver://11.111.55.34;DatabaseName=OHDSI</datasource.url>
<datasource.username>ohdsi_app_user</datasource.username>
<datasource.password>app1</datasource.password>
<datasource.dialect>sql server</datasource.dialect>
<datasource.ohdsi.schema>dbo</datasource.ohdsi.schema>
<flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
<flyway.datasource.url>${datasource.url}</flyway.datasource.url>
<flyway.datasource.username>ohdsi_app_user</flyway.datasource.username>
<flyway.datasource.password>app1</flyway.datasource.password>
<flyway.locations>classpath:db/migration/sqlserver</flyway.locations>
<security.provider>DisabledSecurity</security.provider>
<security.token.expiration>43200</security.token.expiration>
<security.origin>*</security.origin>
<security.ssl.enabled>false</security.ssl.enabled>
<security.cors.enabled>true</security.cors.enabled>
<security.oauth.callback.ui>http://localhost/Atlas/#/welcome</security.oauth.callback.ui>
<security.oauth.callback.api>http://localhost:8080/WebAPI/user/oauth/callback</security.oauth.callback.api>
<security.oauth.google.apiKey></security.oauth.google.apiKey>
<security.oauth.google.apiSecret></security.oauth.google.apiSecret>
<security.oauth.facebook.apiKey></security.oauth.facebook.apiKey>
<security.oauth.facebook.apiSecret></security.oauth.facebook.apiSecret>
<security.oauth.github.apiKey></security.oauth.github.apiKey>
<security.oauth.github.apiSecret></security.oauth.github.apiSecret>
<security.oid.clientId></security.oid.clientId>
<security.oid.apiSecret></security.oid.apiSecret>
<security.oid.url></security.oid.url>
<security.oid.redirectUrl>http://localhost/index.html#/welcome/</security.oid.redirectUrl>
<security.ldap.dn>cn={0},dc=example,dc=org</security.ldap.dn>
<security.ldap.url>ldap://localhost:389</security.ldap.url>
<security.ldap.baseDn></security.ldap.baseDn>
<security.ldap.system.username></security.ldap.system.username>
<security.ldap.system.password></security.ldap.system.password>
<security.ad.url>ldap://localhost:389</security.ad.url>
<security.ad.searchBase>CN=Users,DC=example,DC=org</security.ad.searchBase>
<security.ad.principalSuffix>@example.org</security.ad.principalSuffix>
<security.ad.system.username></security.ad.system.username>
<security.ad.system.password></security.ad.system.password>
<security.ad.searchFilter></security.ad.searchFilter>
<security.ad.ignore.partial.result.exception>true</security.ad.ignore.partial.result.exception>
<security.ad.result.count.limit>30000</security.ad.result.count.limit>
<security.ad.default.import.group>public</security.ad.default.import.group>
<security.cas.loginUrl></security.cas.loginUrl>
<security.cas.callbackUrl></security.cas.callbackUrl>
<security.cas.serverUrl></security.cas.serverUrl>
<security.cas.cassvcs></security.cas.cassvcs>
<security.cas.casticket>casticket</security.cas.casticket>
<security.googleIap.cloudProjectId></security.googleIap.cloudProjectId>
<security.googleIap.backendServiceId></security.googleIap.backendServiceId>
<security.maxLoginAttempts>3</security.maxLoginAttempts>
<security.duration.initial>10</security.duration.initial>
<security.duration.increment>10</security.duration.increment>
<security.db.datasource.schema>${datasource.ohdsi.schema}</security.db.datasource.schema>
<security.db.datasource.url>${datasource.url}</security.db.datasource.url>
<security.db.datasource.driverClassName>${datasource.driverClassName}</security.db.datasource.driverClassName>
<security.db.datasource.username>${datasource.username}</security.db.datasource.username>
<security.db.datasource.password>${datasource.password}</security.db.datasource.password>
<security.db.datasource.authenticationQuery>select password from ${security.db.datasource.schema}.users where lower(email) = lower(?)</security.db.datasource.authenticationQuery>

I’m not sure what may have caused the error.

Thanks,
MB

psbrandt · November 20, 2019, 8:23pm

What does this mean?

The WebAPI is trying to connect to the IP you specified. Can you confirm that you have a MS SQL Server instance running at the given IP (11.111.55.34) and port (1143), accessible with the user credentials you’ve provided?

EDIT: Sorry, I see you said you can connect

In the sample settings, the databaseName parameter starts with a lower case letter. Does doing that make a difference?

marzbuzz · November 20, 2019, 8:42pm

I’m able to connect to the server IP using SSMS with the user ‘ohdsi_app_user’. I confirmed that the instance is running at the port 1433. The IP address(the pseudo server IP) I posted above is not the real one I tested.

I changed the settings to databaseName and recreated the WebAPI .war file using Maven, I still got the same error message.

Thanks,
MB

psbrandt · November 20, 2019, 8:45pm

I think the key is in the message:

The TCP/IP connection to the host serverName, port 1433

It seems like it’s trying to connect to the server called serverName, so you may have forgotten to replace a parameter somewhere. This is where that is specified:

github.com

OHDSI/WebAPI/blob/master/pom.xml#L25


<!-- Spring Boot manages spring.version as well -->
<spring.boot.version>1.5.20.RELEASE</spring.boot.version>
<flyway.version>4.2.0</flyway.version>
<waffle.version>1.7.3</waffle.version>
<circe.version>1.9.0-SNAPSHOT</circe.version>
<start-class>org.ohdsi.webapi.WebApi</start-class>


<!-- Primary DataSource -->
<!-- default sql server - TODO How about making postgres the default (e.g. in line with open source tech)? -alexfranken -->
<datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
<datasource.url>jdbc:sqlserver://serverName;databaseName=databaseName</datasource.url>
<datasource.username>user</datasource.username>
<datasource.password>password</datasource.password>
<!--<datasource.validationQuery>SELECT 1</datasource.validationQuery>-->
<datasource.dialect>sql server</datasource.dialect>
<datasource.ohdsi.schema>dbo</datasource.ohdsi.schema>
<datasource.dialect.source>sql server</datasource.dialect.source>
<!-- DB Change Management / Migration -->
<flyway.datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</flyway.datasource.driverClassName>
<flyway.datasource.url>jdbc:sqlserver://serverName</flyway.datasource.url>
<flyway.datasource.username>userWithWritePrivs</flyway.datasource.username>

Are you sure your settings.xml file is in the right location? Did you build the WAR file yourself? If so, did you configure the Maven profile correctly first?

EDIT: Seems like the right location should be WebAPI/WebAPIConfig/settings.xml.

marzbuzz · November 20, 2019, 8:46pm

I can’t find servername anywhere mentioned in the instruction or in the settings.xml. I built the WAR file using maven and the settings.xml is located at the C:\Git\OHDSI\WebAPI\WebAPIConfig folder.

psbrandt · November 20, 2019, 8:50pm

Are you using a command like the following:

mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P {profile id}

Where {profile id} matches the value of the <id> tag in your settings.xml file?

So if you wanted to do the Postgres build in the example:

github.com

OHDSI/WebAPI/blob/master/sample_settings.xml#L9


<?xml version="1.0" encoding="UTF-8"?>
<!--
		This is sample settings.xml file to use with Maven to configure WebAPI. The default location for the
		settings file is ~/.m2/settings.xml 
-->
<settings>
<profiles>
<profile>
 <id>webapi-postgresql</id>
 <properties>
   <datasource.driverClassName>org.postgresql.Driver</datasource.driverClassName>
   <datasource.url>jdbc:postgresql://localhost:5432/OHDSI</datasource.url>
   <datasource.username>ohdsi_app_user</datasource.username>
   <datasource.password>app1</datasource.password>
   <datasource.dialect>postgresql</datasource.dialect>
   <datasource.ohdsi.schema>webapi</datasource.ohdsi.schema>
   <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
   <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
   <flyway.datasource.username>ohdsi_admin_user</flyway.datasource.username>

You would run:

mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P webapi-postresql

marzbuzz · November 20, 2019, 8:53pm

Yes. I did. I use mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P {webapi-mssql}
since I’m using sql server.

psbrandt · November 20, 2019, 8:54pm

You shouldn’t have the curly braces. Have you tried without them? So:

mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P webapi-mssql

marzbuzz · November 20, 2019, 9:21pm

OMG, This works…
Thanks a lot, Pascal!