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?

Note that this thread didn't help resolve the issue: https://stackoverflow.com/questions/13943765/why-does-my-odbc-connection-fail-when-running-an-ssis-load-in-visual-studio-but — Christian, Sep 08 '21 at 11:03
Does the package worked when it was on 32 bit? Does it works if you switch back to 32 bit? — Francesco Mantovani, Sep 08 '21 at 11:31

Francesco Mantovani · Answer 1 · 2021-09-08T11:43:47.583

0

If this is not the 32/64-bit issue then I would look to the permissions.

When you execute the package manually and it works is because you have the rigth permissions.

Maybe SQL Server Agent service credential doesn't have permission on the target database.

Right click on the Job > Properties > General > Owner, and change the Owner there. Setup your user if your user has access to the remote server and execute.

Also check on the remote server what users have permissions.

Just guessing.

edited Sep 08 '21 at 11:43

answered Sep 08 '21 at 11:29

Francesco Mantovani

10,216
13
73
113

How would I check if it has right permissions? – Christian Sep 08 '21 at 11:34
The thing is, it works fine when using 32-bit ODBC connection so I don't see how it could be a permission issue? – Christian Sep 09 '21 at 09:48

ODBC connection fails when running with SQL Server Agent

1 Answers1