June 30, 2005 at 2:40 am
I am attempting to set up a DTS package that transforms data from one of our SQL Servers to another. I am attempting to use trusted authentication. The only problem is our SQL Sever Agent runs as a local account on all of our SQL Servers.
Is there any way (besides changing our SQL Server Agent to run as a domain account) to get this to work?
I know there is a SQL Proxy account but don't know whether this is capable of doing what we need. As far as I am aware the SQL Proxy account will allow non-sysadmin users to run DTS packages.
Any would would be appreciated.
Carl
July 4, 2005 at 8:00 am
This was removed by the editor as SPAM
July 8, 2005 at 2:51 pm
The SQL Server Agent is useless on the local account and so many services depend on it. In an Enterprise the SQL Server Agent needs a service Account with Domain Admin permissions. That said try the links below to configure the proxy account. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
July 8, 2005 at 3:59 pm
Whoa. No Domain Admin privs. That's not necessary. And it's a bad security practice. Nothing should run as Domain Admin unless it's absolutely impossible to avoid.
If the job is owned by an accunt that's not a member of the sysadmin role, it will attempt to execute under the context of whatever the proxy account is set to (it's not set by default). Set the proxy account to a domain user account. Give that domain account the appropriate rights on the target SQL Server.
K. Brian Kelley
@kbriankelley
July 12, 2005 at 7:27 pm
Definately no Domain Admin service accounts, but you shouldn't use a local system account either. The proxy account will get the agent job to run but there are many other functions (mail, replication, backup to network drives, etc) that won't work under a local system account.
You can set up a separate domain login just for SQL Server and use that account for all SQL Server & SQL Server Agent service logins. It is easiest if this account is a local administrator on each SQL Server, but the Setting up Windows Services Accounts topic in BOL details the exact permissions required if this is an issue.
July 12, 2005 at 8:17 pm
Agreed. Typically you wouldn't want to use the local system account because it has administrative rights (actually a bit more). This is not necessary as of SP3. However, there are cases when you'd normally want to to use a local account because you don't want to allow SQL Server Agent to have network access. In that case you don't use a domain login because such an account could always gain network access.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply