MSSQL SSIS agent job运行出错
问题描述
- 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
- Unable to start execution of step 1 (reason: Error authenticating proxy Desktop\kigiboy, system error: The user name or password is incorrect.). The step failed.
SSIS的Credential不对,检查SSIS代理中的credential name,并在Server Object对象下修改对应的credential名称下的账号,必须是Windows认证的账号.
不知道是不是必须sysadmin才可以,没试过授予对应的SSIS权限可不可以
- Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1). The step failed.
这个是因为上一步未修改成正确的账号导致的,即使这个credential账号是sysadmin,但如果不是Windows认证的账号依旧不行
- 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运行成功
也有可能是使用旧版驱动开发导致的，见第6点
有说使用旧版本ODBC 11.0或其他版本可以成功的,但不是开发,所以没法验证
开启TLS1.2的,贴下Driver={ODBC Driver 11 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
- Msg 15153, Level 16, State 1, Procedure master.dbo.xp_cmdshell, Line 1 [Batch Start Line 9]
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
create credential ##xp_cmdshell_proxy_account## with identity = 'hostname\sqlcmd', secret = 'pwd'
--下面这个语句可能会报错,用上面的比较好
EXEC sp_xp_cmdshell_proxy_account 'hostname\sqlcmd','pwd'
- 使用SSIS开发，遇到使用SQL Server Native Client 10.0不能连接开启TLS1.2的数据库服务器的错误，具体错误如下：
错误 1 验证错误。Package 连接管理器“ff.dfs_Report”: SSIS 错误代码 DTS_E_OLEDBERROR。出现 OLE DB 错误。错误代码: 0x80004005。 已获得 OLE DB 记录。源:“Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 说明:“由于预登录失败，客户端无法建立连接”。 已获得 OLE DB 记录。源:“Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 说明:“TCP 提供程序: 远程主机强迫关闭了一个现有的连接。 ”。 已获得 OLE DB 记录。源:“Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 说明:“客户端无法建立连接”。 已获得 OLE DB 记录。源:“Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 说明:“因为在登录之前握手期间出现错误，所以客户端无法建立连接。最常见原因包括客户端试图连接到不受支持的 SQL Server 版本、服务器太忙无法接受新连接或服务器上的资源受到限制(内存或允许的最大连接数)。”。
查看官方文档后发现已经 弃用SQL Server Native Client（SNAC）:
https://docs.microsoft.com/zh-cn/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15
使用新的驱动成功连接。
