配置 PolyBase 以访问 S3 兼容的对象存储中的外部数据

适用于: SQL Server 2022 (16.x)

本文介绍如何使用 PolyBase 查询与 S3 兼容的对象存储中的外部数据。

SQL Server 2022 (16.x) 引入了连接到任何 S3 兼容对象存储的功能,有两个可用于身份验证的选项:基本身份验证或直通授权(也称为 STS 授权)。

基本身份验证(也称为静态凭据)要求用户将 access key idsecret key id 存储在 SQL Server 中,由用户根据需要显式撤销和轮换凭据。 精细粒度访问控制要求管理员为每个登录设置静态凭据,此方法在处理数十个或数百个唯一凭据时可能具有挑战性。

直通 (STS) 授权通过使用 SQL Server 自己的用户标识来访问与 S3 兼容的对象存储,从而为这些问题提供解决方案。 S3 兼容对象存储能够使用 Secure Token Service (STS) 来分配临时凭证。 这些凭据是短期的,并且是动态生成的。

本文包括基本身份验证和直通授权 (STS) 授权的说明。

先决条件

要使用 S3 兼容对象存储集成功能,需要以下工具和资源:

权限

为了让代理用户读取 S3 桶的内容,需要允许用户 (Access Key ID) 对 S3 端点执行以下操作:

  • 需要 GetBucketLocationGetObject 权限才能从 S3 对象存储读取特定文件。
    • 指向 S3 文件夹位置(而不是单个文件)的外部表或 OPENROWSET 查询需要 ListBucket。 如果没有 ListBucket 权限,将收到错误 Msg 4860, Level 16, State 7, Line 15 Cannot bulk load. The file "s3://<ip address>:9000/bucket/*.*" does not exist or you don't have file access rights.
  • 需要 PutObject 权限才能写入 S3 对象存储。

提示

符合 S3 的对象存储提供程序可能需要额外的 API 操作权限,或者对包含 API 操作权限的角色使用不同的命名。 请查阅产品文档。

启用 PolyBase

  1. sp_configure 中启用 PolyBase:

    EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
    GO
    RECONFIGURE
    GO
    
  2. 确认设置:

    EXEC sp_configure @configname = 'polybase enabled';
    

身份验证

若要继续,请选择“基本身份验证”“直通 (STS) 授权”

基本身份验证

创建数据库范围凭据之前,用户数据库必须具有用于保护凭据的主密钥。 有关详细信息,请参阅 CREATE MASTER KEY

使用基本身份验证创建数据库范围的凭据

以下示例脚本在 SQL Server 实例的 database_name 数据库中创建了一个数据库范围的凭证 s3-dc。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

USE [database_name];
GO
IF NOT EXISTS(SELECT * FROM sys.database_scoped_credentials WHERE name = 's3_dc')
BEGIN
 CREATE DATABASE SCOPED CREDENTIAL s3_dc
 WITH IDENTITY = 'S3 Access Key',
 SECRET = '<AccessKeyID>:<SecretKeyID>' ;
END
GO

使用 sys.database_scoped_credentials (Transact-SQL) 验证新的数据库范围的凭据:

SELECT * FROM sys.database_scoped_credentials;

使用基本身份验证创建外部数据源

以下示例脚本在 SQL Server 的源用户数据库中创建外部数据源 s3_ds。 外部数据源引用 s3_dc 数据库范围的凭据。 有关详细信息,请参阅 CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO

使用 sys.external_data_sources 验证新的外部数据源。

SELECT * FROM sys.external_data_sources;

虚拟托管 URL

某些与 S3 兼容的存储系统(如 Amazon Web Services)利用 virtual_hosted 样式 URL 在 S3 存储桶中实现文件夹结构。 添加以下 CONNECTION_OPTIONS 以允许创建指向 S3 存储桶中的文件夹位置的外部表,例如 CONNECTION_OPTIONS = '{"s3":{"url_style":"virtual_hosted"}}'

如果没有该 CONNECTION_OPTIONS 设置,则查询指向文件夹的外部表时,可能会看到以下错误:

Msg 13807, Level 16, State 1, Line 23  
Content of directory on path '/<folder_name>/' cannot be listed. 

基本身份验证的限制

  • 对于 S3 兼容的对象存储,不允许客户创建包含 : 字符的访问密钥 ID。
  • URL 总长度限制为 259 个字符。 这意味着 s3://<hostname>/<objectkey> 不应超过 259 个字符。 s3:// 计数包含在此限制内,因此路径长度不能超过 259-5 = 254 个字符。
  • SQL 凭据名称的限制为 128 个 UTF-16 格式的字符。
  • 创建的凭据名称必须包含桶名称,除非此凭据用于新的外部数据源。
  • 访问密钥 ID 和密钥 ID 只能包含字母数字值。

直通 (STS) 授权

与 S3 兼容的对象存储能够使用 Secure Token Service (STS) 来分配临时凭证。 这些凭据是短期的,并且是动态生成的。

直通授权依赖于 Active Directory 联合身份验证服务 (ADFS) 充当 OpenID Connect (OIDC) 标识提供者,由 ADFS 与 S3 兼容的对象存储 STS 通信,请求 STS 并将其提供给SQL Server。

