SQL Server Agent Account

Question

SQL Server Agent Account

Carl Rimmer

SSCarpal Tunnel

Points: 4068
More actions
June 30, 2005 at 2:42 am

#89865

(Also logged on SQL Server Agent Forum)
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

Viewing 6 posts - 1 through 5 (of 5 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

The minute you need access to another server in a domain you need a DOMAIN account. Everything you run under SQL Agent is going to use the AGENT account unless as you said before you enable the proxy account. The proxy allows people that are not the owner of the Job to execute them under that "Domain account". But it also let them execute xp_cmdshell and activeScripts in Jobs (may it is ok fo you, may be not)

In summary if you still have to create a DOMAIN account to act as the proxy, why not just simple change the Agent account to a domain account?

* Noel

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

Thanks for the reply.

Changing the Agent account to a domain account won't cause any problems will it?

From what I know, If you change the Account via Enterprise manager you don't need to set any permissions manually do you?

Carl

noeld SSC Guru Points: 96590 More actions · Answer 3

Correct!

You should not have any problems (with EM) , just make sure that account can reach the shares on the remote server and you should be OK

* Noel

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

One other point. When changing the account for the SQL Agent service you will need to stop and restart the service. The same hold true for the SQL Server service. But the SQL Agent service stop/restart is by far less disruptive.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

noeld SSC Guru Points: 96590 More actions · Answer 5

when you prform the change through EM you are prompted for the restart automatically

* Noel