DTS works manually, but fails as a job

Question

DTS works manually, but fails as a job

sql_er

SSCarpal Tunnel

Points: 4135
More actions
May 7, 2007 at 7:38 am

#61302

Guys,
I have a DTS package, running accross a few servers. I created a job to run it automatically once a week. When I run this DTS package manually, it runs with no problem. When the job executes (running this dts package), I get the following error:
Executed as user: AJJ\cust. ...OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error... Process Exit Code 3. The step failed.
Could anyone suggest the possible problem? It looks like there is login issue, but why would that only be a problem when the job is executing the DTS package and how can I fix it?
Please let me know
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

bdohmen SSCrazy Points: 2996 More actions · Answer 1

Do you use Windows Authentication for your connections? If so, make sure the login used by the SQL Server Service has permissions for each connection/server. If you run the package manually, it will use your credentials for security.

Brian

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Jobs run as SQL Server Agent. That's the login that needs permissions for each server.

Greg

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

Keep in mind when you run it manually from your workstation, it runs from your workstation under the context of your account. As a job it runs under the SQL Agent service account.

The two posts above are the advice to follow.

bdohmen SSCrazy Points: 2996 More actions · Answer 4

I stand corrected. Thanks Greg and Steve. I should have my coffee before I start typing.

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 5

Guys,

Thanks for the replies, but I am still a bit confused.

Are you saying that the owner of the job should be a user with priviliges to access both servers?

Thanks a lot

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

The login that SQL Server Agent runs as (look at properties for SQL Server Agent) needs to be a domain account that has access to the remote server.

When a DTS package is executed from a scheduled job, it runs in the security context of that account. If you run SQL Server Agent as a local account rather than a domain account, it won't be able to connect to another server. That's why Brian asked if you were using a Windows login as opposed to a SQL Server login.

Greg

Will SQL 4 Food SSCrazy Points: 2253 More actions · Answer 7

You can also explicitly specify what credentials are used by the connection object(s) in the DTS to authenticate to the destination servers. If not specified, the connection inherits the credentials of the user running the package.

If your login has rights but the login SQL Agent operates as does not, this will cause your problem. You can also see "partial" failures this way - the DTS will run up to a point, then encounter a step with a connection the Agent doesn't have rights on.

One way around this is to create a SQL Server account for running DTS jobs. You'd create the same or similar users within any environment which would be a source or destination connection inside a DTS. This way, you can grant only the rights commonly needed by the packages. Then, all your connections will work and the Agent only needs local rights to launch the DTS.

Be careful not to use a real person, as they occasionally leave the company and things fail when security audits remove their logins.

Hope this helps!

Carter

But boss, why must the urgent always take precedence over the important?

Glen Harland SSC Enthusiast Points: 153 More actions · Answer 8

Is your Windows version 64-bit?

I had the same error message with a DTS package. It worked on 32-bit Windows and on 64-bit Windows when running manually, but when running as a job, or from a stored procedure on a 64-bit server I got the error. I don't know for sure, but I suspect it is because Microsoft have not yet written the OLEDB wrapper DLL for 64-bit Windows (MSDASQL.DLL) and for some reason this is not required when running DTS manually.

The MSDN article "Troubleshooting Package Execution" may also help (although Server 2005, it mentions Server 2000/64-bit issues):

http://msdn2.microsoft.com/en-us/library/aa337088.aspx