[Azure][SQL]Azure SQL Database 搭配 JDBC 與稽核的錯誤處理
這幾天在測試將應用系統搬上 Azure SQL Database，原本以為只是的簡單任務，沒有想到居然一堆奇怪的狀況導致，一開始是因為開發人員沒有注意到，在 Azure SQL Database 的環境下，如果一個連線 Idle 超過 30 分鐘，則 Azure SQL Database 會強制斷線。一般來說 JDBC 的使用環境下，通常都會搭配 DBCP or C3P0 的 Connection Pool，而就 DBCP 為例，為了避免放在 Pool 中的連線因為 idle 或其他原因被斷線，因此可以透過設定 validationQuery 和 testOnBorrow 的參數，讓每次分配連線之前做個驗證，確認連線是可以正常使用之後，才進行配發的動作。所以只是在設定檔內做一些簡單的微調，就順利將 Java 的應用系統移到 Azure 上面了。
放上去之後測試幾天，看起來都很順利，忽然之間系統又連不上去了，從 Log 中可以看到類似下面的錯誤訊息
實在很特別的錯誤訊息，看起來似乎是搭配 SSL 的加密處理出了問題，但原本好好的系統，應該不會平白無故發生異常，因此就開始追查是否有人有調整 Azure 相關設定。經過反覆確認和詢問，才發現原來管理 Azure 資料庫的人員，正在測試 Azure SQL Database 的「稽核」，導致連線失敗。正常來說開啟稽核應該對前端應用程式沒有影響才對，因此我搭配了幾種排列組合的方式，因此測試不同的 JDBC 的版本進行測試，發現一個有趣的狀況。
這個是我的測試程式，在 Server 上我開了兩個資料庫，一個有開稽核，一組沒有開稽核。
import java.sql.*;
public class Main {
public static void main(String[] args) {
new Main().testNormal ("mytest.database.windows.net","cloudworker2015@mytest","Pa$$w0rd","DBWithoutAudit");
new Main().testNormal ("mytest.database.windows.net","cloudworker2015@mytest","Pa$$w0rd","DBWithAudit");
new Main().testEncrypt("mytest.database.windows.net","cloudworker2015@mytest","Pa$$w0rd","DBWithoutAudit");
new Main().testEncrypt("mytest.database.windows.net","cloudworker2015@mytest","Pa$$w0rd","DBWithAudit");
}
/**
* 組成有設定加密連線的設定進行連線
*/
public boolean testEncrypt(String server, String user, String password, String database)
{
String url = String.format("jdbc:sqlserver://%s;database=%s;user=%s;password=%s",server,database,user,password).concat(";encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net") ;
return testConnection(url);
}
/**
* 使用一般連線
*/
public boolean testNormal(String server, String user, String password, String database)
{
// Declare the JDBC objects.
String url = String.format("jdbc:sqlserver://%s;database=%s;user=%s;password=%s",server,database,user,password);
return testConnection(url);
}
/**
* 按照連線狀態顯示訊息
* @param url
* @return
*/
private boolean testConnection(String url) {
System.out.println(url);
boolean result = testURL(url);
if (result)
{
System.out.println("Test JDBC OK");
}
else
{
System.out.println("Test JDBC ERROR");
}
System.out.println();
return result;
}
/**
* 測試連線是否正常
* @param url
* @return
*/
public boolean testURL(String connectionUrl)
{
// Declare the JDBC objects.
Connection con = null;
ResultSet rs = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
rs = con.createStatement().executeQuery("select @@version");
if (rs.next())
System.out.println(rs.getNString(1));
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
原本測試完覺得得到答案了，但後來又拿到另外一個之前在測試用的資料庫，發覺狀況完全不同，反覆查看兩邊的設定之後，發現原來 Azure SQL Database 的版本也有關聯，因此把這些整理一下，得到下方的表格。
上方是資料庫的狀況，右方指的是 JDBC 的版本和連線字串的設定。原本發覺上述的狀況有些特別，因此又仔細確認一下，在舊版本的 Azure SQL Database 上針對啟用「安全性存取」的設定，是可以有兩種選擇方式，在 V2 的環境下我沒有限制必要。
而從上表中看出，如果我要搭配 V12 的版本，則最好是選用 SQL Server JDBC 4.1 或 4.2 的版本，這要看你所使用的 Java 版本而定。如果您採用 Java 8 的版本，則建議使用 JDBC 4.2；如果是採用 Java 7 的產品，則建議搭配 JDBC 4.1 的版本。
而在目前的組合中，看起來似乎還是先單純的設定連線字串，類似以下的方式
jdbc:sqlserver://mytest.database.windows.net;database=Sample;user=cloudworker2015@mytest;password=Pa$$w0rd;
而先不要在後面加上其他的連線設定，否則你就要確定你不能開啟稽核的功能了。而 Azure SQL Database 的稽核有甚麼樣的功能呢 ? 當我們開啟之後，則 Azure 會將你所設定要稽核的事件，給存放到 Azure Storage 上的 Table
後面您就可以直接用 Excel 去抓資料表內的事件來進行分析，而如果您不知道該怎麼來分析，微軟也有提供相關的範本檔案，在範本檔案提入一些儲存體的連線資訊之後，就可以有一份完整的分析報告可以使用了。
因此如果可以的話，建議大家可以選擇合適的 JDBC Driver 和設定，並且開啟 Azure SQL Database 的稽核，應該會有不錯的效果。
PS. 這幾天經過反覆測試，找到一個可行的方式，將 Portal 上所建議的 JDBC 的連線字串做些調整，改成類似以下的方式 :
jdbc:sqlserver://mytest.database.secure.windows.net;database=Sample;user=xxxx@mytest;password=yyyyy;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.secure.windows.net
上面我們將原本的 database.windows.net 置換為 database.secure.windows.net，就可以正常使用了。