在 SQL Server 上使用直通 (STS) 授权

  1. 必须使用 SQL Server 与 S3 兼容的主机服务器之间的证书配置 TLS。 假设所有连接都通过 HTTPS 而不是 HTTP 进行安全传输。 终结点将由安装在 SQL Server OS 主机上的证书进行验证。 支持公共或自签名证书。

  2. 创建数据库范围的凭据,用于将标识传递给 S3 兼容对象存储。 有关详细信息,请参阅 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。 如以下示例所示:

    CREATE DATABASE SCOPED CREDENTIAL CredName
    WITH IDENTITY = 'User Identity'
    
  3. 创建外部数据源以访问与 S3 兼容的对象存储。 使用 CONNECTION_OPTIONS 作为 JSON 格式,告知 ADFS 和 STS 的所需信息。 有关详细信息,请参阅 CREATE EXTERNAL DATA SOURCE。 如以下示例所示:

    CREATE EXTERNAL DATA SOURCE EdsName
    WITH
    {
        LOCATION = 's3://<hostname>:<port>/<bucket_name>'
        , CREDENTIAL = <CredName>
        [ , CONNECTION_OPTIONS = ' {
            [ , "authorization": {
                    "adfs": {
                        "endpoint": "http[s]://hostname:port/servicepath",
                        "relying_party": "SQL Server Relying Party Identifier"
                    },
                    "sts": {
                        "endpoint": "http[s]://hostname:port/stspath",
                        "role_arn": "Role Arn"
                        [ , "role_session_name": "AD user login" ] -- default value if not provided
                        [ , "duration_seconds": 3600 ]             -- default value if not provided
                        [ , "version": "2011-06-15" ]              -- default value if not provided
                        [ , "request_parameters": "In request query string format" ]
                    }
                } ]
            [ , "s3": {
                "url_style": "Path"
                } ]
        }' ]
    }
    
  • ADFS 选项指定 ADFS 中 SQL Server 的 Windows 传输终结点和 relying_party 标识符。
  • STS 选项指定与 S3 兼容的对象存储 STS 终结点和 AssumeRoleWithWebIdentity 请求的参数。 AssumeRoleWithWebIdentity 是用于获取用于进行身份验证的临时安全凭据的方法。 有关完整的参数列表(包括可选参数)以及默认值的相关信息,请参阅 STS API 参考

将直通 (STS) 授权与 Active Directory 配合使用

  • 在 AD 中将 SQL Server 用户帐户属性标记为不敏感,以允许传递到与 S3 兼容的存储。
  • 对于与 SQL Server SPN(服务主体名称)相关的用户,允许对 ADFS 服务 进行 Kerberos 约束委派。

将直通 (STS) 授权与 Active Directory 联合身份验证服务配合使用

  • 使 SQL Server 成为 Active Directory 中的声明提供程序信任
  • 允许 Intranet Windows 身份验证作为 ADFS 的身份验证方法。
  • 在 Intranet 中启用 Windows 传输服务终结点。
  • 启用 OIDC (OpenID Connect) 终结点。
  • 将 SQL Server 注册为信赖方信任
    • 提供唯一标识符。
    • 为 JWT(JSON Web 令牌)设置声明规则。
  • 自定义声明 - 如果需要这些声明来确定存储端的访问策略,客户可以添加这些声明。
  • 有关更多特定于供应商的信息,请咨询 S3 兼容的平台提供商。

在 S3 兼容对象存储上使用直通 (STS) 授权

  • 按照 S3 兼容存储提供程序提供的文档设置外部 OIDC 标识提供者。 要设置标识提供者,通常需要以下值。

    • OIDC 提供程序的配置端点。
    • OIDC 提供程序的指纹。
    • 直通授权到 S3 兼容对象存储

直通 (STS) 授权的限制

  • 使用 Windows 身份验证的 SQL Server 登录名支持直通授权 (STS) 到 S3 兼容对象存储。
  • STS 令牌不能用于备份到 S3 兼容对象存储的 URL
  • ADFS 和 SQL Server 必须位于相同的域中。 应从 Extranet 禁用 ADFS Windows 传输终结点。
  • ADFS 应具有与作为声明信任提供程序的 SQL Server 相同的 AD (Active directory)。
  • S3 兼容存储应具有 STS 终结点服务,使客户端能够使用外部标识的 JWT 请求临时凭据。
  • parquet 和 CSV 格式支持 OPENROWSET 和 CETAS (Create External Table as Select) 查询。
  • 默认情况下,Kerberos 票证续订时间为 7 天,在 Windows 上的生存期为 10 小时,在 Linux 上的生存期为 2 小时。 SQL Server 最多可将用户的 Kerberos 令牌续订 7 天。 7 天后,用户的票证过期,因此直通到 S3 兼容存储将会失败。 在这种情况下,SQL Server 必须重新对用户进行身份验证才能获取新的 Kerberos 票证。
  • 支持将 ADFS 2019 与 Windows Server 2019 配合使用。
  • S3 REST API 调用会使用 AWS 签名版本 4。

Linux 上的 SQL Server 上的 PolyBase

对于 Linux 上的 SQL Server 上的 PolyBase,需要更多的配置。

  • 必须配置 TLS。 假设所有连接通过 HTTPS 而不是 HTTP 进行安全传输。 端点将由安装在 SQL Server OS 主机上的证书进行验证。
  • Linux 上的证书管理不同。 查看并遵循 S3 兼容的存储的 Linux 支持中详细介绍的配置。