January 6, 2004 at 1:11 pm
I want to confirm something with the experts out there. To my knowledge the following is true.
A DTS package has an owner. Typically this is a person who created the package.
A SQL Job has an owner as well.
SQL Service is tied to an NT account.
My question is, when you schedule a DTS package to run, under what NT account does the package run? The package owner, the job owner, or the service? The reason I ask is because I need to be clear on what account needs which rights and so forth.
Thanks for any guidance!
January 6, 2004 at 2:52 pm
When you schedule a DTS package from the DTS Package Designer, the resulting job is owned by the login with which you signed into SQL Server. The job will be executed as that login. (This is why some schedule fails as the login may not have sufficient privilege to execute a job).
What I usually do is change the job ownership to 'sa' after the users have scheduled their DTS packages.
However, if you have set up a proxy account for SQL Server Agent, that account will be used to run the scheduled job.
Joseph
January 6, 2004 at 4:23 pm
Thanks Joseph for your response. I want to make sure I understand you correctly. If I don't have a proxy account for SQL Server Agent (is this possible?), then the DTS package is executed with the permissions of the JOB owner. Is this correct? It seems to me that packages are always ran under the proxy account.
Also, what is the best way to change the job ownership to sa?
Thanks-
Lee
January 6, 2004 at 4:41 pm
Yes you are correct. If you right click SQL Server Agent in EM and select Properties, Job System. You'll see where you can set the proxy account to use for executing CmdExec and ActiveScripting job step. When you schedule a DTS package, the resulting job step is a CmdExec task and this requires SysAdmin rights or a proxy account with a SysAdmin rights.
To change the job ownership to sa: double click the job in EM and select the Owner drop down box to select an owner with a sysadmin privilege.
Joseph
January 7, 2004 at 7:28 am
Okay thanks. Last question. If I'm using a Send Mail task in a package, it would seem to be the package would have to run under the proxy account in order to obtain the correct Mail Profile specified in the Send Mail task. Is that right?
January 7, 2004 at 8:21 am
No, I don't think so. The SendMail task uses whatever email profile is available on the computer on which the DTS package is run. For instance, if you run the package on your workstation, it uses your mail profile, but if you run it on the SQL Server machine either interactively or through a scheduled job, then it uses the mail profile set up on that computer. If no mail profile is set up, then the task fails or if the password you used in SendMail task is incorrect, the task fails.
Joseph
January 7, 2004 at 8:27 am
I agree with you that which ever computer runs the task that the profile has to be there. But if a Mail Profile is tied to a user, then which ever account runs the package has to have that Mail Profile established. So in that case, if the NT account for the SQL Service is what runs the job, then the Profile has to be established under that account on the server.
Also, my network guy asked how is it possible that an owner of a job could be used as the account that executes a package. Since you do not specify a password on the owner of the job, then how does SQL "log in" as the account and obtain that account's credentials?
January 7, 2004 at 8:46 am
I think they are two separate issues. The job owner (for a scheduled DTS package) needs to have sysadmin rights to ba able to execute CmdExec. You could also set up a proxy account for SQL Server Agent if you don't want to give the job owner that privilege.
The DTS SendMail task is different thing entirely. The only requirement is that a mail profile is set up correctly on whichever computer the package is executed and that you provide the correct mail account password in the SendMail task itself.
As the question from your network guy, all jobs execute under the credential of SQL Server Agent but then it checks the job owner rights to CmdExec. If the owner do not have that right and there is no proxy account set up, the job fails.
Joseph
January 8, 2004 at 12:26 pm
fromnaija, I belive you are wrong with regard to the Send Mail Task. SendMail uses a MAPI profile, and it is the name of the MAPI profile that you select in the task itself during design time. If developing on my workstation I know my MAPI profile name is "MS Outlook", but this may not work when scheduled, if a profile of the same name is not available on the server under the correct account.
MAPI profiles are part of the user profile so if the package is scheduled, the account under which the account executes must have a MAPI profile. So if the job is owned by a sysadmin the SQL Server Agent service account must have a MAPI profile and that name must match what the task expects.
If one of my (non-sysadmin) users schedules a DTS package using the Send Mail task, and assuming I have set the Proxy Account correctly for SQL 2000, then the proxy account must also have a MAPI profile and that name match.
When using the proxy account it is the execution context so it must have a MAPI profile, it must have the network permissions if loading a remote file, and it must also have permissions to connect to a SQL Server if using NT authentication.
This KB covers the topics of job owners quite well and the effects this will have-
http://support.microsoft.com/default.aspx?scid=269074
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply