从 Azure 的 SSIS 包中使用 Windows 身份验证访问数据存储和文件共享Access data stores and file shares with Windows authentication from SSIS packages in Azure

适用范围:SQL Server,包括 Linux Azure SQL 数据库yesAzure SQL 数据仓库no并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

可以从 Azure 数据工厂 (ADF) 的 Azure-SSIS Integration Runtime (IR) 上运行的 SSIS 包中使用 Windows 身份验证访问数据存储(如 SQL Server、文件共享、Azure 文件存储等)。You can use Windows authentication to access data stores, such as SQL Servers, file shares, Azure Files, etc. from SSIS packages running on your Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF). 数据存储可以位于本地环境,可以托管在 Azure 虚拟机 (VM) 上,也可以作为托管服务在 Azure 中运行。Your data stores can be on premises, hosted on Azure Virtual Machines (VMs), or running in Azure as managed services. 如果它们位于本地环境,则需要将 Azure SSIS IR 加入连接到本地网络的虚拟网络 (VNet),请参阅将 Azure-SSIS IR 加入 VNetIf they are on premises, you need to join your Azure-SSIS IR to a Virtual Network (VNet) connected to your on-premises network, see Join Azure-SSIS IR to a VNet. 以下四种方法可从 Azure-SSIS IR 上运行的 SSIS 包中使用 Windows 身份验证访问数据存储:There are four methods to access data stores with Windows authentication from SSIS packages running on your Azure-SSIS IR:

连接方法Connection method 有效范围Effective scope 安装步骤Setup step 在包中访问方法Access method in packages 凭据集和连接资源的数量Number of credential sets and connected resources 连接资源的类型Type of connected resources
设置活动级别执行上下文Setting up an activity-level execution context 每个执行 SSIS 包活动Per Execute SSIS Package activity 将 SSIS 包作为 ADF 管道中的执行 SSIS 包活动运行时,配置“Windows 身份验证” 属性以设置“执行/运行方式”上下文。Configure the Windows authentication property to set up an "Execution/Run as" context when running SSIS packages as Execute SSIS Package activities in ADF pipelines.

