DTS Problem

Question

DTS Problem

Carl Rimmer

SSCarpal Tunnel

Points: 4068
More actions
January 4, 2005 at 5:37 am

#163472
I am slightly confused.
We have a job that writes to a UNC path on a monthly basis which fails when scheduled via a JOB but works when you run the DTS package manually. Fair enough.
The thing is the owner of the Job is a network account which has access to the UNC path that the report is trying to write to?
Am I right to think that the Job runs under the context of the owner of the Job?

The SQL Server Agent is run under a local account – this means it won’t have access to any UNC paths
I have looked on google and found a forum discussion which says
“When the DTS package is scheduled it will run under the account of the SQL Server Agent.
```
So make sure the account you specified for the SQL Server Agent Service does have permission to access the file.”
```
```
If that’s the case the Job is trying to run as the local system account isn’t it?  That will be the reason it is failing? 
```
```
Can anyone put me straight on this?
```

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

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

blurry Grasshopper Points: 10 More actions · Answer 1

I had the same problem while SQL Agent was running locally. If you change it to run under a domain account that has access to the shares I believe you should be fine.

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 2

Don't know why the Agent/Service is running under a local system account.

The SQL backups are currently done using Backup exec so I guess thats why it has been set up this way.

Have checked other reports and they output to the C: of the SQL Server.

I am looking at writing the backups to a network share and will need to change the login account for this.

Thanks for your reply

Carl

IanMGSmith Ten Centuries Points: 1286 More actions · Answer 3

Just a caution that SQLSERVERAGENT account may also require suitable access on the local server.

For this we used a generic account (owned by dba) which was given admin access on local server plus suitable access to certain network resources.

Forgive me if this was obvious.

Kindest Regards,

Ian Smith

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 4

I am looking to use a DBA Domain account to run the SQLSERVER. This account has access to the report paths and I believe is a local admin on the SQL SERVER box.

Is there any reason to run SQL SERVER as a local account rather than a domain account? Apart from whether you need to write to the network?

I must have been misinformed as I have always believed that DTS packages are run in the context of the owner of the Job that runs the package?

If Jobs are run under the context of the SQL SERVER Agent account. Why does SQL Server allow you to change the owner of a Job? I guess this is for permissions on the database?

Regards

Carl

IanMGSmith Ten Centuries Points: 1286 More actions · Answer 5

When you manually run a DTS package, YOUR own logon account (credentials and permissions) are used for all access requirements.

Note: SQLSERVERAGENT is a Windows service and NOT part of SQL Server.

Think of SQLSERVERAGENT as another user. Just like you do, it logs onto SQL Server using the account and password given to it.

When a scheduled JOB is run by SQLSERVERAGENT, the specified account (credentials and permissions) are used for all access requirements. I believe this is called "impersonation".

I see no reason to run a domain account unless you need to automatically access other protected resources.

The specified owner of any object simply gives that user permission to change that object. This is useful when non-admin users create objects.

Hope this is helpful.

Kindest Regards,

Ian Smith

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 6

Thanks again for you reply Ian.

I will need to use a domain account as the report that is failing needs to write to the Network.

I could leave the service running as the local system account and write the report to the C: of the sql server as the report is part of a semi-automatic process but I am also looking at possibly backing up to a network share to include transaction logs. The current backup solution only backs up the databases of an evening using Backup Exec.

I have also read on the web that it is better to use a domain account as oppossed to a local account to run the services.

So the owner of a Job simply allows the specified account to change the Job and nothing more.

Thanks again

Carl

Michael Girdler Right there with Babe Points: 780 More actions · Answer 7

I don't like using domain accounts for local services as there can be issues with machine booting and not being able to see the network when they boot...

I run my scheduled DTS packages using Windows Scheduler and domain accounts. I find that this gives me more control of the process and I don't have to worry about mucking up sql if a password changes.... Also means I dont' have to run the DTS package off the SQL server if I don't want.

Just make sure that the account that you use to run the Scheduled job has rights to do what you want. If you log on with the account you can actually test it....

Enjoy

Michael

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 8

I can't seem to get a difinitive answer to the question of Local System or Domain Account for the Services. I've had a look on the microsoft web site and they advise to

Run as a Windows domain user account with regular user privileges. Do not run as local system, local administrator, or domain administrator accounts.

Check out the link:

http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp

I think I'll follow their advice and run as a Domain account.

Regards

Carl