SQL Developer to Oracle by SSL/TLS
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 | ||
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----- |
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
