DTS giving error when run as job
-
DTS is working fine when running through Enterprise Manager but fails when scheduled as job.
The DTS package on first sql server SS1 is copying data from second sql server SS2 to third sql server SS3. The agent is running under Co-domain/sqlserver login and has sa rights. I checked the domain user Co-domain/sqlserver has login in second and third sql server with sa permissions.
Please help... what can be the issue?
I am getting the follwoing error:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting objects for Transfer; PercentComplete = 0; ProgressCount = 0
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024891 (80070005)
Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error Detail Records:
Error: -2147024891 (80070005); Provider Error: 0 (0)
Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error: -2147024891 (80070005); Provider Error: 0 (0)
Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 5700
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: [SQL-DMO]CreateFile error on 'SS2.mydbase.LOG'. Access is denied.
Error source: Microsoft SQL-DMO
Help file: SQLDMO80.hlp
Help context: 1132
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun: Package execution complete.
-
Check to make sure that sa is the owner of the job.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Yeah, the owner is sa. Checked agin. Also, tried with Co-domain/sqlserver (login for sql agent with sa permissions) but same error. No luck.
Thanks
-
Did you right-click on the package to create and schedule the job using the wizard or did you create the job manually? I always create the job manually using this syntax:
DTSRun /S MyServer /N "My DTS Package" /E
When you use the wizard to set up the job, it creates an encrypted string that includes the sa password. If you change the sa password then the jobs no longer work. I'm not sure if this is the issue in your case, but it was a big problem for me when I first became a DBA.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Hmmm, now that I think about it, my last response is not the problem, since the DTS package starts to run.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Can anybody help with this issue or let me know which direction to look.
-
The only cause will be security settings. I would recheck the domain user account one more time (I know this is tedious). One very simple check you can perform is simplify your data transfer.
Like select ID=1 (using a connection to the tempdb on server 2 and transfer data to a new table on server 3. I would set the user up as read/write (again simplify) and build from there.
You didn't mention using TSQL for inserts so you won't need to worry about linked servers.
-
I have designed simple DTS as copy sql server objects task using DTS task. set up source and destination servers and used copy data - replace existing data for one small table (for simplicity). No other option is selected.
Checked domain user Co-domain/sqlserver. Domain user is in adminstrator group and has full control on disk (including data and log disks). Domain user has sa privileges. This is in all the sql servers (SS1,SS2 and SS3)
I am not using any TSQL. Inserts are generted by SQL DTS task copy sql server objects.
Domain user has the full administrative rights in windows and sql server. why it is not running as job? still problem.
-
When you run the DTS package manually, you are running it as whoever you are logged in as. Can you log in to the SQL Server using the Co-domain/sqlserver account to see if it still runs?
Also, are your connection objects inside the DTS package using Windows authentication?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Don't use sa. You should have a domain SQL Server account which SQL server can use to run jobs. This will allow it to run jobs that go across servers and so forth.
-
When you look at the job history, under what account does the job say to be executing ? Is it sa, or domain account or some other account ? If the owner of the job is a windows account, check the articles below.
http://support.microsoft.com/kb/241643
http://support.microsoft.com/?kbid=834124
Workaround is to change the account to sa.
-
There is no connection object in DTS. I given it try though and added connection to server SS2 and SS3 using windows authentication. but same error.
I tried changing owner of dts package, used sa and co-domain/sqlserver login but no luck same error.
Even if job owner is Co-domain\sqlserver or sa, I get the same error. SQL agent is Co-domain/sqlserver and executing job.
Executed as user: Co-domain\sqlserver. ...ting... DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1 DTSRun OnProgress: DTSStep_DTSTransferObjectsTask_1; Scripting objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024891 (80070005) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 5700 Error: -2147467259 (800040. The step failed.
Don't know where I am missing.
I saw in one forum about IP table mapping as dts access error cause but not sure how to try it out. Please help, if somebody had this problem before.
-
I found the problem. Its with the rights on dts script file directory. The dts create script file under C:\Program Files\Microsoft SQL Server\80\Tools and agent doesn't have rights. Assigned rights and worked for me.
Thanks to all for help.
-
Hi Ajay,
I was so relieved to see someone with the same problem I've been having. I've never had this problem with a simple database copy before. This is only happening on one of our SQL servers that has recently been added to the domain.
You mentioned that the domain account had administrative rights as well as sa in SQL. But even with this you still had to assign rights to the DTS script file directory on your C drive. Please explain exactly what rights you had to assign. I'll have to explain what's needed to our Network Admins I think to convince them since my domain account also is in the NT admin group on the server.
Thanks,
Teresa
Teresa Wilson
Database Administrator
Titan Systems Corporation -
Hi Teresa,
Actually C drive is for OS software and no rights were assigned to SQL agent on C. D drive is for App like SQL server and had rights on App drive, Data and Log drives. When you see the DTS package there is file directory script path which was on C drive. when I run from DTS if works as it used my local C drive instead of linked server. Moved path to data drive or the option is assign rights to sql agent on C drive.
Hope it explains.
Let me know where are you getting problem.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply