Configuring secure access to SQL Server databases in federated environments

Edit online

Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-01-20 10:38

Draft comment:
This topic was viewed 89 times since its publication

Secure access to SQL Server databases by enabling Secure Sockets Layer (SSL) communication between Process Federation Server and the database server.

Before you begin

Ensure that the following conditions are met:

SSL is enabled on Process Federation Server.
SSL is enabled on the SQL Server database and an SSL certificate is available.
Ensure that in the properties.microsoft.sqlserver file on Process Federation Server, the Force Encryption option is set to yes, and that the file contains the settings for the SSL certificate and the truststore for the SQL Server database.

Procedure

Import the SQL Server SSL certificate into the Process Federation Server truststore.

On Process Federation Server, run the following command:

keytool -import -keystore path_to_keystore 
        -storepass your_password -keypass your_password 
        -alias custom_alias -file certificate.cer


Enter keystore password:
Owner: OU=Org_Unit, O=Org, C=Country
Issuer: OU=Org_Unit, O=Org, C=Country

   ...

Trust this certificate? [no]:  yes
Certificate was added to keystore

Verify that the certificate is available on the server by running the following command:
```
keytool -list -v -keystore path_to_keystore; -storepass keystore_password
```
Enable SSL communication between Process Federation Server and SQL Server databases.
Add the SQL Server SSL port number, certificate, truststore, and encryption settings that are specified in the properties.microsoft.sqlserver file for the SQL Server driver to the server.xml file:
1. Open the server.xml configuration file for editing.
  By default, the configuration file is in the pfs_install_root/usr/servers/server_name directory on Process Federation Server.
2. Update the attributes for the properties.microsoft.sqlserver property with the information from the properties.microsoft.sqlserver file.
  The following code snippet is an example of a configured properties.microsoft.sqlserver property for an SQL Server JDBC driver. The attributes that are relevant for SSL configuration are highlighted:
```
<dataSource
   ...
   ... 
   <properties.microsoft.sqlserver serverName="localhost" encrypt="true"
       databaseName="BPMDB" instanceName="MSSQLSERVER"
       password="password" trustStorePassword="password" 
       hostNameInCertificate="mssql.mycompany.com" 
       trustStore="pfs_install_root/usr/servers/server_name/resources/security/sqltrust.p12"
       portNumber="SQL_SSL_port" 
       ...
       ... 
    </properties.microsoft.sqlserver>
</dataSource>
```

Results

SSL communication between Process Federation Server and SQL Server databases is enabled.