3

I have a job, C:\foo.bat.

When I run the job from windows explorer it fails. When I right click on the file and click "Run as administrator..." the job passes.

Now when I setup the job in SQL Server Agent, it fails. I can't figure out how to run the job as Administrator.

John Oxley
  • 278
  • 1
  • 6
  • 19

3 Answers3

6

Rather than setting the SQL Server Agent Service to use a local admin account, which would essentially give local admin access to every job, you can use credentials and proxies which is a new feature in SQL 2005 in order to provide tighter security.

On your SQL instance:

  1. Under Security, right-click Credentials and click New Credential
  2. Fill in the credential information (Credential name = whatever, Identity is the account with local admin privileges to your machine, password = that accounts pwd, confirm password = enter it again), click Ok.
  3. Under the SQL Server Agent, expand Proxies
  4. Right-click on Operating System (CmdExec) and click New Proxy
  5. Give the proxy a name, select the credential you created in step 2, click Ok
  6. Click on Principles in the New Proxy window and select SQL Logins and roles that you wish to be able access this proxy
  7. Create a job
  8. Add a step to the job and set it to type Operating System (CmdExec)
  9. In the Run As drop-down, select the proxy you created in step 4 and 5.

That should do it for you. Much safer than making the whole SQL Agent service run as local admin.

squillman
  • 37,883
  • 12
  • 92
  • 146
0

Okay a very NOT nice way of doing it.

Step 1: Disable UAC

Done.

There must be a better way though that having to disable UAC for the whole machine.

John Oxley
  • 278
  • 1
  • 6
  • 19
0

See this link Selecting an Account for the SQL Server Agent Service You can set SQL Server Agent Service to use administrator's account, so there will be no problem to run that bat file.

Bogdan_Ch
  • 483
  • 1
  • 3
  • 12