/
SQL Developer to Oracle by SSL/TLS

SQL Developer to Oracle by SSL/TLS

Owned by Vincent Li (Unlicensed)
Mar 18, 2022

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

-----BEGIN CERTIFICATE-----
MIICuDCCAiGgAwIBAgIJAMyj6FVzJ5GeMA0GCSqGSIb3DQEBCwUAMIGKMQswCQYD
VQQGEwJDQTEQMA4GA1UECBMHT250YXJpbzEYMBYGA1UEChMPQ2l0eSBvZiBUb3Jv
bnRvMRswGQYDVQQLExJDaGlsZHJlbnMgU2VydmljZXMxEjAQBgNVBAMTCVRDU1Jv
b3RDQTEeMBwGCSqGSIb3DQEJARYPYWhvbkB0b3JvbnRvLmNhMB4XDTIwMTEwMzE5
MTY1MloXDTMwMTEwMTE5MTY1MlowEDEOMAwGA1UEAxMFc3VuMDYwggEiMA0GCSqG
SIb3DQEBAQUAA4IBDwAwggEKAoIBAQCwl4K/LGhbGtUd0mQRqso31bbrDleA/mVw
1CkmYIclZPqMdwpidJVp+2MF3oySpqGkBfFhtLtNwNCVRt0hGd3F8c/Q8+CXfnkR
cdOz2ZGC7BoYOhjrWdtW0mXi5TkxBy37O1gRFagQFleb2VlRnfm+Hlmlts8RaIxv
E/29YCU23822Ng6n8xq0t5maxNssXItlXMJMsLhWH8Wk6Y4/BnpGO46FWeyuwOb/
rnL907/5cpZH0zVxuk1clN/epDNpKyOVHMN0I8SFKXA5VH4lpDePmMghPf5lX0jq
pHdQY7ewXPK6IFDEL0TU6UpRRH8YPWQNgILMoDSePMXxmNVNIq0pAgMBAAGjGzAZ
MBcGA1UdEQQQMA6CBXN1bjA2ggVzdW4wNjANBgkqhkiG9w0BAQsFAAOBgQB6n9IA
vvO4KW4mNFZlVVA6PqLGSDqymCeRL/p1pplFlEg039eq/21g4O87GDOkp6sVxgkN
Qd2jojJ7JgxMNJ3hi0boD/54ZX9a6b+Uv8s6gdC9SKXhbWbIC1c3YCSZGC+nRQc2
4SXa+oqno7TbyHrzZnOMcR+N5W4xMyn1ppNoMQ==
-----END CERTIFICATE-----

QA/UAT

-----BEGIN CERTIFICATE-----
MIIC6jCCAlOgAwIBAgIJAMyj6FVzJ5GfMA0GCSqGSIb3DQEBCwUAMIGKMQswCQYD
VQQGEwJDQTEQMA4GA1UECBMHT250YXJpbzEYMBYGA1UEChMPQ2l0eSBvZiBUb3Jv
bnRvMRswGQYDVQQLExJDaGlsZHJlbnMgU2VydmljZXMxEjAQBgNVBAMTCVRDU1Jv
b3RDQTEeMBwGCSqGSIb3DQEJARYPYWhvbkB0b3JvbnRvLmNhMB4XDTIwMTEwOTE5
MjcwMVoXDTMwMTEwNzE5MjcwMVowHjEcMBoGA1UEAxMTZGJxYS5jc2QudG9yb250
by5jYTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKrkzwJuPpaRuGRA
RcYSvWi+yqcrZS6zccRpVVA1xahPsZLwZuei1XA6fIx9CHWwZHhI3sj32yi2G9jH
UU7NbthD1MdEuRUP+rLO/lZ1tEHzngV56+jSekszJfkxJIgJz0Nn5hx7rfk9XT3k
iz722TU+UC45VhgDjyPnPg/x8E3ue64mQsOYMJq+zb2p+TnbP/kIDe4Gx+bz8jlv
iFGwljn0QPSY6oYDnpsc8OScY2DWfB/2lepNgZ+F9QFFSY2tsfPRCdGIvkKnsavX
fZ6+8rDzXMi6TUJ7WbokYtCWyAGGN3i8RqKmA1We+DlKncXVQxqJqaFUFdAvrXTQ
2aj1+mECAwEAAaM/MD0wOwYDVR0RBDQwMoIXTk9UVkFMSUQuY3NkLnRvcm9udG8u
Y2GCF05PVFZBTElELmNzZC50b3JvbnRvLmNhMA0GCSqGSIb3DQEBCwUAA4GBAHsY
xtcR7KrekYg2BeIN55KYcj9Kj/VqhlmKYLT07/htolU2Uj3vFo8PqFeAiToqPTFF
icf5PXHKeaNODZ9Mp6oHKvKN8kDcdoAgFGcnqtseteaLujVhl8K/Rr5MWeMkC6GA
0l1nGnzslkeUIqG6TIpEPR1T4z3pSwE4eCVsgWrx
-----END CERTIFICATE-----


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