Preconditions

JDBC driver for one of the following databases:

Oracle: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/ (The driver version must match the database version.)
Microsoft SQL Server: http://msdn.microsoft.com/data/jdbc/ (The driver version must match the database version.)

Provided by Amana:

You should find the following folder structure in your distribution after unpacking the provided ZIP file:

databasescript → all necessary update scripts for the database
deployment → war-file with the current application

Installation of the Apache Tomcat Server

1. In the following we assume an installation on a Windows operating system. An installation on a Unix-like system differs only in the paths and the installation procedure described under 2. and 3.
2. Install the recommended JAVA version and either create the system variable "JAVA_HOME" which points to the installation directory or set the JavaHome path in the Tomcat start scripts. Be sure to disable the automatic Java updates.
3. Install the Apache Tomcat Server into a directory of your choice (Please note: too deep subdirectory structures can lead to path length problems). In the following the directory "C:\Server\Tomcat" is assumed. This is explained in detail:

• We recommend to install the start menu entries to simplify the configuration.
• The ports used by Tomcat can be customized to your needs.

Configuration of the Tomcat Server:

1. After the installation run the application ->Start menu->Tomcat->"configure tomcat".
2. Execute the following steps:
   1. In the "General" window: Set the start of the service to automatic.
   2. Enter "128" in the Initial Memory Pool window.
   3. In the Maximum Memory Pool window, enter about half of the available physical RAM. For 32 bit systems please consider the limitation of the RAM to approx. 3.5 GB. The memory settings configured here influence the performance and operability of the application. These may have to be adjusted over time as the use of the product increases.
   4. In the "Java" window, add the entry ""--add-opens=java.base/java.util=ALL-UNNAMED" in the "Java 9 Options" field.
   5. If necessary, set the server encoding manually to UTF-8. The parameter -Dfile.encoding=UTF-8 can be set in the "Java Options" for this.

3. In the web.xml in the conf directory of the Tomcat server (%TOMCAT_HOME%/conf) a <init-param> block with parameter "mappedfile" = false must be added in the servlet with name <servlet-name>jsp</servlet-name> section.

   Code Block
   language xml title web.xml
   <servlet> <servlet-name>jsp</servlet-name> <servlet-class>org.apache.jasper.servlet.JspServlet</servlet-class> <init-param> <param-name>fork</param-name> <param-value>false</param-value> </init-param> <init-param> <param-name>xpoweredBy</param-name> <param-value>false</param-value> </init-param> <init-param> <param-name>mappedfile</param-name> <param-value>false</param-value> </init-param> <load-on-startup>3</load-on-startup> </servlet>

   
   

4. The following adjustments should be made in the context.xml of the Tomcat server:

   We recommend increasing the cacheMaxSize attribute of the Resources element to 100000 kilobytes (100MB):

   Code Block
   language xml title content.xml
   <WatchedResource>WEB-INF/web.xml</WatchedResource> <Resources cachingAllowed="true" cacheMaxSize="100000" />

   
   

5. Please copy the deployment (usually "vat.war") from the deployment folder of your distribution into the "webapps" directory in the Tomcat installation directory.

Setting up the Database

The steps for setting up the database are kept general at this point in order to do justice to the different configurations of the database systems.

1. Create a new database/database schema on the database server.
2. Create a database user with permission for CREATE, INSERT, UPDATE, DELETE, ALTER on this database. (For Oracle: CONNECT + RESSOURCE).
3. Pleas Note for Oracle: As of Oracle 12, the Oracle user must receive quotas on his/a tablespace, e.g:

   
   ALTER USER amanavat QUOTA 1000M ON users;

   ALTER USER amanavat QUOTA UNLIMITED ON amanavat;
4. Make sure that the database can be accessed from the application server with this user.
5. Execute the scripts included in your distribution to create the data tables.
   Make sure that the user who imports the DB scripts has the necessary rights to create the table structure.
   Should, contrary to expectations, error messages occur during the execution of the scripts, please contact our support and send us the execution log.

Variant 1: Configuration of JNDI (Datasource) database connection with user/password

Please copy the appropriate JDBC driver into the lib directory of the Apache Tomcat server (%TOMCAT_HOME%/lib).

In the context.xml of the Apache Tomcat Server a corresponding resource entry for the database connection must be created within the <Context> block:

Example Datasource entry for MSSQL:

 <Resource name="jdbc/gtcvatdatasource" auth="Container"
 	type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url="jdbc:sqlserver://[SERVER-ADRESS]:[PORT];DatabaseName=[DATABASE_NAME]"
    username="[USERNAME]" password="[PASSWORD]" maxTotal="20" maxIdle="10"
    maxWaitMillis="-1"/>

Example Datasource entry for Oracle:

 <Resource name="jdbc/gtcvatdatasource" auth="Container"
 	type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@[SERVER-ADRESS]:[PORT]:[INSTANCE]"
    username="[USERNAME]" password="[PASSWORD]" maxTotal="20" maxIdle="10"
    maxWaitMillis="-1"/>

