Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Verify that DB Server is up and running
  • Verify that DB Server machine is accessible from the ADSS Server machine
  • Verify that DB Server is accessible from other machine if ADSS Server machine is unable to communicate
  • Verify that DB Server is accessible from other client tool from the machine where ADSS Server is installed
  • Verify that DB password/rights are not changed for the user configured in ADSS Server
  • Verify that DB pool size is not exhausted, if so then restarted the DB Server and then ADSS Server
  • Check the DB health so that it response in a timely fashion
  • Verify that disk space is not fully utilized where DB Server is installed
  • Verify that logs/CRL archiving is not on the same drive where DB Server/ADSS Server is installed so that space is not consumed by them
  • Verify that "Store input and output documents in the transactions log" option is unchecked if not deemed necessary in the Server Manager sub-module of the Signing, Verification and Go>Sign Service so that database size does not grow and also to keep the DB size small.

How can I configure ADSS Server with database server running over SSL/TLS Authentication?

The following are the steps to configure the ADSS Server with database server running over SSL/TLS Authentication:
Info

If the ADSS Server Core, Console and Service components are running on separate machines then the changes have to be made on all the ADSS Server instances separately

  1. Launch the ADSS Server Console and register the issuer CA of the database TLS/SSL Server Authentication Certificate in the Trust Manager with purpose CA for verifying SSL client certificates.

    Info
    titleDatabase Machine Name

    Make Sure that TLS Server authentication certificate must include Machine Name/Domain Name/IP Address of the relevant database server deployment in certificate's Common Name and also as SAN extension.

  2. Stop the ADSS Server Core, Console and Service instances from Windows services or Unix daemons
  3. Uninstall these services by executing the [ADSS-Server-Installation-Dir]/setup/uninstall.bat/sh script by right clicking and choosing Run as administrator in case of Windows
  4. Open the [ADSS -Server-Installation-Dir]/setup/bin/internal.bat/sh file in edit mode and add the parameter -Djsse.enableCBCProtection=false after the memory parameters as shown below:

    Code Block
    titleinternal.bat
    themeRDark
    linenumberstrue
    languagepowershell
    firstline1
    "../jdk/jre/bin/java" -Xms32M -Xmx256M -Djsse.enableCBCProtection=false -cp ../console;../service/server/
  5. Edit the [ADSS -Server-Installation-Dir]/tomcat/bin/service.bat/sh file and add the parameter -Djsse.enableCBCProtection=false for ++JvmOptions as shown in the following snippet:

    Code Block
    titleservice.bat
    themeRDark
    linenumberstrue
    languagepowershell
    firstline1
    rem More extra parameters
    set "PR_LOGPATH=%CATALINA_BASE%\logs"
    set PR_STDOUTPUT=auto
    set PR_STDERROR=auto
    "%EXECUTABLE%" //US//%SERVICE_NAME% ++JvmOptions "-Djsse.enableCBCProtection=false;-Dorg.apache.catalina.connector.CoyoteAdapter.ALLOW_BACKSLASH=true;-Dorg.apache.tomcat.util.buf.UDecoder.ALLOW_ENCODED_SLASH=true;-Djava.io.tmpdir=%CATALINA_BASE%\temp;-Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager;-Djava.util.logging.config.file=%CATALINA_BASE%\conf\logging.properties" --JvmMs %6 --JvmMx %7
    echo The service '%SERVICE_NAME%' has been installed.
    :end
    cd "%CURRENT_DIR%"
    exit
  6. Open the [ADSS -Server-Installation-Dir]/util/bin/export_logs.bat/sh file in edit mode and add the parameter -Djsse.enableCBCProtection=false after the memory parameters as shown below:

    Code Block
    titleexport_logs.bat
    themeRDark
    linenumberstrue
    languagepowershell
    firstline1
    call setclasspath.bat
    %JAVA% -cp %CLASSPATH% -Xms32M -Xmx128M -Djsse.enableCBCProtection=false com.ascertia.adss.util.export.logs.ASC_LogsExportUtility %*
    pause
  7. Open the [ADSS-Server-Installation-Dir]/setup/bin/compute_hmac.bat/sh file in edit mode and add the parameter -Djsse.enableCBCProtection=false to compute the HMAC over SSL:

    Code Block
    titlecompute_hmac.bat
    themeRDark
    linenumberstrue
    languagepowershell
    firstline1
    call bin\setclasspath.bat
    %JAVA% -cp %CLASSPATH% -Djsse.enableCBCProtection=false com.ascertia.adss.setup.util.hmac.ASC_HmacRecomputationManager %1
    exit
    
  8. Open the [ADSS -Server-Installation-Dir]/conf/hibernate.cfg.xml file in edit mode and update the hibernate.connection.url element by appending ;ssl=require as shown below:

    Code Block
    titlehibernate.cfg.xml
    themeRDark
    linenumberstrue
    languagexml
    firstline1
    <property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:jtds:sqlserver://db-server-name:1433/adss;ssl=require</property>
    <property name="hibernate.connection.username">sa</property>
    <property name="dialect">com.ascertia.adss.common.dialect.ASC_SQLServerDialect</property>
  9. Open the  [ADSS -Server-Installation-Dir]/tomcat/bin directory and run the install_core.bat/sh, install_console.bat/sh, install_service.bat/sh scripts by right clicking, choosing the Run as administrator one by one in case of Windows.

  10. Start the ADSS Server Core, Console and Service instances from Windows services or Unix daemons so that the connection is established with the database server over SSL/TLS Authentication.