有关详细信息,请参阅配置执行 SSIS 包活动For more info, see Configure Execute SSIS Package activity.
通过 UNC 路径直接访问包资源(例如,如果使用文件共享或 Azure 文件存储:\\YourFileShareServerName\YourFolderName\\YourAzureStorageAccountName.file.core.windows.net\YourFolderNameAccess resources directly in packages via UNC path, e.g. if you use file shares or Azure Files: \\YourFileShareServerName\YourFolderName or \\YourAzureStorageAccountName.file.core.windows.net\YourFolderName 仅支持适用于所有连接资源的一个凭据集Support only one credential set for all connected resources - 本地/Azure VM 上的文件共享- File shares on premises/Azure VMs

- Azure 文件,请参阅使用 Azure 文件共享- Azure Files, see Use an Azure file share

- 使用 Windows 身份验证的本地/Azure VM 上的 SQL Server- SQL Servers on premises/Azure VMs with Windows authentication

- 使用 Windows 身份验证的其他资源- Other resources with Windows authentication
设置目录级别执行上下文Setting up a catalog-level execution context 每个 Azure-SSIS IR,但是,如果同时还设置活动级别执行上下文,则会被替代(见上文)Per Azure-SSIS IR, but will be overriden when also setting up an activity-level execution context (see above) 执行 SSISDB catalog.set_execution_credential 存储过程来设置“执行/运行方式”上下文。Execute SSISDB catalog.set_execution_credential stored procedure to set up an "Execution/Run as" context.

有关详细信息,请参阅本文下面的其余部分。For more info, see the rest of this article below.
通过 UNC 路径直接访问包资源(例如,如果使用文件共享或 Azure 文件存储:\\YourFileShareServerName\YourFolderName\\YourAzureStorageAccountName.file.core.windows.net\YourFolderNameAccess resources directly in packages via UNC path, e.g. if you use file shares or Azure Files: \\YourFileShareServerName\YourFolderName or \\YourAzureStorageAccountName.file.core.windows.net\YourFolderName 仅支持适用于所有连接资源的一个凭据集Support only one credential set for all connected resources - 本地/Azure VM 上的文件共享- File shares on premises/Azure VMs

- Azure 文件,请参阅使用 Azure 文件共享- Azure Files, see Use an Azure file share

- 使用 Windows 身份验证的本地/Azure VM 上的 SQL Server- SQL Servers on premises/Azure VMs with Windows authentication

- 使用 Windows 身份验证的其他资源- Other resources with Windows authentication
通过 cmdkey 命令持久保留凭据Persisting credentials via cmdkey command 每个 Azure-SSIS IR,但是,如果同时还设置活动/目录级别执行上下文,则会被替代(见上文)Per Azure-SSIS IR, but will be overriden when also setting up an activity/catalog -level execution context (see above) 在预配/重新配置 Azure-SSIS IR 时,在自定义安装脚本 (main.cmd) 中执行 cmdkey 命令,例如,如果使用文件共享或 Azure 文件存储 cmdkey /add:YourFileShareServerName /user:YourDomainName\YourUsername /pass:YourPasswordcmdkey /add:YourAzureStorageAccountName.file.core.windows.net /user:azure\YourAzureStorageAccountName /pass:YourAccessKeyExecute cmdkey command in a custom setup script (main.cmd) when provisioning/reconfiguring your Azure-SSIS IR e.g. if you use file shares or Azure Files: cmdkey /add:YourFileShareServerName /user:YourDomainName\YourUsername /pass:YourPassword or cmdkey /add:YourAzureStorageAccountName.file.core.windows.net /user:azure\YourAzureStorageAccountName /pass:YourAccessKey.

有关详细信息,请参阅为 Azure-SSIS IR 自定义安装程序For more info, see Customize setup for Azure-SSIS IR.
通过 UNC 路径直接访问包资源(例如,如果使用文件共享或 Azure 文件存储:\\YourFileShareServerName\YourFolderName\\YourAzureStorageAccountName.file.core.windows.net\YourFolderNameAccess resources directly in packages via UNC path, e.g. if you use file shares or Azure Files: \\YourFileShareServerName\YourFolderName or \\YourAzureStorageAccountName.file.core.windows.net\YourFolderName 支持不同连接资源的多个凭据集Support multiple credential sets for different connected resources - 本地/Azure VM 上的文件共享- File shares on premises/Azure VMs

- Azure 文件,请参阅使用 Azure 文件共享- Azure Files, see Use an Azure file share

- 使用 Windows 身份验证的本地/Azure VM 上的 SQL Server- SQL Servers on premises/Azure VMs with Windows authentication

- 使用 Windows 身份验证的其他资源- Other resources with Windows authentication
在包执行时装载驱动器(非永久)Mounting drives at package execution time (non-persistent) 每个包Per package 在包的控制流开头添加的执行过程任务中(例如,net use D: \\YourFileShareServerName\YourFolderName)执行 net use 命令Execute net use command in Execute Process Task that is added at the beginning of control flow in your packages, for example, net use D: \\YourFileShareServerName\YourFolderName 通过映射驱动器访问文件共享Access file shares via mapped drives 支持不同文件共享的多个驱动器Support multiple drives for different file shares - 本地/Azure VM 上的文件共享- File shares on premises/Azure VMs

- Azure 文件,请参阅使用 Azure 文件共享- Azure Files, see Use an Azure file share

警告

如果不使用任何上述方法通过 Windows 身份验证访问数据存储,依赖于 Windows 身份验证的包将无法访问它们,并在运行时失败。If you do not use any of the above methods to access data stores with Windows authentication, your packages that depend on Windows authentication will not be able to access them and will fail at run time.

本文的其余部分介绍如何配置托管在 Azure SQL 数据库服务器/托管实例上的 SSIS 目录 (SSISDB),以便在使用 Windows 身份验证的 Azure-SSIS IR 上运行包来访问数据存储。The rest of this article describes how to configure SSIS catalog (SSISDB) hosted in Azure SQL Database server/Managed Instance to run packages on Azure-SSIS IR that use Windows authentication to access data stores.

仅可使用一组凭据You can only use one set of credentials

在 SSIS 包中使用 Windows 身份验证时,只能使用一组凭据。When you use Windows authentication in an SSIS package, you can only use one set of credentials. 按照本文中的步骤操作时提供的域凭据适用于 Azure-SSIS IR 上的所有包执行(交互式或按预定),直至更改或删除这些凭据。The domain credentials that you provide when you follow the steps in this article apply to all package executions - interactive or scheduled - on your Azure-SSIS IR until you change or remove them. 如果必须使用多组不同的凭据将包连接到不同的数据存储,应考虑上述替代方法。If your package has to connect to multiple data stores with different sets of credentials, you should consider the above alternative methods.

提供 Windows 身份验证的域凭据Provide domain credentials for Windows authentication

若要提供域凭据,让包使用 Windows 身份验证访问本地数据存储,请执行以下操作:To provide domain credentials that let packages use Windows authentication to access data stores on premises, do the following things:

  1. 使用 SQL Server Management Studio (SSMS) 或其他工具连接到托管 SSISDB 的 Azure SQL 数据库服务器/托管实例。With SQL Server Management Studio (SSMS) or another tool, connect to Azure SQL Database server/Managed Instance that hosts SSISDB. 有关详细信息,请参阅连接到 Azure 中的 SSISDBFor more info, see Connect to SSISDB in Azure.

  2. 将 SSISDB 设置为当前数据库后,打开一个查询窗口。With SSISDB as the current database, open a query window.

  3. 运行以下存储过程,并提供相应的域凭据:Run the following stored procedure and provide the appropriate domain credentials:

    catalog.set_execution_credential @user='<your user name>', @domain='<your domain name>', @password='<your password>'
    
  4. 运行 SSIS 包。Run your SSIS packages. 这些包将使用所提供的凭据通过 Windows 身份验证访问本地数据存储。The packages will use the credentials that you provided to access data stores on premises with Windows authentication.

查看域凭据View domain credentials

若要查看可用的域凭据,请执行以下操作:To view the active domain credentials, do the following things:

  1. 使用 SSMS 或其他工具连接到托管 SSISDB 的 Azure SQL 数据库服务器/托管实例。With SSMS or another tool, connect to Azure SQL Database server/Managed Instance that hosts SSISDB. 有关详细信息,请参阅连接到 Azure 中的 SSISDBFor more info, see Connect to SSISDB in Azure.

  2. 将 SSISDB 设置为当前数据库后,打开一个查询窗口。With SSISDB as the current database, open a query window.

  3. 运行以下存储过程,并检查输出:Run the following stored procedure and check the output:

    SELECT * 
    FROM catalog.master_properties
    WHERE property_name = 'EXECUTION_DOMAIN' OR property_name = 'EXECUTION_USER'
    

清除域凭据Clear domain credentials

若要清除和删除按本文所述提供的凭据,请执行以下操作:To clear and remove the credentials that you provided as described in this article, do the following things:

  1. 使用 SSMS 或其他工具连接到托管 SSISDB 的 Azure SQL 数据库服务器/托管实例。With SSMS or another tool, connect to Azure SQL Database server/Managed Instance that hosts SSISDB. 有关详细信息,请参阅连接到 Azure 中的 SSISDBFor more info, see Connect to SSISDB in Azure.

  2. 将 SSISDB 设置为当前数据库后,打开一个查询窗口。With SSISDB as the current database, open a query window.

  3. 运行以下存储过程:Run the following stored procedure:

    catalog.set_execution_credential @user='', @domain='', @password=''
    

连接到本地 SQL ServerConnect to a SQL Server on premises

若要检查能否连接到本地 SQL Server,请执行以下操作:To check whether you can connect to a SQL Server on premises, do the following things:

  1. 若要运行此测试,请找一台未加入域的计算机。To run this test, find a non-domain-joined computer.

  2. 在未加入域的计算机上,运行以下命令,以便通过要使用的域凭据启动 SSMS:On the non-domain-joined computer, run the following command to start SSMS with the domain credentials that you want to use:

    runas.exe /netonly /user:<domain>\<username> SSMS.exe
    
  3. 通过 SSMS 检查能否连接到本地 SQL Server。From SSMS, check whether you can connect to the SQL Server on premises.

必备条件Prerequisites

若要从 Azure 中运行的包中访问本地 SQL Server,请执行以下操作:To access a SQL Server on premises from packages running in Azure, do the following things:

  1. 在 SQL Server 配置管理器中,启用 TCP/IP 协议。In SQL Server Configuration Manager, enable TCP/IP protocol.
  2. 允许通过 Windows 防火墙进行访问。Allow access through Windows firewall. 有关详细信息,请参阅配置 Windows 防火墙以访问 SQL ServerFor more info, see Configure Windows firewall to access SQL Server.
  3. 将 Azure-SSIS IR 加入连接到本地 SQL Server 的 VNet。Join your Azure-SSIS IR to a VNet that is connected to the SQL Server on premises. 有关详细信息,请参阅将 Azure-SSIS IR 加入 VNetFor more info, see Join Azure-SSIS IR to a VNet.
  4. 使用 SSISDB catalog.set_execution_credential 存储过程提供凭据,如本文中所述。Use SSISDB catalog.set_execution_credential stored procedure to provide credentials as described in this article.

连接到本地文件共享Connect to a file share on premises

若要检查能否连接到本地文件共享,请执行以下操作:To check whether you can connect to a file share on premises, do the following things:

  1. 若要运行此测试,请找一台未加入域的计算机。To run this test, find a non-domain-joined computer.

  2. 在未加入域的计算机上运行以下命令。On the non-domain-joined computer, run the following commands. 这些命令会打开一个命令提示符窗口,其中包含要使用的域凭据,然后通过获取目录列表测试与本地文件共享的连接性。These commands open a command prompt window with the domain credentials that you want to use and then test connectivity to the file share on premises by getting a directory listing.

    runas.exe /netonly /user:<domain>\<username> cmd.exe
    dir \\fileshare
    
  3. 检查是否返回本地文件共享的目录列表。Check whether the directory listing is returned for the file share on premises.

必备条件Prerequisites

若要从 Azure 中运行的包中访问本地文件共享,请执行以下操作:To access a file share on premises from packages running in Azure, do the following things:

  1. 允许通过 Windows 防火墙进行访问。Allow access through Windows firewall.
  2. 将 Azure-SSIS IR 加入连接到本地文件共享的 VNet。Join your Azure-SSIS IR to a VNet that is connected to the file share on premises. 有关详细信息,请参阅将 Azure-SSIS IR 加入 VNetFor more info, see Join Azure-SSIS IR to a VNet.
  3. 使用 SSISDB catalog.set_execution_credential 存储过程提供凭据,如本文中所述。Use SSISDB catalog.set_execution_credential stored procedure to provide credentials as described in this article.

连接到 Azure VM 上的文件共享Connect to a file share on Azure VM

若要从 Azure 中运行的包中访问 Azure VM 上的文件共享,请执行以下操作:To access a file share on Azure VM from packages running in Azure, do the following things:

  1. 使用 SSMS 或其他工具连接到托管 SSISDB 的 Azure SQL 数据库服务器/托管实例。With SSMS or another tool, connect to Azure SQL Database server/Managed Instance that hosts SSISDB. 有关详细信息,请参阅连接到 Azure 中的 SSISDBFor more info, see Connect to SSISDB in Azure.

  2. 将 SSISDB 设置为当前数据库后,打开一个查询窗口。With SSISDB as the current database, open a query window.

  3. 运行以下存储过程,并提供相应的域凭据:Run the following stored procedure and provide the appropriate domain credentials:

    catalog.set_execution_credential @domain = N'.', @user = N'username of local account on Azure virtual machine', @password = N'password'
    

连接到 Azure 文件中的文件共享Connect to a file share in Azure Files

有关 Azure 文件的详细信息,请参阅 Azure 文件For more info about Azure Files, see Azure Files.

若要从 Azure 中运行的包中访问 Azure 文件存储中的文件共享,请执行以下操作:To access a file share in Azure Files from packages running in Azure, do the following things:

  1. 使用 SSMS 或其他工具连接到托管 SSISDB 的 Azure SQL 数据库服务器/托管实例。With SSMS or another tool, connect to Azure SQL Database server/Managed Instance that hosts SSISDB. 有关详细信息,请参阅连接到 Azure 中的 SSISDBFor more info, see Connect to SSISDB in Azure.

  2. 将 SSISDB 设置为当前数据库后,打开一个查询窗口。With SSISDB as the current database, open a query window.

  3. 运行以下存储过程,并提供相应的域凭据:Run the following stored procedure and provide the appropriate domain credentials:

    catalog.set_execution_credential @domain = N'Azure', @user = N'<storage-account-name>', @password = N'<storage-account-key>'
    

后续步骤Next steps