Configuring secure database access

This topic describes how to configure a secure connection, such as Secure Socket Layer (SSL), from the OpenText Application Quality Management server to the database server. If your database server requires an encrypted channel, you must follow these instructions.

Before you start

Before beginning, determine the following:

Database	Considerations
SQL	Is the certificate signed by a trusted Certificate Authority (CA)? If not, obtain the certificate chain of authority that issued your SQL server certificate and import it into the ALM server truststore using the procedure to configure trust on the ALM server in Configure secure access on Windows systems or Configure secure access on Linux systems. Is host name validation required? If yes, what is the host name, including the domain name, in the server certificate?
Oracle	If the database is SSL configured: Place the Oracle Wallet file in a location on the ALM server where the ALM Service user has read permissions. Is host name validation required? If yes, what is the host name, including the domain name, in the server certificate? Is the port different than what it was before? If the database is not SSL configured: Is native Data Integrity configured? Is native Encryption configured? If yes, what is the algorithm? Is the key larger than 128 bits?

Database

Considerations

SQL

Is the certificate signed by a trusted Certificate Authority (CA)? If not, obtain the certificate chain of authority that issued your SQL server certificate and import it into the ALM server truststore using the procedure to configure trust on the ALM server in Configure secure access on Windows systems or Configure secure access on Linux systems.
Is host name validation required? If yes, what is the host name, including the domain name, in the server certificate?

Oracle

If the database is SSL configured:

Place the Oracle Wallet file in a location on the ALM server where the ALM Service user has read permissions.
Is host name validation required? If yes, what is the host name, including the domain name, in the server certificate?
Is the port different than what it was before?

If the database is not SSL configured:

Is native Data Integrity configured?
Is native Encryption configured? If yes, what is the algorithm? Is the key larger than 128 bits?

Configure a secure connection for a previously unsecured database.

To configure a secure database connection for a previously unsecured database:

For SQL databases, follow the procedure to configure trust on the ALM server in Configure secure access on Windows systems or Configure secure access on Linux systems.

Configure the Site Administration schema connection.

This section is relevant if the database server that was configured for a secure connection contains your Site Administration schema. If you have a separate database server for your projects and you only want a secure connection to that database, skip this section.

OS Steps

Windows

OS	Steps
Windows	Stop the server. Run the Server Configuration wizard: Win > Run > “%ALM_INSTALL_PATH%\run_configuration.bat” gui false In the Database Server step, enter the database administrator password and click Next. Select the Connection String option under Database Connection: For SSL, add ;encrypt=true to the end of the value. For example: jdbc:sqlserver://localhost:1433;databaseName=DBNAME;integratedSecurity=true;encrypt=true;trustServerCertificate=true For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value. For example: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=servicename)));javax.net.ssl.trustStore=C:\path\ewallet.p12;javax.net.ssl.trustStorePassword=password;javax.net.ssl.trustStoreType=PKCS12 Alternatively, you can import the certificate as a Java keystore (.jks) file into the Java cacerts store. For details about how to enable providers in java security files, see the Oracle JDBC driver documentation. For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in `..\java\jre\lib\security\`. For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in `..\java\jre\lib\security\`. Note: For details on java security policy files, see the Oracle documentation. Click Next. In the Site Administration Database Schema step: Select Connect to existing schema/ second node under Selected Action. Enter your Site Administration schema name and password. Continue until the end of the wizard and start the ALM Service.
Linux	Stop the OpenText Application Quality Management server. Edit the qcConfigFile.properties file located in the deployment folder. Value SaDbAction with connectToExisting SaDbAction=connectToExisting Edit the line with dbConnectionString: For SSL, add ;encrypt=true to the end of the value. For example: jdbc:sqlserver://localhost:1433;databaseName=DBNAME;integratedSecurity=true;encrypt=true;trustServerCertificate=true For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value. For example: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=servicename)));javax.net.ssl.trustStore=/path/ewa llet.p12;javax.net.ssl.trustStorePassword=password;javax.net.ssl.trustStoreType=PKCS12 Alternatively, you can import the certificate as a Java keystore (.jks) file into the Java cacerts store. For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in `../java/jre/lib/security/`. For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in `../java/jre/lib/security/`. Note: For details on java security policy files, see the Oracle documentation. Run the Server Configuration wizard from the ALM installation folder: ./run_configuration.sh Wait until the server is reconfigured and start the ALM Service.

Stop the server.

Run the Server Configuration wizard:

Win > Run > “%ALM_INSTALL_PATH%\run_configuration.bat” gui false

In the Database Server step, enter the database administrator password and click Next.
Select the Connection String option under Database Connection:
- For SSL, add ;encrypt=true to the end of the value. For example:
```
jdbc:sqlserver://localhost:1433;databaseName=DBNAME;integratedSecurity=true;encrypt=true;trustServerCertificate=true
```
- For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value. For example:
```
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=servicename)));javax.net.ssl.trustStore=C:\path\ewallet.p12;javax.net.ssl.trustStorePassword=password;javax.net.ssl.trustStoreType=PKCS12
```
  Alternatively, you can import the certificate as a Java keystore (.jks) file into the Java cacerts store.
  
  For details about how to enable providers in java security files, see the Oracle JDBC driver documentation.