How to change the ADSS Server database authentication scheme from SQL authentication to Windows authentication and vice versa?

 If you have an existing installation of ADSS Server which is using SQL authentication and you are planning to change it to Windows authentication (and vice versa) then follow these steps:
  1. Stop the ADSS Sever Core, Console and Service instances from the Windows Services Panel / UNIX Daemon.
  2. Go to location [ADSS Server Installation Directory]/conf/
  3. Open the file hibernate.cfg.xml in a text editor and search the property <property name="hibernate.connection.url"> and <"hibernate.connection.username">, make the changes accordingly:

    • For Windows Authentication (Kerberos):

      Code Block
      titlehibernate.cfg.xml
      themeRDark
      linenumberstrue
      languagexml
      firstline1
      <property name="hibernate.connection.url">jdbc:jtds:sqlserver://<DATABASE_MACHINE>:1433/<DATABASE_NAME>;integratedSecurity=true;</property>
      <property name="hibernate.connection.username"></property>

      Note: User name must be left empty or username property must be removed in case of Windows Authentication (Kerberos)


    • For Windows Authentication (NTLM):

      Code Block
      titlehibernate.cfg.xml
      themeRDark
      linenumberstrue
      languagexml
      firstline1
      <property name="hibernate.connection.url">jdbc:jtds:sqlserver://<DATABASE_MACHINE>:1433/<DATABASE_NAME>;domain=<DOMAIN_NAME>;useNTLMv2=true</property>
      <property name="hibernate.connection.username">DOMAIN_USER_NAME</property>
      
    • For SQL Authentication:

      Code Block
      titlehibernate.cfg.xml
      themeRDark
      linenumberstrue
      languagexml
      firstline1
      <propertyname="hibernate.connection.url">jdbc:jtds:sqlserver://<DATABASE_MACHINE>:1433/<DATABASE_NAME></property>
      <property name="hibernate.connection.username">DATABASE_SERVER_USER_NAME</property>
      
  4. Change the password of the ADSS Server database by following this link: HowtochangethedatabasepasswordifADSSServerisalreadyinstalled
  5. Start the ADSS Sever Core, Console and Service instances from the Windows Services Panel / UNIX Daemon.

Why does the SQL Server database size increase after deleting records?

