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

原创

lulight 2022-09-01 23:01:19 ©著作权

文章标签 Azure Developer sql ide 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者lulight的原创作品，请联系作者获取转载授权，否则将追究法律责任

问题描述

在Azure Spring Cloud中，通过ActiveDirectoryMSI方式来连接到SQL Service，需要如何配置呢？

问题分析

在SQL Service中启用Active Directory MSI认证方式，需要执行两个步骤：

1）在Auzre Spring Cloud App中分配一个Managed Identity。

【Azure Spring Cloud】Azure Spring Cloud connect to SQL using MSI_Azure Developer

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 = newds.setServerName("aad-managed-demo.database.chinacloudapi.cn"); // Replace with your server name       // 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;
=================================================================================================================