使用 SSL 加密的 JDBC 连接 SAP HANA 数据库
近期客户为满足安全要求，提了让业务应用使用 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 establish
Connection has closed
