我在运行 SSIS 包时遇到问题。这个问题有几个要素,我希望的是实现目标的总体正确方法。
此 SSIS 包从网络上的 Excel 文件导入数据库 (MyDb)。它在 VS 中正常工作。最初,它在部署时也能正常工作,我以系统管理员身份登录(主要是……我将在下面进一步详细介绍),但以其他人身份登录时它不起作用。
问题的一个关键部分是大多数需要能够运行此程序包的用户实际上对正在读取或更新的表没有直接的 SELECT、INSERT、UPDATE 或 DELETE 权限。这是故意的,我需要保持这种状态。我需要他们能够通过 SSIS 包更新表格,而不是随需应变。
为了解决这个问题,经过多次调整,我创建了以下存储过程:
USE [MyDb]
GO
CREATE PROCEDURE [impexp].[udp_ImportSpreadsheet_PersonActionsAndEvents]
-- Add the parameters for the stored procedure here
@ActionID int
, @FileName nvarchar(250)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @execution_id bigint
DELETE FROM [impexp].[PersonActionsAndEventsImport_CellNoMatch]
WHERE ActionID = @ActionID;
EXECUTE [SSISDB].[impexp].[udp_ImportSpreadsheet_PersonActionsAndEvents]
@ActionID
,@FileName
END
USE [SSISDB]
GO
CREATE PROCEDURE [impexp].[udp_ImportSpreadsheet_PersonActionsAndEvents]
-- Add the parameters for the stored procedure here
@ActionID int
, @FileName nvarchar(250)
WITH EXECUTE AS 'MyDomain\SSISUser'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @execution_id bigint
EXEC [SSISDB].catalog.create_execution
@package_name=N'EventsImport.dtsx',
@execution_id=@execution_id OUTPUT,
@folder_name=N'Allusr_ImportsAndExports',
@project_name=N'Imports',
@use32bitruntime=TRUE,
@reference_id=Null
SELECT @execution_id
--run synchronously so the rest of the code doesn't run until after it's done.
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id
, @object_type = 50 -- System parameter
, @parameter_name = N'SYNCHRONIZED'
, @parameter_value = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=30,
@parameter_name=N'ActionID',
@parameter_value=@ActionID
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=30,
@parameter_name=N'FileName',
@parameter_value=@FileName
EXEC [SSISDB].[catalog].[start_execution] @execution_id
END
“MyDomain\SSISUser”确实使用 Windows 身份验证。它是 SSISDB 中两个用户定义角色的成员,这两个角色赋予它执行包的权限(这些角色的其他成员也具有对 SSISDB 中的“MyDomain\SSISUser”的模拟权限),并且它是 datareader 和 datawriter 角色的成员我的数据库。
(WITH EXECUTE AS 'MyDomain\SSISUser' 是我今天早些时候所做的最新更改 - 在此之前,我试图让它以不同的 SQL 身份验证用户身份执行)。
如果我按原样运行它,我会收到以下错误(无论我是否以系统管理员身份登录):
消息 27123,级别 16,状态 1,第 3 行操作无法由使用 SQL Server 身份验证的帐户启动。使用使用集成身份验证的帐户开始操作。
消息 6522,级别 16,状态 1,过程 internal.start_execution_internal,第 0 行 [批处理起始行 2]
在执行用户定义的例程或聚合“start_execution_internal”期间发生 .NET Framework 错误:System.Data.SqlClient.SqlException:The使用 SQL Server 身份验证的帐户无法启动操作。使用使用集成身份验证的帐户开始操作。
System.Data.SqlClient.SqlException:在 System.Data.SqlClient.SqlConnection.OnError(SqlException 异常,Boolean breakConnection,Action`1 wrapCloseInAction)在 System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)在 System.Data System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 完成,字符串方法名称,布尔值 sendToPipe,Int32 超时,布尔值和 usedCache,布尔值 asyncWrite,布尔值 inRetry)在 System.Data.SqlClient .SqlCommand.ExecuteToPipe(SmiContext pipeContext) 在 Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand 命令) 在 Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.RaiseError(SysMessageId messageId, SysMessageSeverityObject[] args) 在 Microsoft.SqlServer.IntegrationServices.Server.ServerApi.StartExecutionInternal(SqlInt64 projectId, SqlInt64 executionId, SqlInt64 versionId, SqlInt16 use32BitRuntime)
如果我注释掉“WITH EXECUTE AS 'MyDomain\SSISUser'”,那么它在以 MyDb 的系统管理员/dbowner 身份登录时运行完美,但在以其他任何人(即不使用 Windows 身份验证的登录名)身份登录时运行无法直接访问表),然后程序包运行时出现错误(主要错误是“对象‘Mytablename’的 SELECT 权限被拒绝......”)。
因此,核心问题是我需要无法直接访问表的用户能够运行基于 Excel 文件读取和更新表的 SSIS 包,并且模拟似乎不起作用。(我也尝试过对程序进行证书签名——这没有用,我猜出于同样的原因)。有没有办法让模仿工作,或者有另一种方法来实现同样的目标?
运行 SQL Server 2019 标准版。
结果一直都很简单。解决方案是在原始 (VS) 包中编辑与数据库的连接,以便 SSIS 以(在我的例子中是经过 SQL 身份验证的)用户身份登录,并有权执行它需要执行的所有操作。以前,与 SQL Server 的 SSIS 包连接使用 Windows 身份验证。
完成后,我只是从 SSIS 过程中删除了“WITH EXECUTE AS...”行,并且它工作得很好,无论我是否以所有者身份登录。