SQL Agent Job Step Run As

• dan-572483

  SSCoach

  Points: 15870

  More actions

  January 5, 2011 at 4:49 pm

  #141988

  I have a SQL Agent job that needs to run a script whoch copies backup files from a production server to the local server. In developing the script I connected to the remote server through an admin share (\\d$\MSSQL\Backups\backupfile.bak). This works when I'm logged in with my user account, but not when the job runs under SQL Agent because the account used SQLAgent is not in the Admin group on the remote server.

  Possible solutions to this include:

  1) Put the SQL Agent account into the local admin group on the remote server.

  2) Create file shares on the folders containing the files on the remote server and give access to the account used by SQLAgent.

  3) Run the job, or at least the step, using an account other than the SQL Agent account and does exist in the remote server Admin group.

  I would prefer to do 3) because 1) and 2) require changes to a production server and extra steps to get approval from mnagement. Is 3) possible without changing the SQLAgent user?

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 5, 2011 at 5:31 pm

  #1269827

  I don't have 2005 available right now, but this should work I think...

  You create a credential first, then create an agent proxy that will use that credential.

  Once these are set up you can choose the proxy account as the "Run As" account in a job step.

  USE [master]

  GO

  CREATE CREDENTIAL [agent_cred] WITH IDENTITY = N'DOMAIN\USER', SECRET = N'PassWord'

  GO

  USE [msdb]

  GO

  EXEC msdb.dbo.sp_add_proxy @proxy_name=N'agent_proxy',@credential_name=N'agent_cred',

  @enabled=1

  GO

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'agent_proxy', @subsystem_id=3 -- CmdExec

  GO

  Here is the documentation for SQL Agent Stored Procs : http://msdn.microsoft.com/en-us/library/ms187763(v=SQL.90).aspx

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]