Background:
The SQL Server database grows upon deleting records because the transaction logs file substantially increases when the data is deleted/updated or created within the database.  Also upon deleting the data the used database space is not automatically released and a shrink operation needs to be performed.

Workaround:
The size of the ADSS Server database can be reduced by following these simple instructions, the instructions are written keeping in mind the SQL Server 2005 Enterprise, these may slightly change based on your specific version of the SQL Server database:
  1. Stop the ADSS Server Core, Console and Service instances from Windows NT Services panel
  2. Launch the SQL Server 2005 Enterprise management studio and connect to the database engine with relevant username and password
  3. Right click on the ADSS Server database > click on properties > point to Options tab
     
  4. On the above screen select the Recovery Model as “Simple” and click on “OK” buttonRight click on the ADSS Server database again > Point to Tasks > Shrink > Files
     
  5. On above screen select “Data” as File type, Enable the option to “Reorganize pages before releasing unused space” and provide the minimum recommended value for the “Shrink file to” parameter e.g. 681 in above case. Click on “OK” button.
  6. Wait for the database file shrink operation to complete.  The time required for the database file shrink operation depends upon the number of records present in the ADSS Server database.  In our lab, it took us 3 minutes to reduce the 2.5 GB database to 600 MB.
  7. Right click on the ADSS Server database again > Point to Tasks > Shrink > Files
     
  8. This time select “Log” as File type, select the option to “Reorganize pages before releasing unused space” and provide the minimum recommended value for the “Shrink file to” parameter e.g. 0 in above case.  Click on “OK” button
Database size reduction task has been performed.
  

Configuring SQL Server to use correct auto-increment for identity columns

Background: 
There is a "feature" (defect) in SQL 2012 where the identity values are jumping by around 1000 each time the service is restarted (Planed/Unplaned). Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this one can see identity gaps between database records after rebooting the SQL server instance or server machine. There are several connect item on the issue:
https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Workaround: 
Follow these instructions to configure SQL Server 2012 to work without problems with the ADSS Server:
  1. Open SQL Server Configuration Manager
  2. Click SQL Server Services on the left pane
  3. Right-click on your SQL Server instance name on the right pane -> Default: SQL Server(MSSQLSERVER)
  4. Click Properties.
  5. Click Startup Parameters.
  6. On the specify a startup parameter textbox type "-T272"
  7. Click Add and
  8. Confirm the changes
Info
titleCurrect Status

On planned shutdown the issue is fixed in SQL Server 2014 but still there for Unplaned shutdown

How can I handle the growing ADSS Server database temp db?

  1. Stop the ADSS Server Core, Console and Service instances from Windows services panel or UNIX daemon
  2. Launch the SQL Server management studio and connect to the database server
  3. Right click on the respective database for ADSS Server > Properties > Options
  4. Look for the property Is Read Committed Snapshot On, ensure that the value of this properly is set to False
     
  5. Start the ADSS Server Core, Console and Service instances from Windows services panel or UNIX daemon

 

How can I determine the number of DB connections acquired by the ADSS Server?

The following SQL Queries are used to determine the DB connection count:  
Code Block
themeEclipse
languagesql
firstline1
#SQL Server
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame;

Special instructions for Percona XtraDB environment (Galera cluster)

  1. Disable the ECC cipher in Catalina. Follow this KB article and make changes accordingly in catalina.sh.
  2. Increase the value of max_connect_error parameter on MySQL. Follow the instructions at this link and set max_connect_errors=10000.

  3. If you're accessing the cluster through a load balancer, make sure that the idle timeout on the proxy is greater than 28800 seconds (default value in MySQL and hibernate).

 

Special Instructions to Install ADSS Server with MySQL

