General discussion

Locked

SQL DTS package can't run at server after changes

By kaypaul ·
I have made changes to a DTS package at my laptop, which have accessed to the SQL 2000 server.

I am able to execute this package. But when I run this package as a scheduled job in SQL Agent, this package failed with error message "Error opening datafile. Logon invalid user or password wrong".

I also got this error when I manually rerun this package at the server.

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by kevin.lobo In reply to SQL DTS package can't run ...

The errors you are getting are due to permission issues.
Ask your DBA or someone with SA priveleges on the SQL Server to change the ownership of the scheduled job/package to SA or a similar level login.

Collapse -

by kaypaul In reply to

Do I need to make this change every time a user changes or updates a package? Is there any other solution to this problem?

Collapse -

by SoftwareGuru In reply to SQL DTS package can't run ...

(Assuming that your datafile is an external db like MS Access or text file)
SQL Agent Service usually runs as a local system account. This account doesn't have access to any of the network resources. So, if your data file is located on a network drive SQL Agent won't be able to access the file. To correct this problem, either copy the file to the SQL machine or create the same admin account (same password)on the two machines and then run SQL Agent with that account.
Good Luck.
CJ

Collapse -

by kaypaul In reply to

How do I check the present SQL Server ownership of the scheduled job or package? How do I go about to change the SQL Server ownership for them?

I am running linked servers. Using DTS to call Excel with a Update macro to update a spreadsheet from a text file. The spreadsheet is located at the linked server. Whenever the package is failed/cancelled, the task is not terminated. This will lead to our users unable to amend the spreadsheet manually. They received a message 'read-only' access.

Collapse -

by traty1 In reply to SQL DTS package can't run ...

This has nothing to do with package ownership. When you manually run a dts, it uses your credentials. When you schedule the dts as a job it uses the sql agent. Whatever account you have given the sql agent, make sure this account has all the rights and permissions needed to access whatever datafiles, folders, and drives your dts uses. For instance, if your dts grabs data from an Access database on server A drive X, ensure the sql server agent account has permissions to the database file on server A drive X.

Collapse -

by kaypaul In reply to

Look like I have a "Double hop" problem by Microsoft(MS). MS
provided the solution saying:

"Map the clients on server A to a standard security login on server B, by using either the "sp_addlinkedsrvlogin" stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager."

Have anyone out there used these options before? I would like a sample script or more deatil steps.

Collapse -

by mpeer In reply to SQL DTS package can't run ...

I've had once the same problem to. And the problem was that the DTS package could not run because my local computer (with only the client tools) had SQL Service Pack 3, while the server still had Service Pack 1 or 2. Upgrading the sql server to service pack 3 solved the problem.

Note: packages with SP3 do have a diferent binairy layout than the previous SP's.

Collapse -

by kaypaul In reply to

I have service pack 3 installed.

Collapse -

by kaypaul In reply to SQL DTS package can't run ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums