| Author |
Topic |
|
Ian Smith
Starting Member
5 Posts |
 Posted - 2004-02-12 : 12:19:55
|
| SQL SERVER 2000:I have a DTS package which imports data from a secured server on another domain.It works when I run the DTS package from Enterprise Manager but ONLY when I am logged onto the other domain.How can I create a scheduled JOB to run the DTS package? I.e. Should I be using some system account which must be trusted on the other domain?IanMGSmith |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-12 : 17:43:18
|
| The account that the SQLSERVERAGENT uses needs to have the access in order for it to work as a job. If it using the local system account, change it to a domain account that has local admin access as well as any other permissions that are needed on other servers.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-12 : 17:47:05
|
| And yes you will need a trust relationship setup between the two domains.Tara |
|
|
|
Ian Smith
Starting Member
5 Posts |
Posted - 2004-02-12 : 19:19:59
|
| Hi Tara,Your advice confirms: "If the package is run as a SQL Server Agent job, then the integrated security connection is made using the account you used to start SQL Agent (assuming that the owner of the package is a member of the Sysadmin role)."They use SQL Local System Account (many apps addressing this) so it looks like we won't be able to use the JOB schedule, OR is there another way round this?Many thanks,IanIanIanMGSmith |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-12 : 19:23:06
|
| Why don't you just change the service so that it uses a domain account with local admin privileges? Using the Local System account is not recommended anyway unless your server will never ever have to connect to a networked resource such as another server.Tara |
|
|
|
Ian Smith
Starting Member
5 Posts |
Posted - 2004-02-12 : 19:40:14
|
| Thank you Tara,I'll ask the Administrator to set SQLSERVERAGENT to Local System Account and (hopefully) he will know what that account is so I can arrange access on the other domain.Many thanksIanMGSmith |
|
|
|
Ian Smith
Starting Member
5 Posts |
Posted - 2004-02-12 : 19:42:55
|
| Oops!I got it wrong. In XP (my dev environment) the other option is "This Account - Browse/password/confirm password."IanMGSmith |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-12 : 19:45:03
|
quote:
Originally posted by Ian Smith
Thank you Tara,I'll ask the Administrator to set SQLSERVERAGENT to Local System Account and (hopefully) he will know what that account is so I can arrange access on the other domain.Many thanksIanMGSmith
No, you've got it backwards. You DON'T want the SQLSERVERAGENT service to use the Local System Account. Have the administrator create a domain account, perhaps call it sqladmin. Then have him/her add that account to the local administrators group. Then change the service so that it uses this account.And while you're at it, fix the MSSQLSERVER service so that it uses the domain account as well.Tara |
|
|
|
Ian Smith
Starting Member
5 Posts |
Posted - 2004-02-12 : 19:46:09
|
| Thanks Tara,I think I understand the process now.IanIanMGSmith |
|
|
|
|
How can I create a JOB with domain access?