asked
SSIS job fail due to ADO NET Source has failed to acquire the connection
Hello, I have an SQL server 2012 SSIS package that runs fine in Visual Studios (BIDS) but when I try to schedule it in SQL Agent Service Job, I get the following error: Description: ADO NET Source has failed to acquire the connection {E06725D4-46B5-4B9D-929E-3AAD98D98354} with the following error message: "ERROR [28000] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid user authorization specification. ERROR [01S00] [Pervasive][ODBC Client Interface]Invalid connection string attribute. ERROR [01S00] [Pervasive][ODBC Client Interface]Invalid connection string attribute. ERROR [28000] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager]Invalid user authorization specification. My connection is to an offsite server and I've created the ODBC connection to the server and provided the login to the Application on the connection string. However, I don't see in the connection string in the job the connection to the server, it only has the ODBC connection name and username for the application. I've added the password in the connection string to the application. I'm thinking that I need to provide the username and password for the connection to the server in the job. Can someone please assist in resolving the above error? Any help is greatly appreciated. Thanks, Doug
I have found that when using jobs to call an SSIS package you have to ensure that whatever user the package is running as has to have the right permissions on the server it's calling. In the past I have had to create proxy accounts to replicate user credentials. The other thing to look at is Package Protection levels. More information can be found here:
http://support.microsoft.com/kb/918760
14 People are following this question.
