Development Desktop to Oracle SSL/TLS (copied from TCS)

1 Install Oracle SQL Developer with version 4.x or Build 17.x

You should have some admin right to the folder below

Check you have right to modify the installation folder ( copy file over )

And modify the file C:\Program Files (x86)\sqldeveloper\sqldeveloper\bin\ sqldeveloper.conf

2 Oracle Server Information

Oracle Information	Dev	QA/UAT
Server	dbdev.csd.toronto.ca	dbqa.csd.toronto.ca
Port	1528	1528
Service Name	CSISDV	CSISQA
JDBC String	jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST= dbdev.csd.toronto.ca)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=CSISDV)))	jdbc:oracle:thin:@(description=(address=(protocol=tcps)(host=dbqa.csd.toronto.ca)(port=1528))(connect_data=(service_name=CSISQA)))
UserID, Password	Use your own userid and password Or shared userID : csisweb Password : get from SQL Admin	Use your own userid and password Or shared userID : csisweb Password : get from SQL Admin
Desktop Trust Store Name	cacerts.jks password : your own password clark: summer2020, replace	cacerts.jks password : your own password clark: summer2020, replace
Certificate Exatrction	for Unix echo -n \| openssl s_client –connect sun06.csd.toronto.ca:1528 \| sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > wsdev_db_server.crt for Windows copy from 3.2.3	echo -n \| openssl s_client -connect dbqa.csd.toronto.ca:1528 \| sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > wsqa_city_signed_db_server.crt

Table 1.0 Reference for Oracle SSL

3 Install Secure Certificates in Desktop

Create a Trust Store
Get Certificate
Add Certificate to Trust Store

* summer2020 is mockup password, you should use your own

3.1 Create New TrustStore in Windows

Run command prompt , go to folder C:\Program Files (x86)\sqldeveloper\sqldeveloper

Run command below

“C:\Program Files (x86)\sqldeveloper\jdk\jre\bin\keytool” -keystore cacerts.jks –genkey -alias client

Replace \keytool path if is installed at different location.

Fill the information as below or similar, the information you put there only relevant to you.

You will create a new Certification trust store : cacert.jks, with password protected

the sample below is from Solaris box, but are similar to Windows

Solaris

/usr/java/jre/bin/keytool –genkey -alias client -keystore cacerts.jks

-bash-5.0$ /usr/java/jre/bin/keytool -genkey -alias client -keystore cacert.jks

Enter keystore password:

Re-enter new password:

What is your first and last name?

[Unknown]: Your Name

What is the name of your organizational unit?

[Unknown]: tcs

What is the name of your organization?

[Unknown]: toronto

What is the name of your City or Locality?

[Unknown]: toronto

What is the name of your State or Province?

[Unknown]: on

What is the two-letter country code for this unit?

[Unknown]: ca

Is CN=Clark Gao, OU=tcs, O=toronto, L=toronto, ST=on, C=ca correct?

[no]: y

Enter key password for <client>

(RETURN if same as keystore password):

Re-enter new password:

They don't match. Try again

Enter key password for <client>

(RETURN if same as keystore password):

Warning:

The JKS keystore uses a proprietary format. It is recommended to migrate to PKCS12 which is an industry standard format using "keytool -importkeystore -srckeystore cacert.jks -destkeystore cacert.jks -deststoretype pkcs12".

-bash-5.0$ ls

Table 2.0 Sample Creating Trust Store

3.2 Extract Certificate

You can extract the security certificate from the Oracle Server or copy from this document ( at the time of documentation, the certification is the latest in this document)

3.2.1 Windows

Download OpenSSL

Run Openssl in folder

C:\Program Files\Git\usr\bin

openssl s_client -connect sun06.csd.toronto.ca:1528

3.2.2 Solaris

echo -n | openssl s_client -connect sun06.csd.toronto.ca:1528 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > wsdev_db_server.crt

3.2.3 Use Existing

copy the content in the certificate cell, including — lines, and save to a text file with file extension .crt

Environment

Certificate

WSDev

Development

QA/UAT

And copy the crt file to

C:\Program Files (x86)\sqldeveloper\sqldeveloper\

3.3 Add Certificate to TrustStore

Run Windows Command Prompt, cd to folder

Folder C:\Program Files (x86)\sqldeveloper\sqldeveloper\

Add

The command for add dev certificate

“C:\Program Files (x86)\sqldeveloper\jdk\jre\bin\keytool” -import -trustcacerts -keystore cacerts.jks -storepass summer2020 -noprompt -alias dbcertws -file wsdev_db_server.crt

To replace - alias and -file for QA

Verify

LIST

"C:\Program Files (x86)\sqldeveloper\jdk\jre\bin\keytool" -list -keystore cacerts.jks -storepass summer2020

C:\Program Files (x86)\sqldeveloper\sqldeveloper>"C:\Program Files (x86)\sqldeveloper\jdk\jre\bin\keytool" -list -keystore cacerts.jks -storepass summer2020

Keystore type: JKS

Keystore provider: SUN

…

client, 27-Oct-2020, PrivateKeyEntry,

Certificate fingerprint (SHA1): 71:C8:9A:A8:51:99:F8:B7:37:6E:7C:C7:4A:FD:96:12:D1:D0:B8:2C

dbcertwsqacitysigned, 10-Nov-2020, trustedCertEntry,

Certificate fingerprint (SHA1): AD:B5:8D:59:CD:AA:F4:CE:CB:B4:3C:B7:06:66:67:BD:4D:F6:9E:F9

dbcertws, 9-Nov-2020, trustedCertEntry,

Certificate fingerprint (SHA1): E0:B2:C7:D7:26:3D:8C:71:59:BF:9F:F8:D0:B2:D3:29:44:02:FF:27

Check if you have entries : dev : dbcertws, QA: dbcertwsqa

Optional below DELETE

DELETE

“C:\Program Files (x86)\sqldeveloper\jdk\jre\bin\keytool” -delete -trustcacerts -keystore cacerts.jks -storepass summer2020 -noprompt -alias dbcertws

4 Setup SQL Developer Client on Desktop

4.1 Modify SQL Developer Configuration

In your local desktop, edit file inside the folder SQL Developer installed

C:\Program Files (x86)\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

Append the following at the end of file, replace the password

AddVMOption -Djavax.net.ssl.trustStore=../cacerts.jks

AddVMOption -Djavax.net.ssl.trustStoreType=JKS

AddVMOption -Djavax.net.ssl.trustStorePassword=summer2020

4.2 Create SQL Developer with JDBS

Open SQL Developer

Create a new JDBC with following

Click Connection Type as Advanced , copy the line below,

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST= dbdev.csd.toronto.ca)(PORT=1528))(CONNECT_DATA=(SERVICE_NAME=CSISDV)))

4.3 Test new JDBC

Click Test, Connect, then Save. You are ready to use SSL JDBS with your development

5 Setup QA Environment SQL Developer SSL Access

Use the same steps by different information to setup QA, refer to Table 1

Verify the access

Browser not supported