问题描述
1.Unable to start execution of step 1 (reason: JobOwner kigiboy doesn't have permissions to use proxy 1 for subsystem SSIS). The step failed.
#没有权限导致
#在Agent-->Proxies-->SSIS Package Execute下选择job中得SSIS代理名称,然后再Principals这个选项卡中添加运行job的owner
2.Unable to start execution of step 1 (reason: Error authenticating proxy Desktopkigiboy, system error: The user name or password is incorrect.). The step failed.
#SSIS的Credential不对,检查SSIS代理中的credential name,并在Server Object对象下修改对应的credential名称下的账号,必须是Windows认证的账号.
#不知道是不是必须sysadmin才可以,没试过授予对应的SSIS权限可不可以
3.Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1). The step failed.
#这个是因为上一步未修改成正确的账号导致的,即使这个credential账号是sysadmin,但如果不是Windows认证的账号依旧不行
4.Microsoft (R) SQL Server Execute Package Utility Version 14.0.3192.2 for 32-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 14:08:34 Error: 2020-08-21 14:08:50.34 Code: 0xC0202009 Source: WGQ_SHA_MCD_Storer Connection manager "Microsoft OLE DB Provider for SQL Server 2" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.". End Error Error: 2020-08-21 14:08:50.34 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server 2" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2020-08-21 14:08:50.34 Code: 0xC0047017 Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2020-08-21 14:08:50.34 Code: 0xC004700C Source: DTSTask_DTSDataPumpTask_1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2020-08-21 14:08:50.34 Code: 0xC0024107 Source: DTSTask_DTSDataPumpTask_1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:08:34 Finished: 14:08:50 Elapsed: 15.922 seconds. The package execution failed. The step failed.
#这是因为TLS1.2导致的,注意这里:[DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.
#重新开启TLS1.0/1.1后job运行成功
#有说使用旧版本ODBC 11.0或其他版本可以成功的,但不是开发,所以没法验证
#开启TLS1.2的,贴下Driver={ODBC Driver 11 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;