{"id":471,"date":"2019-05-29T16:56:21","date_gmt":"2019-05-29T16:56:21","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=471"},"modified":"2019-05-29T17:17:07","modified_gmt":"2019-05-29T17:17:07","slug":"sqlcl-enabling-mcs-for-jdbc-thin-driver","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=471","title":{"rendered":"SQLcl \u2013 enabling MCS for JDBC thin driver"},"content":{"rendered":"

Let’s say you have configured TLS authentication for your database users using the Microsoft Certificate Store (MCS) on Windows clients.
\n\"\"<\/a><\/p>\n

For OCI based client this is pretty well documented and understood:<\/p>\n

sqlnet.ora<\/p>\n

\r\nSQLNET.AUTHENTICATION_SERVICES = (TCPS)\r\nWALLET_LOCATION = (SOURCE = (METHOD = MCS))\r\nSSL_SERVER_DN_MATCH = yes\r\n<\/pre>\n
tnsnames.ora<\/p>\n
\r\nDEV1_TLS =\r\n  (DESCRIPTION =\r\n    (ADDRESS = (PROTOCOL = TCPS)(HOST = ol7ora19dev1.spotonoracle.com)(PORT = 2484))\r\n    (CONNECT_DATA =\r\n      (SERVICE_NAME = DEV1.spotonoracle.com)\r\n    )\r\n    (SECURITY =\r\n      (SSL_SERVER_CERT_DN = "cn=dev1,ou=servers,ou=oracle,dc=spotonoracle,dc=com")\r\n    )\r\n  )\r\n<\/pre>\n
With above configuration I can connect to the database using SQL*Plus.<\/p>\n
\r\nC:\\Users\\user1>sqlplus \/@dev1_tls\r\n\r\nSQL*Plus: Release 12.2.0.1.0 Production on Wed May 29 18:31:53 2019\r\n\r\nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\r\n\r\nLast Successful login time: Wed May 29 2019 18:10:15 +02:00\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\n<\/pre>\n
Note that I use a 12.2 OCI client. This is because of bug 25809524 for which the fix has not made its way into the 18c release. I hope the soon to be released 19c client for Windows will have the fix.<\/i><\/p>\n
In a previous post<\/a> I’ve shown how to use a file based wallet (cwallet.sso) for JDBC thin applications,namely SQLcl. Unfortunately, we cannot simply change the WALLET_LOCATION parameter as we do in sqlnet.ora. This does not work<\/strong>:<\/p>\n
\r\nset JAVA_TOOL_OPTIONS=-Doracle.net.wallet_location=(SOURCE=(METHOD=MCS)) -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=true\r\n<\/pre>\n
In order for JDBC thin applications to access the Microsoft Certificate Store we have to set Windows platform specific settings for trust store and key store properties:<\/p>\n
\r\nset JAVA_TOOL_OPTIONS=-Djavax.net.ssl.trustStore=NONE -Djavax.net.ssl.trustStoreType=Windows-ROOT -Djavax.net.ssl.keyStore=NONE -Djavax.net.ssl.keyStoreType=Windows-MY -Doracle.net.authentication_services=(TCPS) -Doracle.net.ssl_server_dn_match=true\r\n\r\nC:\\Users\\user1>sql \/@dev1_tls\r\nPicked up JAVA_TOOL_OPTIONS: -Djavax.net.ssl.trustStore=NONE -Djavax.net.ssl.trustStoreType=Windows-ROOT -Djavax.net.ssl.keyStore=NONE -Djavax.net.ssl.keyStoreType=Windows-MY -Doracle.net.authentication_services=(TCPS,KERBEROS5) -Doracle.net.ssl_server_dn_match=true\r\n\r\nSQLcl: Release 19.1 Production on Wed May 29 18:45:21 2019\r\n\r\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\r\n\r\nLast Successful login time: Wed May 29 2019 18:45:22 +02:00\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.3.0.0.0\r\n<\/pre>\n
This works for SQLcl, SQL Developer, and any reasonable JDBC thin application. \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
Let’s say you have configured TLS authentication for your database users using the Microsoft Certificate Store (MCS) on Windows clients. For OCI based client this is pretty well documented and understood: sqlnet.ora SQLNET.AUTHENTICATION_SERVICES = (TCPS) WALLET_LOCATION = (SOURCE = (METHOD = MCS)) SSL_SERVER_DN_MATCH = yes tnsnames.ora DEV1_TLS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,6],"tags":[],"class_list":["post-471","post","type-post","status-publish","format-standard","hentry","category-security","category-sqldev"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=471"}],"version-history":[{"count":5,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/471\/revisions"}],"predecessor-version":[{"id":477,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/471\/revisions\/477"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=471"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=471"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}