通过 ssms 远程运行时 SSIS 作业失败，但在 SQL Server 上运行良好

在带有 SSIS 的开发箱上安装了 SQL Server 2016.
有一个通过目录循环并验证 XML 文件的包.

作业在 SQL Server 上从 Integration Services 目录和 SQL Server 代理执行时运行良好.在本地开发人员的 PC 上也运行良好.

如果您通过 SSMS 远程连接到 SQL Server(同一用户)并尝试通过集成服务目录执行包，则会出现问题，它运行但无法通过文件循环.它给出了以下错误:

https://blogs.msdn.microsoft.com/autz_auth_stuff/2011/05/03/kerberos-delegation/

Installed SQL Server 2016 on a dev box, with SSIS.
Have a package that loops thru a directory and validates XML files.

The job runs fine on the SQL Server from the Integration Services Catalog and from SQL Server Agent, when executed there. Also runs fine in the local developers PC's.

The issue comes in if you connect to the SQL Server via SSMS remotely, (Same user) and try to execute the package via Integration Services Catalog, it runs but fails to loop thru files. It gives the following error: ForEach Error/Warning

But when executed on the SQL Server or thru the SQL Server Agent, the job runs fine. Only when using SSMS connecting remotely does it not loop thru the directory. Same user credentials are used. This works on SSIS 2012 /SQL Server 2012.

I have checked and adjust the DCom rights to Launch and Activate. This had no affect either.

Any ideas why this is not working on SQL Server / SSIS 2016?

Thanks,

Patrick

解决方案

Kerberos double hop issue. If you RDP into the machine and connect to the server via SSMS there, it works. Same command but from your computer's SSMS to the remote database and it throws the error/warning about no files found.

You present your credentials to SQL Server from your machine. That's one hop. SQL Server then tries to present your credentials to the file share and the account that runs SQL Server is not authorized for delegation and so it can't access that resource.

You need to have your DBA get with your Network/Active Directory administrators and have the admin account trusted.

https://blogs.msdn.microsoft.com/autz_auth_stuff/2011/05/03/kerberos-delegation/