问题描述
在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