v5.x

  1. Edit the my,cnf file e.g. /etc/mysql/my.cnf in a text editor and perform following steps:
    1. Add a new variable lower_case_table_names=1 (If not added then you will face this error Invalid default value for 'CreatedAt')

    2. Remove the values "NO_ZERO_IN_DATE,NO_ZERO_DATE" from variable sql_mode (If not removed you will face this error Invalid default value for 'CreatedAt')

    3. Set the variable lower_case_table_names value to 1 (If not set to 1 then you will face this error MySQLSyntaxErrorException: Table XYZ doesn't exist)
    4. Set the variable pxc_strict_mode value to PERMISSIVE (To solve database table explicit primary key problem)

      Code Block
      titlemy.cnf
      languagexml
      lower_case_table_names=1
      sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
      set lower_case_table_names=1 #(default value '0')
      pxc_strict_mode=PERMISSIVE #(default value RESTRICTIVE OR MASTER)
  2. Save the changes and close the text editor
  3. Restart the mysqld Service 
  4. Create a new Database for ADSS Server
  5. Extract Fresh copy of ADSS Server and install it with newly created database. Remember to copy the mysql driver in core,console and service directories before running the installer 

Known issues when installing ADSS Server with MySQL

v5.0.x

  1. Invalid default value for 'CreatedAt'
    Cause & solution: Error is occurring because of sql_modes. Please check your current sql_modes by using this command:

    Code Block
    titleMySQL
    themeEclipse
    languagebash
    mysql> show variables like 'sql_mode';
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | Variable_name | Value |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.04 sec)
    

     

    1. Add the following variable lower_case_table_names and remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE"  in mysqld.conf/my.cnf to make it work:

      Code Block
      titlemy.cnf
      languagexml
      lower_case_table_names=1
      sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    2. Extract Fresh copy of ADSS Server and install it with new database. Remember to copy the mysql driver in core,console and service directories before running the installer 


  2. MySQLSyntaxErrorException: Table XYZ doesn't exist

    Cause & solution: The issue is that table names in MySQL are case sensitive and hibernate is upper casing them.

    1. Drop the database.

    2. Add the following variable to /etc/mysql/my.cnf:

      Code Block
      titlemy.cnf
      themeEclipse
      languagexml
      firstline1
      set lower_case_table_names=1 #(default value '0')
    3. Restart mysqld.

    4. Extract Fresh copy of ADSS Server and install it with new database. Remember to copy the mysql driver in core,console and service directories before running the installer 

  3. To solve database table explicit primary key problem, update /etc/mysql/my.cnf in all data nodes:

    Code Block
    themeEclipse
    languagesql
    firstline1
    pxc_strict_mode=PERMISSIVE #(default value RESTRICTIVE OR MASTER)
  4. Database meta information is not shown on ADSS Server Console and will be picked up in a later release. Last updated when ADSS Server v5.5 was released.

 

Known issue when installing ADSS Server with MySQL v8.0.x using MariaDB connector

Database connection error after installing ADSS Server with MySQL server v8.0.x using MariaDB connector in Linux RedHat

Error in Logs: 
java.sql.SQLException: Host 'c2devnro1is.csld.e-mip.eu' is not allowed to connect to this MySQL server
Caused by: java.sql.SQLException: Access denied for user 'user1'@'192.168.238.1' (using password: NO)
Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

Root Cause:
MySQL 8 uses caching_sha2_password rather than mysql_native_password as of MySQL 5.7 (and MariaDB) and it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. ADSS Server uses MariaDb connector to communicate/connect with MySQL database which has a limitation working with caching_sha2_password authentication plugin.

Solution: 
To fix this you need to switch to default mysql_native_password by following the below steps:

  1. Go to your MySQL query editor
  2. Connect the ADSS Server Database with Admin user (Root User in Linux)
  3. Run the below query:

    Code Block
    titleMySQL Query Editor
    languagesql
     ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
  4. Once the query ran successfully you may resume the installer
Warning

Please note: Before you attempt to connect to ADSS Server console make sure you have installed the default PFX file in your browser. This will allow you to access ADSS console over a TLS session

 

Special instructions for installing ADSS Server v6.2 and older versions with ORACLE 

