请求协助：用Python实现Azure AD与Snowflake的SSO自动化集成

前置依赖 #

• 已创建Azure AD应用，获取到client_id、client_secret、tenant_id，并为应用分配Directory.ReadWrite.All和Application.ReadWrite.OwnedBy权限（需管理员同意）
• Snowflake账户管理员权限，知晓账户URL（如https://xxx.snowflakecomputing.com）和账户名
• 安装Python依赖库：

pip install requests snowflake-connector-python

步骤1：通过Microsoft Graph API配置Azure AD侧SAML SSO #

以下代码会自动配置Azure AD应用的SAML SSO设置，适配Snowflake的要求：

import requests
import json

# Azure AD配置参数
tenant_id = "你的租户ID"
client_id = "你的应用ID"
client_secret = "你的应用密钥"
azure_app_object_id = "你的Azure AD应用对象ID"  # 可在Azure门户应用概述页获取

# Snowflake配置参数
snowflake_account_url = "https://xxx.snowflakecomputing.com"
snowflake_reply_url = f"{snowflake_account_url}/fed/login"

# 1. 获取Graph API访问令牌
token_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
token_payload = {
    "grant_type": "client_credentials",
    "client_id": client_id,
    "client_secret": client_secret,
    "scope": "https://graph.microsoft.com/.default"
}
token_response = requests.post(token_url, data=token_payload)
access_token = token_response.json()["access_token"]

# 2. 配置SAML SSO设置
graph_api_url = f"https://graph.microsoft.com/v1.0/applications/{azure_app_object_id}"
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}

saml_config = {
    "web": {
        "redirectUris": [snowflake_reply_url],
        "implicitGrantSettings": {
            "enableIdTokenIssuance": True
        }
    },
    "samlSingleSignOnSettings": {
        "serviceProviderEntityId": snowflake_account_url,
        "signingCertificate": "",  # 若需自定义证书可填写，否则使用Azure自动生成的
        "signingAlgorithm": "http://www.w3.org/2001/04/xmldsig-more#rsa-sha256",
        "issuerUri": f"https://sts.windows.net/{tenant_id}/",
        "singleSignOnServiceUrl": f"https://login.microsoftonline.com/{tenant_id}/saml2",
        "relayState": "",
        "logoutUrl": f"{snowflake_account_url}/fed/logout"
    }
}

response = requests.patch(graph_api_url, headers=headers, json=saml_config)
if response.status_code == 204:
    print("Azure AD SAML配置更新成功")
else:
    print(f"配置失败：{response.text}")

步骤2：自动化配置Snowflake侧安全集成 #

以下代码会在Snowflake中创建用于SSO的安全集成：

import snowflake.connector

# Snowflake连接参数
snowflake_account = "你的Snowflake账户名"
snowflake_user = "管理员用户名"
snowflake_password = "管理员密码"
snowflake_warehouse = "默认仓库"
snowflake_database = "默认数据库"

# Azure AD元数据URL（可从Azure应用的SAML配置页获取）
azure_ad_metadata_url = f"https://login.microsoftonline.com/{tenant_id}/federationmetadata/2007-06/federationmetadata.xml"

# 连接Snowflake
conn = snowflake.connector.connect(
    account=snowflake_account,
    user=snowflake_user,
    password=snowflake_password,
    warehouse=snowflake_warehouse,
    database=snowflake_database
)
cursor = conn.cursor()

# 创建安全集成
create_integration_sql = f"""
CREATE OR REPLACE SECURITY INTEGRATION AZURE_AD_SSO
TYPE = SAML2
ENABLED = TRUE
SAML2_ISSUER = 'https://sts.windows.net/{tenant_id}/'
SAML2_SSO_URL = 'https://login.microsoftonline.com/{tenant_id}/saml2'
SAML2_PROVIDER = AZUREAD
SAML2_X509_CERT = (SELECT PUBLIC_KEY FROM TABLE(GET_SAML_IDP_CERTIFICATES('{azure_ad_metadata_url}')))
SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'Azure AD SSO'
SAML2_FORCE_AUTHN = TRUE
SAML2_POST_LOGOUT_REDIRECT_URL = '{snowflake_account_url}'
"""

try:
    cursor.execute(create_integration_sql)
    print("Snowflake安全集成创建成功")
finally:
    cursor.close()
    conn.close()

步骤3：验证配置 #

1. 从Snowflake获取SP元数据，确认与Azure AD配置匹配：

DESC SECURITY INTEGRATION AZURE_AD_SSO;

2. 创建测试用户并关联Azure AD身份：

CREATE USER test_user
LOGIN_NAME = 'test.user@yourdomain.com'
DISPLAY_NAME = 'Test User'
DEFAULT_ROLE = PUBLIC
SAML2_IDENTITY_PROVIDER = AZURE_AD_SSO;

3. 尝试通过Azure AD登录Snowflake，确认流程正常

内容的提问来源于stack exchange，提问作者Ravindra