近期客戶為滿足安全要求，提了讓業務應用使用 SSL 方式連線 SAP HANA 資料庫的需求。本人查詢 SAP官方文件 發現資料庫支援 SSL 連線，有引數直接加到 JDBC 的 URL 後邊就行了，為了便於驗證寫了個 JDBC 的 demo，在此處記錄一二。
準備工作
JdbcTest.java
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcTest {
public static Connection getConnection() throws FileNotFoundException, IOException {
Connection conn = null;
Properties prop = new Properties();
try {
InputStream in = JdbcTest.class.getResourceAsStream("jdbc.properties");
prop.load(in);
Class.forName(prop.getProperty("jdbcDriver"));
//@formatter:off
conn = (Connection) DriverManager.getConnection(
prop.getProperty("jdbcUrl"),
prop.getProperty("username"),
prop.getProperty("password"));
//@formatter:on
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void releaseConnection(Connection conn) {
try {
if (conn != null) {
conn.close();
System.out.println("Connection has closed");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws FileNotFoundException, IOException {
Connection conn = getConnection();
if (conn != null)
System.out.println("Connection has establish");
releaseConnection(conn);
}
}
jdbc.properties
jdbcDriver=com.sap.db.jdbc.Driver
jdbcUrl=jdbc:sap://10.2.42.15:30015/?databaseName=Q15&encrypt=true&trustStore=/deployments/SSL/jssecacerts&trustStorePassword=changeit
username=test
password=ABCabc123
InstallCert.java
/*
* Copyright 2006 Sun Microsystems, Inc. All Rights Reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* - Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* - Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
*
* - Neither the name of Sun Microsystems nor the names of its
* contributors may be used to endorse or promote products derived
* from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
* IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
* THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR
* CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
* PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
* LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
* NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/**
* Originally from:
* http://blogs.sun.com/andreas/resource/InstallCert.java
* Use:
* java InstallCert hostname
* Example:
*% java InstallCert ecc.fedora.redhat.com
*/
import javax.net.ssl.*;
import java.io.*;
import java.security.KeyStore;
import java.security.MessageDigest;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
/**
* Class used to add the server's certificate to the KeyStore
* with your trusted certificates.
*/
public class InstallCert {
public static void main(String[] args) throws Exception {
String host;
int port;
char[] passphrase;
if ((args.length == 1) || (args.length == 2)) {
String[] c = args[0].split(":");
host = c[0];
port = (c.length == 1) ? 443 : Integer.parseInt(c[1]);
String p = (args.length == 1) ? "changeit" : args[1];
passphrase = p.toCharArray();
} else {
System.out.println("Usage: java InstallCert <host>[:port] [passphrase]");
return;
}
File file = new File("jssecacerts");
if (file.isFile() == false) {
char SEP = File.separatorChar;
File dir = new File(System.getProperty("java.home") + SEP
+ "lib" + SEP + "security");
file = new File(dir, "jssecacerts");
if (file.isFile() == false) {
file = new File(dir, "cacerts");
}
}
System.out.println("Loading KeyStore " + file + "...");
InputStream in = new FileInputStream(file);
KeyStore ks = KeyStore.getInstance(KeyStore.getDefaultType());
ks.load(in, passphrase);
in.close();
SSLContext context = SSLContext.getInstance("TLS");
TrustManagerFactory tmf =
TrustManagerFactory.getInstance(TrustManagerFactory.getDefaultAlgorithm());
tmf.init(ks);
X509TrustManager defaultTrustManager = (X509TrustManager) tmf.getTrustManagers()[0];
SavingTrustManager tm = new SavingTrustManager(defaultTrustManager);
context.init(null, new TrustManager[]{tm}, null);
SSLSocketFactory factory = context.getSocketFactory();
System.out.println("Opening connection to " + host + ":" + port + "...");
SSLSocket socket = (SSLSocket) factory.createSocket(host, port);
socket.setSoTimeout(10000);
try {
System.out.println("Starting SSL handshake...");
socket.startHandshake();
socket.close();
System.out.println();
System.out.println("No errors, certificate is already trusted");
} catch (SSLException e) {
System.out.println();
e.printStackTrace(System.out);
}
X509Certificate[] chain = tm.chain;
if (chain == null) {
System.out.println("Could not obtain server certificate chain");
return;
}
BufferedReader reader =
new BufferedReader(new InputStreamReader(System.in));
System.out.println();
System.out.println("Server sent " + chain.length + " certificate(s):");
System.out.println();
MessageDigest sha1 = MessageDigest.getInstance("SHA1");
MessageDigest md5 = MessageDigest.getInstance("MD5");
for (int i = 0; i < chain.length; i++) {
X509Certificate cert = chain[i];
System.out.println
(" " + (i + 1) + " Subject " + cert.getSubjectDN());
System.out.println(" Issuer " + cert.getIssuerDN());
sha1.update(cert.getEncoded());
System.out.println(" sha1 " + toHexString(sha1.digest()));
md5.update(cert.getEncoded());
System.out.println(" md5 " + toHexString(md5.digest()));
System.out.println();
}
System.out.println("Enter certificate to add to trusted keystore or 'q' to quit: [1]");
String line = reader.readLine().trim();
int k;
try {
k = (line.length() == 0) ? 0 : Integer.parseInt(line) - 1;
} catch (NumberFormatException e) {
System.out.println("KeyStore not changed");
return;
}
X509Certificate cert = chain[k];
String alias = host + "-" + (k + 1);
ks.setCertificateEntry(alias, cert);
OutputStream out = new FileOutputStream("jssecacerts");
ks.store(out, passphrase);
out.close();
System.out.println();
System.out.println(cert);
System.out.println();
System.out.println
("Added certificate to keystore 'jssecacerts' using alias '"
+ alias + "'");
}
private static final char[] HEXDIGITS = "0123456789abcdef".toCharArray();
private static String toHexString(byte[] bytes) {
StringBuilder sb = new StringBuilder(bytes.length * 3);
for (int b : bytes) {
b &= 0xff;
sb.append(HEXDIGITS[b >> 4]);
sb.append(HEXDIGITS[b & 15]);
sb.append(' ');
}
return sb.toString();
}
private static class SavingTrustManager implements X509TrustManager {
private final X509TrustManager tm;
private X509Certificate[] chain;
SavingTrustManager(X509TrustManager tm) {
this.tm = tm;
}
public X509Certificate[] getAcceptedIssuers() {
/**
* This change has been done due to the following resolution advised for Java 1.7+
http://infposs.blogspot.kr/2013/06/installcert-and-java-7.html
**/
return new X509Certificate[0];
//throw new UnsupportedOperationException();
}
public void checkClientTrusted(X509Certificate[] chain, String authType)
throws CertificateException {
throw new UnsupportedOperationException();
}
public void checkServerTrusted(X509Certificate[] chain, String authType)
throws CertificateException {
this.chain = chain;
tm.checkServerTrusted(chain, authType);
}
}
}
將這三個檔案和 ngdbc-2.4.70.jar 上傳到伺服器目錄 /deployments/SSL，執行編譯命令：
cd /deployments/SSL
javac JdbcTest.java
javac InstallCert.java
此時目錄中會有三個class檔案（有個內部類）：
$ ls
InstallCert.class InstallCert$SavingTrustManager.class JdbcTest.class ngdbc-2.4.70.jar
InstallCert.java jdbc.properties JdbcTest.java
匯入證照
執行 java InstallCert <域名 或 IP>[:埠號]，根據提示輸入 1 匯入證照，公鑰證照檔案是從資料庫端返回的，會被工具類轉換成 Java 程式能識別的證照格式（JKS），輸出證照到當前目錄下的 jssecacerts，後邊要做的就是在 JDBC 連線地址上新增這個證照的絕對位置，使用證照加密連線資料庫。
SAP 技術支援人員提供的證照有問題，hostname 與 證照裡簽得不一樣，導致他們給的 cer 證照匯入系統後報錯如下：
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target引起這個報錯的原因不大好定位，使用
InstallCert.java匯入證照時會很清晰地提示證照有問題，而且也不需要額外上傳 cer 證照 再執行 keytool 匯入證照命令。
驗證連線
修改 jdbc.properties，執行 java -cp ".:ngdbc-2.4.70.jar" JdbcTest ，當出現如下輸出而不是報錯即連線測試通過。
$ java JdbcTest
Connection has closed
Connection has establish