sql-server - ODBC connection fails when running with SQL Server Agent

Question

I've changed an SSIS package to use a 64-bit ODBC connection instead of 32-bit.

After deployment it works fine when executing the package manually but when running a SQL Server Agent job with the package it fails with:

An ODBC Error -1 has occurred

and

There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server

and

The AcquireConnection call to the connection manager Athena West-1 64-bit failed with error code 0xC0014009.

What is the reason for this error?

标签： sql-serverssisodbc

Answer 1

如果这不是 32/64 位问题，那么我会查看权限。

当您手动执行包并且它可以工作时，是因为您拥有正确的权限。

可能 SQL Server 代理服务凭据对目标数据库没有权限。

右键单击作业 > 属性 > 常规 > 所有者，然后在此处更改所有者。如果您的用户有权访问远程服务器并执行，请设置您的用户。

还要在远程服务器上检查哪些用户具有权限。

只是猜测。