- For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in ..\java\jre\lib\security\.
- For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in ..\java\jre\lib\security\.
  
  Note: For details on java security policy files, see the Oracle documentation.
Click Next. In the Site Administration Database Schema step:
1. Select Connect to existing schema/ second node under Selected Action.
2. Enter your Site Administration schema name and password.
Continue until the end of the wizard and start the ALM Service.

Linux

Stop the OpenText Application Quality Management server.
Edit the qcConfigFile.properties file located in the deployment folder.
1. Value SaDbAction with connectToExisting
```
SaDbAction=connectToExisting
```
2. Edit the line with dbConnectionString:
  - For SSL, add ;encrypt=true to the end of the value. For example:
```
jdbc:sqlserver://localhost:1433;databaseName=DBNAME;integratedSecurity=true;encrypt=true;trustServerCertificate=true
```
  - For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value. For example:
```
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=servicename)));javax.net.ssl.trustStore=/path/ewa llet.p12;javax.net.ssl.trustStorePassword=password;javax.net.ssl.trustStoreType=PKCS12
```
    Alternatively, you can import the certificate as a Java keystore (.jks) file into the Java cacerts store.
  - For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in ../java/jre/lib/security/.
  - For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in ../java/jre/lib/security/.
    
    Note: For details on java security policy files, see the Oracle documentation.
Run the Server Configuration wizard from the ALM installation folder:
```
./run_configuration.sh
```
Wait until the server is reconfigured and start the ALM Service.

Configure the database servers:
1. Log in to Site Administration.
2. In the Database Servers tab, do the following for each database that was configured for a secure connection:
  1. Select the database and click Edit.
  2. Change the connection string:
    - For SSL, add ;encrypt=true to the end of the value.
    - For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value.
    - For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in ..\java\jre\lib\security\.
    - For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in ..\java\jre\lib\security\.
      
      Note: For details on java security policy files, see the Oracle documentation.
  3. Click Test Connection to check that the connection works.
  4. Click OK.
Configure LAB_PROJECT, if LAB_PROJECT is on a secure connection database:
1. Log in to Site Administration.
2. Go to the Site Projects tab, select LAB_PROJECT, and click Edit :
  1. Click OK for any error messages that appear.
  2. The Connection String Editor (MS-SQL/Oracle) dialog box opens. Change the connection string:
    - For SSL, add ;encrypt=true to the end of the value.
    - For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value.
    - For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in ..\java\jre\lib\security\.
    - For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in ..\java\jre\lib\security\.
      
      Note: For details on java security policy files, see the Oracle documentation.
  3. Click Test Connection to check that the connection works.
  4. Click OK.
  5. Click Activate Project.
Configure all site projects on a secure connection database:
1. Log in to Site Administration.
2. Go to the Site Projects tab, select the project and click Edit:
  1. Click OK for any error messages that appear.
  2. The Connection String Editor (MS-SQL/Oracle) dialog box opens. Change the connection string:
    - For SSL, add ;encrypt=true to the end of the value.
    - For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value.
    - For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in ..\java\jre\lib\security\.
    - For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in ..\java\jre\lib\security\.
      
      Note: For details on java security policy files, see the Oracle documentation.
  3. Click Test Connection to check that the connection works.
  4. Click OK.
  5. Click Activate Project.
3. Perform the above step for all projects on a secure connection database.
  
  If you have a large number of projects to update, you can run the following SQL update query on the site administration schema:
  1. In MS SQL Server: UPDATE td.PROJECTS SET DB_CONNSTR_FORMAT = 'your new connection string'
  2. In Oracle: UPDATE [your sa schema name].PROJECTS SET DB_CONNSTR_FORMAT = 'your new connection string'
  3. To limit the projects you update, add a where clause to the query, such as WHERE PROJECT_NAME IN ('project1', 'project2') or WHERE DOMAIN_NAME IN ('damain1', 'domain2')
  4. After executing the query, restart the ALM service.

Configure a secure database connection for a new installation

You can configure a secure database connection for a new installation as follows:

For SQL databases, follow the procedure to configure trust on the ALM server in Configure secure access on Windows systems or Configure secure access on Linux systems.
During the installing, in the Database Server step, select the Connection String option and value the field as follows:
- For MS SQL server use this format: jdbc:sqlserver://;serverName:1433;encrypt=true;.
  
  If TLSv1.2 is required use this format: jdbc:sqlserver://;serverName:1433;encrypt=true; sslProtocol=TLSv1.2;.
- For Oracle, add ;javax.net.ssl.trustStore=[path to Oracle Wallet];javax.net.ssl.trustStorePassword=[password to Oracle wallet] to the end of the value.
  
  For details, see the Oracle JDBC driver documentation.
- For Oracle native Data Integrity, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and replace the java security policy files in ..\java\jre\lib\security\.
- For Oracle native Encryption, add ;oracle.net.crypto_checksum_client =ACCEPTED or ;oracle.net.crypto_checksum_client =REQUIRED to the end of the value, and, for encryption algorithms with keys longer than 128 bits, replace the java security policy files in ..\java\jre\lib\security\.
  
  Note: For details on java security policy files, see the Oracle documentation.
Complete the installation.