If the Oracle database used for ADSS Server installation have multiple users then some times ADSS Server tries to connect with the first available database schema, follow these instruction to resolve this issue:

  1. Stop the ADSS Sever Core, Console and Service instances from the Windows Services Panel / UNIX Daemon. 
  2. Go to location: [ADSS-Server-Installation-Dir]\conf\
  3. Edit the hibernate.cfg.xml file and search for the property <property name="hibernate.connection.username">

  4. Place following property after the above one (Remeber to add the correct Database schema name):

    Code Block
    titlehibernate.cfg.xml
    languagetext
    <property name="hibernate.default_schema">SCHEMA_NAME</property> 
  5. Save the changes 
  6. Start the ADSS Sever Core, Console and Service instances from the Windows Services Panel / UNIX Daemon.

Configure ADSS Server with database SQL server running over TLS Authentication?

If you are running ADSS Server v5.6 or later then the following are the steps to configure the ADSS Server with database server over TLS Authentication:

If the ADSS Server Core, Console and Service components are running on separate machines then the changes have to be made on all the ADSS Server instances separately

  1. Generate a self-signed certificate with FQDN of the machine/server name in the CN and SAN extension of the TLS Server Authentication certificate (you can use ADSS Server to generate a self-signed certificate)
  2. Launch the ADSS Server Console and register the issuer CA of the database TLS Server Authentication Certificate in the Trust Manager with purpose CA for verifying SSL client certificates.
  3. Install the TLS Server Authentication key on database server and enable the TLS encryption of SQL Server by using SQL Server Configuration Manager utility. Follow the below link to enable the TLS encryption on SQL Server: https://support.microsoft.com/en-hk/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

  4. Download the Microsoft JDBC driver from link: https://www.microsoft.com/en-us/download/details.aspx?id=56615.

  5. Extract the downloaded JDBC driver
  6. Copy the mssql-jdbc-6.4.0.jre8.jar file from the driver directory at the following directories:

    1. [ADSS-Server-Installation-Dir]\core\server\webapps\core\WEB-INF\lib

    2. [ADSS-Server-Installation-Dir]\consoleconsole\server\webapps\console\WEB-INF\lib

    3. [ADSS-Server-Installation-Dir]\serviceservice\server\webapps\service\WEB-INF\lib

  7. Stop the ADSS Server Core, Console and Service instances from Windows services or Unix daemons
  8. Go to [ADSS-Server-Installation-Dir]/conf/ and take a backup of the hibernate.cfg.xml file
  9. Edit the hibernate.cfg.xml file in edit mode and update the hibernate.connection.driver_classhibernate.connection.url  and hibernate.hbm2ddl.auto elements as following:

    Code Block
    titlehibernate.cfg.xml
    themeEclipse
    languagexml
    <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
    <property name="hibernate.connection.url">jdbc:sqlserver://databaseServer:1433;databaseName=adss-db;encrypt=true;trustServerCertificate=true;</property>
    <property name="hibernate.hbm2ddl.auto">false</property>
  10. Start the ADSS Server Core, Console and Service instances from Windows services or Unix daemons so that the connection is established with the database server over TLS Authentication.

If you wish to configure the ADSS Server with SQL Server database using Windows Authentication option then additionally following these instructions:

  1. Copy the sqljdbc_auth.dll file from download package (sqljdbc_6.4\enu\auth\x64) of Microsoft JDBC driver and put the sqljdbc_auth.dll file at [ADSS-Server-Installation-Dir]\jdk\jre\bin change hibernate.cfg.xml file as following:

    Code Block
    titlehibernate.cfg.xml
    themeEclipse
    languagexml
    <property name="hibernate.connection.url">jdbc:sqlserver://databaseServer:1433;databaseName=adss-db;encrypt=true;trustServerCertificate=true;integratedSecurity=true;</property>
  2. Configure the ADSS Server Services in Windows Services Panel to run under a domain user
  3. Start the ADSS Server Core, Console and Service instances from Windows Services Pane