# 使用MSSQL的AlwaysEncrypt 從程式端建立CMK、CEK 來加密資料庫欄位

**Step1:**
先使用java 的 keytool.exe來建立憑證
[參考網站1](https://blog.csdn.net/w47_csdn/article/details/87564029)
[參考網站2](https://www.tpisoftware.com/tpu/articleDetails/2247)
在JDK 1.4以後的版本都有這個keytool.exe的工具，位置在"%JAVA_HOME%\bin\keytool.exe"

**產生自簽憑證存放於jks中**
keytool -genkeypair -keyalg RSA -alias AlwaysEncryptedKey -keystore keystore.jks -storepass 123456 -validity 7200 -keysize 2048 -storetype jks


然後就會看到產生檔案

---

**Step2:**
執行程式AlwaysEncrypted建立cmk、cek
- 修改設定檔
- 執行程式建立CMK
- 執行程式建立CEK

key.alias=AlwaysEncryptedKey
cmk.name=CSSFCB_CMK_CAMIOL_1
cek.name=CSSFCB_CEK_CAMIOL_1
cek.secret=hyweb_bss_bakong_camiol_joyce_tw
keystore.location=D:\\Camiol\\bakong\\keystore.jks
keyStore.secret=123456
algorithm=RSA_OAEP
conn.url=jdbc:sqlserver://10.10.10.144:1433;databaseName=CSSFCB;user=css_owner;password=css_owner;
conn.attributes=sendStringParametersAsUnicode=false;columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionJavaKeyStoreProvider;
import com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionKeyStoreProvider;
import com.microsoft.sqlserver.jdbc.SQLServerConnection;
import com.microsoft.sqlserver.jdbc.SQLServerException;

/**
 * This program demonstrates how to create a column encryption key programmatically for the Java Key Store.
 */
public class AlwaysEncrypted {
    
    private static Properties props = new Properties();
    
    static {//目前目錄
        try (InputStream inputStream = new FileInputStream("config.properties")) {
            props.load(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // Alias of the key stored in the keystore.
    //private static String keyAlias = "AlwaysEncryptedKey";
    private static String keyAlias = props.getProperty("key.alias");

    // Name by which the column master key will be known in the database.
    private static String columnMasterKeyName = props.getProperty("cmk.name");

    // Name by which the column encryption key will be known in the database.
    private static String columnEncryptionKey = props.getProperty("cek.name");

    // The location of the keystore.
    private static String keyStoreLocation = props.getProperty("keystore.location");
    
    
    // The password of the keystore and the key.
    private static char[] keyStoreSecret = props.getProperty("keyStore.secret").toCharArray();

    private static String ckeSecret = props.getProperty("cek.secret");
    
    /**
     * Name of the encryption algorithm used to encrypt the value of the column encryption key. The algorithm for the system providers must be RSA_OAEP.
     */
    private static String algorithm = props.getProperty("algorithm");//"RSA_OAEP";

    //private static String connectionUrl = "jdbc:sqlserver://10.10.10.144:1433;databaseName=CSSFCB;user=css_owner;password=css_owner;sendStringParametersAsUnicode=false;columnEncryptionSetting=Enabled;";
    //private static String connectionUrl = "jdbc:sqlserver://10.10.10.144:1433;databaseName=CSSFCB;user=css_owner;password=css_owner;sendStringParametersAsUnicode=false;"
    //+ "columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;keyStoreLocation="+keyStoreLocation+";" + "keyStoreSecret=123456;";
    private static String connectionUrl = String.format(
            "%s;keyStoreLocation=%s;keyStoreSecret=%s;%s", 
            props.getProperty("conn.url"), 
            keyStoreLocation, 
            props.getProperty("keyStore.secret"), 
            props.getProperty("conn.attributes"));

    public static void main(String[] args) {
        //System.out.println(System.getProperty("java.library.path"));
        //createCMK();
        createCEK();
        //insdata();
        //selectEncTest();//jdbc使用javakeystore方式只能以程式方式去處理
        //selectEncryptedTest();//jdbc使用windowstorecertificate只適用win平台，只需匯入憑證(到Ppersonal)且columnEncryptionSetting=Enabled就可以work
    }
    private static void selectEncryptedTest() {

        String filterRecord = "SELECT AcctNo, Remark, BirthDate FROM " + "BkAcctKycENCRYPTED" + " where AcctNo = ? ";

        try (SQLServerConnection connection = (SQLServerConnection) DriverManager.getConnection(connectionUrl);
                PreparedStatement selectStatement = connection.prepareStatement(filterRecord);
                //PreparedStatement selectStatement = connection.prepareStatement(filterRecord, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
                //        connection.getHoldability(), SQLServerStatementColumnEncryptionSetting.ResultSetOnly);
                ) {
            selectStatement.setString(1, "918050101281");
            ResultSet rs = selectStatement.executeQuery();
            while (rs.next()) {
                System.out.println("AcctNo: " + rs.getString("AcctNo"));
                System.out.println("Remark: " + rs.getString("Remark"));
                System.out.println("BirthDate: " + rs.getString("BirthDate"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private static void selectEncTest() {

        String filterRecord = "SELECT AcctNo, Remark, BirthDate FROM " + "BkAcctKycEnc2" + " where AcctNo = ? ";

        try (SQLServerConnection connection = (SQLServerConnection) DriverManager.getConnection(connectionUrl);
                PreparedStatement selectStatement = connection.prepareStatement(filterRecord);
                //PreparedStatement selectStatement = connection.prepareStatement(filterRecord, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
                //        connection.getHoldability(), SQLServerStatementColumnEncryptionSetting.ResultSetOnly);
                ) {
            selectStatement.setString(1, "918050101281");
            ResultSet rs = selectStatement.executeQuery();
            while (rs.next()) {
                System.out.println("AcctNo: " + rs.getString("AcctNo"));
                System.out.println("Remark: " + rs.getString("Remark"));
                System.out.println("BirthDate: " + rs.getString("BirthDate"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private static void insdata() {
        String insSQL = "INSERT INTO BkAcctKycEnc2 (AcctNo,AcctType,KycLevel,CustomerId,Name,BirthDate,PersonId,QuizComplete,Remark,Status,UpdateDate,UpdateTime,Employee,MgrId,MgrId2,RejectReason,AcctCcy) VALUES (?,?,?,null,null,null,null,?,?,?,?,?,null,null,null,null,null)";
        
        try (Connection connection = DriverManager.getConnection(connectionUrl); PreparedStatement pstatement = connection.prepareStatement(insSQL);) {

            pstatement.setString(1, "918050101281");
            pstatement.setString(2, "01");
            pstatement.setString(3, "1");
            pstatement.setString(4, "1");            
            pstatement.setString(5, "Full KYC - USD Acct");
            pstatement.setString(6, "2");            
            pstatement.setString(7, "20230107");            
            pstatement.setString(8, "100000");            
            
            pstatement.executeUpdate();
            System.out.println("insSQL finish");  
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

    }
    private static void createCMK() {

        try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) {
            //String createCMKSQL = "CREATE COLUMN MASTER KEY " + columnMasterKeyName +" WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_JAVA_KEYSTORE', KEY_PATH = N'AlwaysEncryptedKey')";
            String cmksql = String.format("CREATE COLUMN MASTER KEY %s WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_JAVA_KEYSTORE', KEY_PATH = N'AlwaysEncryptedKey')",columnMasterKeyName);
            System.out.println(cmksql);
            statement.executeUpdate(cmksql);
            System.out.println("Column MASTER key created with name : "+columnMasterKeyName);            

        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private static void createCEK() {
        try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) {

            // Instantiate the Java Key Store provider.
            SQLServerColumnEncryptionKeyStoreProvider storeProvider = new SQLServerColumnEncryptionJavaKeyStoreProvider(keyStoreLocation, keyStoreSecret);

            byte[] encryptedCEK = getEncryptedCEK(storeProvider);

            /**
             * Create column encryption key For more details on the syntax, see: https://learn.microsoft.com/sql/t-sql/statements/create-column-encryption-key-transact-sql
             * Encrypted column encryption key first needs to be converted into varbinary_literal from bytes, for which byteArrayToHex() is used.
             */
            //String createCEKSQL = "CREATE COLUMN ENCRYPTION KEY " + columnEncryptionKey + " WITH VALUES ( " + " COLUMN_MASTER_KEY = " + columnMasterKeyName
            //        + " , ALGORITHM =  '" + algorithm + "' , ENCRYPTED_VALUE =  0x" + byteArrayToHex(encryptedCEK) + " ) ";
            String ceksql = String.format("CREATE COLUMN ENCRYPTION KEY %s WITH VALUES ( COLUMN_MASTER_KEY = %s  , ALGORITHM =  '%s' , ENCRYPTED_VALUE = %s )", columnEncryptionKey,columnMasterKeyName,algorithm,"0x"+byteArrayToHex(encryptedCEK));
            System.out.println(ceksql);
            statement.executeUpdate(ceksql);
            System.out.println("Column encryption key created with name : " + columnEncryptionKey);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static byte[] getEncryptedCEK(SQLServerColumnEncryptionKeyStoreProvider storeProvider) throws SQLServerException {
        //String plainTextKey = "samson_a_rich_man_you_can_trust.";//eg.9C27B632A53439D657EF75D17A198159

        // plainTextKey has to be 32 bytes with current algorithm supported
        //byte[] plainCEK = plainTextKey.getBytes();
        byte[] plainCEK = ckeSecret.getBytes();

        // This will give us encrypted column encryption key in bytes
        byte[] encryptedCEK = storeProvider.encryptColumnEncryptionKey(keyAlias, algorithm, plainCEK);

        return encryptedCEK;
    }

    public static String byteArrayToHex(byte[] a) {
        StringBuilder sb = new StringBuilder(a.length * 2);
        for (byte b : a)
            sb.append(String.format("%02x", b).toUpperCase());
        return sb.toString();
    }
}

依序執行cmk 和 cek 後
就會在MSSQL上面看到產生的金鑰


---

**Step3:** 新建Table包含加密欄位使用指定cek
(修改下面SQL中的CSSFCB_CEK_CAMIOL_1)

CREATE TABLE "dbo"."BkAcctKycEnc5"
(
   AcctNo varchar(20) COLLATE Chinese_Taiwan_Stroke_BIN2  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CSSFCB_CEK_CAMIOL_1], ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL PRIMARY KEY ,
   AcctType varchar(2),
   KycLevel char(1) DEFAULT ('2') NOT NULL,
   CustomerId varchar(25),
   Name varchar(128),
   BirthDate varchar(8),
   PersonId varchar(25),
   QuizComplete char(1) DEFAULT ('0') NOT NULL,
   Remark nvarchar(256),
   Status char(1) DEFAULT ('0') NOT NULL,
   UpdateDate varchar(8),
   UpdateTime varchar(8),
   Employee varchar(50),
   MgrId varchar(50),
   MgrId2 varchar(20),
   RejectReason nvarchar(300),
   AcctCcy varchar(3)
)
GO

然後在url上面加上鑰匙位置和密碼

jdbc:sqlserver://10.10.10.144:1433;databaseName=CSSFCB;keyStoreLocation=D:\\Camiol\\bakong\\keystore.jks;keyStoreSecret=123456;sendStringParametersAsUnicode=false;columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;

然後只能透過程式裡面使用prepare statement的方式才能存取加密過後的table , JPA只要是用@Query方式用nativeQuery=true的都是prepare statement的方式


測試新增和查詢都成功

**注意事項：**
如果用JPA，在NVARCHAR的欄位上面要加上annotation: @Nationalized
這樣AlwaysEncrypt才會抓到該欄位是要用nvarchar的型態去轉換，才能insert data


然後如果拿加密欄位來當查詢條件時，該欄位不能是null, 不然會報錯，所以要擋掉參數是null的狀況。