Please adjust the SERVER-ADRESS of the database server, the database name and the user/password in the Datasource entry according to your database.

Variant 2: Configuration of JNDI (Datasource) database connection with Windows user

Please copy the appropriate JDBC driver into the lib directory of the Apache Tomcat server (%TOMCAT_HOME%/lib).

The database connection in the context.xml of the Tomcat server can alternatively be established via the Windows user in whose context the Tomcat is running. The following steps have to be performed:

1. Download Microsoft SSPI driver (sqljdbc 7.0): https://www.microsoft.com/de-de/download/sqljdbc_7.0

   Info
   The used JAVA version is important: A 64-bit JAVA requires the x64 version of the driver, a 32-bit Java requires the x86 version.

   
   

2. 
   

   The driver is delivered in a self-extracting EXE, which can be unpacked e.g. by 7-Zip or alternatively the driver can be downloaded as tar.gz archive.

3. Copy the sqljdbc_auth.dll from the subdirectory "/auth" into the "\windows\system32" directory of your Windows installation.
   Image Added
4. Copy the file sqljdbc42 from the subdirectory of the driver "/jre8" into the "/bin" directory of your installed JAVA version (which is used by Tomcat).
   
   Image Added
   
   Image Added

5. In the context.xml of the Tomcat server a corresponding datasource entry must be configured for the database connection:

   Code Block
   language xml title MSSQL Datasource linenumbers true
   <Resource name="jdbc/gtcvatdatasource" type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://[SERVER-NAME]\SQLEXPRESS;DatabaseName=[DATABASE_NAME];Integratedsecurity=SSPI" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/>

   
   

6. In addition, the Windows user must be entered in the "Tomcat Properties" - see illustration:

   Info
   Please note that the user also has the right to log in as a "service", otherwise Tomcat will not start anymore.

   Image Added

Setting up a TLS connection to the SQL server

As of driver version 10.x of mssql-jdbc (Microsoft SQL server), a connection without TLS must be explicitly configured, otherwise the driver assumes a TLS connection and expects certificates provided for this.

To disable this, the context.xml of the Tomcat server must be extended as follows:

 <Resource name="jdbc/gtcvatdatasource" auth="Container"
 	type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url="jdbc:sqlserver://[SERVER-ADRESS]:[PORT];DatabaseName=[DATABASE_NAME];encrypt=false;trustServerCertificate=true"
    username="[USERNAME]" password="[PASSWORD]" maxTotal="20" maxIdle="10"
    maxWaitMillis="-1"/>

Crucial are encrypt=false and trustServerCertificate=true in the url.

If the TLS connection should be used, then the following information is needed:

 <Resource name="jdbc/gtcvatdatasource" auth="Container"
 	type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url="jdbc:sqlserver://[SERVER-ADRESS]:[PORT];DatabaseName=[DATABASE_NAME];hostNameInCertificate=[HOST_NAME_IN_CERTIFICATE];trustStore=[TRUSTSTORE_LOCATION];trustStorePassword=[TRUSTSTORE_PASSWORD]
    username="[USERNAME]" password="[PASSWORD]" maxTotal="20" maxIdle="10"
    maxWaitMillis="-1"/>

The SQL server must be secured with a certificate and this must be located in a TrustStore/KeyStore. Both the path to the TrustStore and the password are given, as well as the host name in the certificate.

Information about the parameters:

https://docs.microsoft.com/de-de/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver16

Information about configuring:

https://docs.microsoft.com/de-de/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver16

https://docs.microsoft.com/de-de/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16

https://docs.microsoft.com/de-de/sql/connect/jdbc/configuring-the-client-for-ssl-encryption?view=sql-server-ver16

System start and activation

1. Now start the Tomcat application server either under the system services or with the "Configure Tomcat" application.
2. Start a browser and type in the address bar (note the port selected in "Install Tomcat"):
   http://<IP address or hostname of the application server>:[Port]/vat
3. It should now load a login page, there you can log in with
   User: superuser
   Password: test
4. Now a web page should ask you for activation. Copy the contents of the upper text window into an e-mail and send it to your contact person in our company. Alternatively, you can click the "Request activation" button, which automatically creates an e-mail.
5. We will send you the activation in the form of a text file. Please copy its content into the text window below and confirm by clicking on "Activate".
6. If an error should occur with one of the previous points, please check the parameters entered under Setup of the application and contact us.

Final remarks

After the activation of the VAT@GTC the installation is completed successfully. The software can now be handed over to the relevant departments for use or specialist tests.

Finally, a few remarks:

• We recommend that you back up the database regularly, as it contains the data of the application.
• The deployment does not have to be backed up continuously. In this case it is sufficient to keep the status at the delivery of a version ready.
• You should document the configuration carried out so that adjustments can be made in the event of system updates or a recovery. In particular, adjustments that deviate from this document must be documented.