Configuring Windchill with Oracle Database to Use JDBC over SSL
|
|
ATTENTION: This topic refers to the Windchill Server Host as the “client” and the Oracle Database Host on which the Oracle Server is running is referred to as the “server”. In the context of database interactions, the Windchill server acts as a client to the database server.
|
The high-level steps for configuring the Oracle 12C Database Server for encrypted communication with its client (the Windchill server) using Microsoft JDBC Driver for Oracle Database Server is as follows:
1. Create Certificate and Exchange it at Client and Server Side
2. Configure Database to Use TCPS with the Server Key
3. Configure JDBC over SSL
The subsequent sections describe the procedure for each of these steps.
Windchill 11.1 F000 release and later support this feature along with Oracle 12C R1 and 12C R2 databases.
Oracle Wallets created by Oracle Wallet Manager or orapki use the standard PKCS12 format to store X.509 certificates and private keys. The wallet is stored in a file named
ewallet.p12. In the example given below, you will use existing
Windchill Java Keystore at the client side. At the server side, you will use orapki to create wallets, as Oracle provides SSL support for Oracle Wallet. The OraclePKI command is stored in the
$ORACLE_HOME/bin folder. For more information on Oracle wallets, see
https://docs.oracle.com/middleware/1213/wls/JDBCA/oraclewallet.htm#JDBCA598Creating Certificate and Exchanging it at Client and Server Side
On Oracle 12C R2 database server, a wallet can be created at any directory location. However, Oracle recommends to create a wallet in the ORACLE_HOME database environment. Ensure that you have the required Read permission for wallet directory on which Oracle service is running. A wallet can be created using SUDO user on Unix platform or Administrative user on Windows platform.
Perform the following steps to create two wallets, a server wallet and a client wallet, with self-signed certificates.
1. Create a directory to store all the wallets. This wallet directory can be created anywhere. Oracle recommends to create the wallet in:
> mkdir wallets
> cd wallets
For example, %ORACLE_HOME%\wallets
2. Create a wallet for the Oracle server. Create an empty wallet with auto-login enabled:
> orapki wallet create -wallet ./server_wallet -auto_login -pwd server01
For example,
orapki wallet create -wallet %ORACLE_HOME%\wallets\server_wallet
-auto_login -pwd server01
Two files are created under the server_wallet directory:
server_wallet/cwallet.sso
server_wallet/ewallet.p12
3. Add a self-signed certificate in the wallet. You will find a new pair of private or public keys is created:
> orapki wallet add -wallet ./server_wallet -dn “CN=server”
-keysize 1024 -self_signed -validity 365 -pwd server01
a. View the server_wallet directory:
> orapki wallet display -wallet ./server_wallet
...
Requested Certificates:
Subject: CN=server
...
b. Export the server certificate:
> orapki wallet export –wallet ./server_wallet -dn “CN=server”
-cert ./server_wallet/cert.txt
Copy the cert.txt file on the Windchill client side.
4. Import server certificate to client keystore. For the client, use Java Keystore to store the server certificate:
> keytool -importcert -alias <ALIAS-NAME> -keystore <PATH-TO-KEYSTORE>
-file <FILE-PATH-TO-SERVER-CERTIFICATE>
For example,
keytool -importcert -alias oraclessl
-keystore JAVA_HOME\jre\lib\security\cacerts
-file JAVA_HOME\bin\cert.txt
The parameters used are:
◦ <ALIAS-NAME> - Alias name used to store the server certificate file in keystore.
◦ <PATH-TO-KEYSTORE> - Keystore location to import the server certificate. If you want to use the default Java Keystore of Windchill, it can be located at {wt.jdk}/jre/lib/security location with name as cacerts. The wt.jdkvalue can be found in the WT_HOME/codebase/wt.properties file.
◦ <FILE-PATH-TO-SERVER-CERTIFICATE> - Directory location where the server certificate is stored. In this example, it is cert.txt file.
5. Create a self-signed client certificate in the client keystore:
For example,
keytool -genkeypair -alias ClientCerts -keylag RSA -validity 365
-keysize 2048 -keystore JAVA_HOME\jre\lib\security\cacerts
The system will ask you to enter the keystore password. The default keystore password is: changeit.
The parameters used are:
◦ <ALIAS-OF-YOUR-CLIENT-SSL-CERT>- Alias of certificate with which you want to create a self-signed certificate.
◦ <PATH-TO-KEYSTORE> - Keystore location to import the server certificate.
6. Export the client certificate to the file which can be imported from the server_wallet directory:
For example,
keytool -export -alias CLientCerts
-keystore JAVA_HOME\jre\lib\security\cacerts
-rfc -file JAVA_HOME\bin\ClientCertificate.txt
Copy ClientCertificate.txt file on the Oracle DB server.
The parameters used are:
◦ <ALIAS-OF-YOUR-CLIENT-SSL-CERT> - Alias of certificate which you want to export to the file and can be used to import from the server wallet to authenticate client identity.
◦ <PATH-TO-KEYSTORE> - Keystore location to import the server certificate.
◦ <PATH-TO-OUTPUT-FILE> - File location for exporting the client key.
7. Import the client certificate into the server wallet.
> orapki wallet add –wallet ./server_wallet -trusted_cert
-cert ./client_wallet/cert.txt -pwd server01
For example,
orapki wallet add -wallet Oracle_Home\wallets\server_wallet
-trusted_cert -cert Oracle_Home\wallets\client_wallet\ClientCertificate.txt
-pwd server01
The command client_wallet/cert.txt refers to the exported client certificate.
|
|
If you need client authentication, you must import the client certificate on the server wallet. If you only need encryption and server authentication, this step can be skipped.
|
8. View the results for server wallet and client keystore. Ensure that you have an entry for the client key on the server wallet and also an entry for the server key on the client keystore:
◦ Under User Certificates:
Subject: CN=server
◦ Under Trusted Certificates:
Subject: CN=sam,OU=ptc,O=isg,L=pune,ST=mh,C=in
◦ Under Client Keystore Output:
Owner: CN=server
Issuer: CN=server
◦ Under Certificate[1]:
Owner: CN=sam, OU=ptc, O=isg, L=pune, ST=mh, C=in
Issuer: CN=sam, OU=ptc, O=isg, L=pune, ST=mh, C=in
Server Wallet Output -
> orapki wallet display -wallet .
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=server
Trusted Certificates:
Subject: CN=client
Subject: CN=VAGRANT
Subject: CN=VAGRANT_CLIENT
Subject: CN=server
Subject: CN=sam,OU=ptc,O=isg,L=pune,ST=mh,C=in
Client Keystore Output –
> keytool -list -v -keystore selfsigned3.jks
Keystore type: JKS
Keystore provider: SUN
Your keystore contains 3 entries
.
.
.
*******************************************
*******************************************
Alias name: stepde-cert
Creation date: 24 Aug, 2017
Entry type: trustedCertEntry
Owner: CN=server
Issuer: CN=server
Serial number: 0
Valid from: Wed Jun 28 15:17:33 IST 2017 until: Thu Jun 28 15:17:33 IST 2018
Certificate fingerprints:
MD5: F3:46:9C:70:74:93:75:EA:08:F6:35:D9:EC:A5:20:F0
SHA1: 5D:43:23:4F:FA:C8:5B:48:C5:61:A5:CB:02:66:E7:CD:F4:47:4E:95
SHA256: BE:26:3F:88:E8:DD:F2:57:F3:D4:5F:4C:70:4A:0F:D2:99:10:D5:
27:18:BA:29:E5:00:AD:00:41:78:1E:CE:92
Signature algorithm name: MD5withRSA
Version: 1
*******************************************
*******************************************
Alias name: smandaokar2l_selfsigned3
Creation date: 24 Aug, 2017
Entry type: PrivateKeyEntry
Certificate chain length: 1
Certificate[1]:
Owner: CN=sam, OU=ptc, O=isg, L=pune, ST=mh, C=in
Issuer: CN=sam, OU=ptc, O=isg, L=pune, ST=mh, C=in
Serial number: dccd23b
Valid from: Thu Aug 24 18:49:15 IST 2017 until: Fri Aug 24 18:49:15 IST 2018
Certificate fingerprints:
MD5: 5A:39:55:EA:29:0C:46:C7:86:92:DF:98:4B:C9:E5:93
SHA1: 9D:75:AE:C8:F0:EE:B9:E1:9B:E5:98:10:47:7F:3D:CE:C9:D9:47:18
SHA256: F0:78:81:AA:C4:AC:17:1C:3B:10:87:9B:A7:DF:CE:0F:BE:A0:B6:42:08:90:D9:
BC:59:23:91:BA:03:02:44:1D
Signature algorithm name: SHA256withRSA
Version: 3
Extensions:
#1: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: D3 CF 0B 8B 41 B6 63 13 B8 D3 59 57 DB E2 75 7F ....A.c...YW..u.
0010: C5 50 DE 88 .P..
]
]
*******************************************
*******************************************
Configuring Database to Use TCPS with the Server Key
Perform the following steps on the Oracle Database server, to configure the network to activate SSL using Oracle Network Manager.
Configure the listener.ora file:
1. Launch the Net Manager tool. The netmgrcommand is available if ORACLE_HOME is set as environment variable. In the command prompt or terminal, enter the following command to open network manager.
> netmgr
2. Navigate to > > .
3. Click Add Address and select TCP/IP with SSL and enter the values for protocol, host name and port as follows:
Protocol: TCP/IP with SSL
Host Name: vv-stepde-vm
Port: 2484
|
|
For Windchill 11.1 F000 release, Windchill supports JDBCs communication on dedicated TCPS port 2484 configured on Oracle Database server. Cognos and Solr will not support JDBCs. All JDBC communication will be managed on the existing default TCP port 1521 which comes as default Windchill installation. The next step about listener.ora configuration shows how Oracle can serve both JDBC and JDBCs on two different ports
|
4. Click >
Here is an example of the listener.ora file that you created.
...
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2)))
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vv-stepde-vm
)(PORT = 1521)))
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = vv-stepde-vm)(PORT = 2484)))
)
...
◦ If you need client authentication, ensure that the SSL_CLIENT_AUTHENTICATION property is set to TRUE in the listener.ora file:
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:\wallets\server_wallet)))
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = PROTOCOL = IPC) (KEY = EXTPROC1)))
(DESCRIPTION =
(ADDRESS = PROTOCOL = TCP) (HOST = vv-stepde-vm)(PORT = 1521)))
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCPS)(HOST = vv-stepde-vm)(PORT = 2484))))
5. Configure the sqlnet.ora file. Click Profile and select Oracle Advanced Security from the dropdown list and then, on the SSL tab, click Server if you do not have an SSL form. If you have an SSL form, ensure that the wallet directories of SSL form are pointing to the server_wallet directory.
Here is an example of a complete SSL form.
Configure Method: File System
Wallet Directory: C:\wallets\server_wallet
Configure SSL for: Server
Revocation Check: None
Require SSL Version: Any
Require Client Authentication: <Leave unchecked>
◦ If client authentication is required, ensure that the Require Client Authentication checkbox is selected in the SSL form. After saving this network configuration, verify that the authentication parameter is set to TRUE in the sqlnet.ora file:
SSL_VERSION = 0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:\wallets\server_wallet)
)
)
|
|
If you only want encryption and server authentication, this step can be skipped.
|
6. Click >
7. Exit the Net Manager tool. However, ensure that you have saved the changes and restarted the listener to pick up the new changes:
> lsnrctl stop
> lsnrctl start
8. Launch the Net Manager tool.
Here is an example of the sqlnet.ora and listener.ora files that you created without client authentication. If you need client authentication, ensure that the SSL_CLIENT_AUTHENTICATION property is set to TRUE in the listener.ora file:
◦ sqlnet.ora
...
SQLNET.AUTHENTICATION_SERVICES=(TCPS,NTS)
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA = (DIRECTORY = C:\wallets\server_wallet))
)
SSL_CIPHER_SUITES= (SSL_RSA_WITH_AES_128_CBC_SHA,
SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)
...
◦ listener.ora
...
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA = (DIRECTORY = C:\wallets\server_wallet))
)
...
9. Configure the tnsnames.ora file. In the Net Manager tool, click Service Naming.
10. To create a new service, click Edit and then click Create. Complete the Net Service Name Wizard using the following information:
Net Service Name: vv-stepde-vm_tcps
Select: TCP/IP with SSL (Secure Internet Protocal)
Host Name: vv-stepde-vm
Port Number: 2484
(Oracle8i or later) Service Name: orcl
Connection Type: Default database
Test the connection on page 5 of the wizard
Here is an example of the tnsnames.ora file that you created:
...
vv-stepde-vm_tcps =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = vv-stepde-vm)(PORT = 2484)))
(CONNECT_DATA = (SERVICE_NAME = orcl))
)
...
11. Click > .
12. Click > . The configuration is now complete.
Configuring JDBC over SSL
Perform the following steps to configure JDBC over SSL:
1. Define the following properties in the Windchill server db.properties file:
wt.pom.dbConnectionPropertiesNameList=javax.net.ssl.trustStore,
javax.net.ssl.trustStoreType,javax.net.ssl.trustStorePassword,
oracle.net.ssl_cipher_suites
wt.pom.dbConnectionPropertiesValueList=C:\\jks\\selfsigned3.jks,
JKS,client01,(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA,
SSL_DH_anon_WITH_RC4_128_MD5,SSL_DH_anon_WITH_DES_CBC_SHA)
|
|
If SSL cipher suites are in place, we must specify the 'oracle.net.ssl_cipher_suites' property and its values. If SSL cipher suites are not in place, we do not need this property and its value.
|
◦ If you need client authentication, provide keystore properties instead of truststore properties:
wt.pom.dbConnectionPropertiesNameList=javax.net.ssl.keyStore,
javax.net.ssl.keyStoreType,javax.net.ssl.keyStorePassword,
oracle.net.ssl_cipher_suites
wt.pom.dbConnectionPropertiesValueList= C:\\jks\\selfsigned3.jks,
JKS,client01,(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA,
SSL_DH_anon_WITH_RC4_128_MD5,SSL_DH_anon_WITH_DES_CBC_SHA)
2. Add the new property given below in the Windchill server db.properties file, which determines whether JDBC should be activated over SSL.
wt.pom.jdbc.protocol=TCPS
|
|
If the property is not specified, Windchill will serve the regular JDBC connections. However, if TCPS value is specified for the property, the corresponding port number must be specified in the wt.pom.jdbc.port property.
|
After completing the configuration steps, restart Windchill. The new setup will enable communication between Windchill and Oracle database server using JDBC over SSL.
For more information on configuring a database client connection, see the following documentation from Oracle:
