【Azure Spring Cloud】Azure Spring Cloud connect to SQL using MSI

問題描述

在Azure Spring Cloud中，通過ActiveDirectoryMSI方式來連接配接到SQL Service，需要如何配置呢？

問題分析

在SQL Service中啟用Active Directory MSI認證方式，需要執行兩個步驟：

1）在Auzre Spring Cloud App中配置設定一個Managed Identity。

2）在SQL Service中，使用CREATE USER 指令建立一個Contained User，并且與第一步中的Managed Identity關聯。

• 建立映射到 Azure AD 辨別的包含的使用者：https://docs.microsoft.com/zh-cn/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell&view=azuresql#create-contained-users-mapped-to-azure-ad-identities
• CREATE USER [<Azure_AD_principal_name>] FROM EXTERNAL PROVIDER;

完成以上配置後，使用JDBC + ActiveDirectoryMSI的示例代碼為：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class AAD_MSI {
    public static void main(String[] args) throws Exception {

        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName("aad-managed-demo.database.chinacloudapi.cn"); // Replace with your server name
        ds.setDatabaseName("demo"); // Replace with your database name
        ds.setAuthentication("ActiveDirectoryMSI");
        // Optional
        ds.setMSIClientId("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"); // Replace with Client ID of User-Assigned Managed Identity to be used

        try (Connection connection = ds.getConnection();
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()")) {
            if (rs.next()) {
                System.out.println("You have successfully logged on as: " + rs.getString(1));
            }
        }
    }
}      

或者使用SQLServerDataSource.setURL()來設定SQL的連接配接字元串

SQLServerDataSource.setURL(): Sets the URL that is used to connect to the data source.      

=================================================================================================================
jdbc:sqlserver://aad-managed-demo.database.chinacloudapi.cn:1433;
database=hsp-sql-database1-dev;
authentication=ActiveDirectoryMSI;
msiClientId=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;
encrypt=true;
trustServerCertificate=false;
hostNameInCertificate=*.database.chinacloudapi.cn;
loginTimeout=3000;
=================================================================================================================      

參考資料

建立映射到 Azure AD 辨別的包含的使用者: https://docs.microsoft.com/zh-cn/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell&view=azuresql#create-contained-users-mapped-to-azure-ad-identities

Connect using ActiveDirectoryMSI authentication mode：https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15#connect-using-activedirectorymsi-authentication-mode

setURL Method (SQLServerDataSource)：https://docs.microsoft.com/en-us/sql/connect/jdbc/reference/seturl-method-sqlserverdatasource?view=sql-server-ver15

當在複雜的環境中面臨問題，格物之道需：濁而靜之徐清，安以動之徐生。 雲中，恰是如此!