none
chaining jobs from 2000 to 2008

    Question

  • If I start with a sqlagent job on a 2000 server and wish to start a 2008 job from it upon completion, how can I plumb the relationship between the jobs?  I know the 2008 server is STD Edition.  Does any special infrastructure need to be in place? 

    Tuesday, April 13, 2010 2:28 AM

Answers

  • This should require just a couple of things to set up.  There is more than one way to do this, but for simplicity:

    1. Define a linked server from the 2000 to the 2008 server.
    2. Define the security mapping from the 2000 to the 2008 server.  This can be a trusted connection via delegation for a Windows login or can be mapped to use a specific SQL Server login on the 2008 server.
    3. As the last step of your 2000 job run:
      exec RemoteJob.msdb.dbo.sp_start_job @job_name='Name of the 2008 job'

    The security context that the 2000 job is running under will need to have rights to start a job on the 2008 server.  One way to do this on the 2000 server is:

    USE master
    
    EXEC master.dbo.sp_addlinkedserver @server = N'RemoteJob', 
       @srvproduct=N'Link', @provider=N'SQLNCLI', 
       @datasrc=N'USSRV115', @catalog=N'msdb'
    
    EXEC master.dbo.sp_serveroption @server=N'RemoteJob', 
       @optname=N'rpc', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'RemoteJob', 
       @optname=N'rpc out', @optvalue=N'true'
    
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteJob', 
       @locallogin = N'Domain\Login', 
       @useself = N'False', @rmtuser = N'RemoteJobStart', 
       @rmtpassword =N'!q@w#e4R5T6Y'

    Then on the 2008 server, create the login RemoteJobStart with the same password that you defined in the linked server.  Notice that this linked server is being set up so that ONLY 'Domain\Login' can use the RemoteJobStart account.  (You can change this, of course.)

    CREATE LOGIN RemoteJobStart WITH PASSWORD = N'!q@w#e4R5T6Y'
    
    USE MSDB
    CREATE USER RemoteJobStart FROM LOGIN RemoteJobStart
    EXEC sp_addrolemember 'SQLAgentOperatorRole', 'RemoteJobStart'
    

    Use a different password, of course, than the one I used. 

    This should be enough to make it work.

    RLF

     

     

    • Proposed as answer by LekssEditor Tuesday, April 13, 2010 9:05 PM
    • Marked as answer by db042188 Wednesday, April 14, 2010 7:35 PM
    Tuesday, April 13, 2010 1:37 PM
  • Some people, DBAs and others, do not like them.  I think twice before using them, but if that is the best answer, I will go ahead.  If you are not able to use a linked server and the restricted connection to the SQL login on the target server, then another way is to create a credential and proxy on the source server:

    USE [master]
    CREATE CREDENTIAL RemoteJobCred WITH IDENTITY = N'domain\login', 
          SECRET = N'domain\login''s password'
    GO
    USE [msdb]
    EXEC msdb.dbo.sp_add_proxy @proxy_name=N'RemoteJobProxy',
    	@credential_name=N'RemoteJobCred', @enabled=1
    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'RemoteJobProxy', 
    	@subsystem_id=3 -- Operating System commands
    GO

    Then on your SQL Agent job create a final Operating System step that runs something like this as a trusted connection.  In this case, the 'domain\login' needs rights to start the job:

    SQLCMD  -Q "exec msdb.dbo.sp_start_job @job_name='Name of the 2008 job'" -S RemoteServer -E

    This execution of SQLCMD will start the job on the other server by connecting as the identity logon of the credential used by the proxy.  :-)

    HTH,
    RLF

    • Marked as answer by db042188 Wednesday, April 14, 2010 7:34 PM
    Wednesday, April 14, 2010 6:59 PM

All replies

  • This should require just a couple of things to set up.  There is more than one way to do this, but for simplicity:

    1. Define a linked server from the 2000 to the 2008 server.
    2. Define the security mapping from the 2000 to the 2008 server.  This can be a trusted connection via delegation for a Windows login or can be mapped to use a specific SQL Server login on the 2008 server.
    3. As the last step of your 2000 job run:
      exec RemoteJob.msdb.dbo.sp_start_job @job_name='Name of the 2008 job'

    The security context that the 2000 job is running under will need to have rights to start a job on the 2008 server.  One way to do this on the 2000 server is:

    USE master
    
    EXEC master.dbo.sp_addlinkedserver @server = N'RemoteJob', 
       @srvproduct=N'Link', @provider=N'SQLNCLI', 
       @datasrc=N'USSRV115', @catalog=N'msdb'
    
    EXEC master.dbo.sp_serveroption @server=N'RemoteJob', 
       @optname=N'rpc', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'RemoteJob', 
       @optname=N'rpc out', @optvalue=N'true'
    
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteJob', 
       @locallogin = N'Domain\Login', 
       @useself = N'False', @rmtuser = N'RemoteJobStart', 
       @rmtpassword =N'!q@w#e4R5T6Y'

    Then on the 2008 server, create the login RemoteJobStart with the same password that you defined in the linked server.  Notice that this linked server is being set up so that ONLY 'Domain\Login' can use the RemoteJobStart account.  (You can change this, of course.)

    CREATE LOGIN RemoteJobStart WITH PASSWORD = N'!q@w#e4R5T6Y'
    
    USE MSDB
    CREATE USER RemoteJobStart FROM LOGIN RemoteJobStart
    EXEC sp_addrolemember 'SQLAgentOperatorRole', 'RemoteJobStart'
    

    Use a different password, of course, than the one I used. 

    This should be enough to make it work.

    RLF

     

     

    • Proposed as answer by LekssEditor Tuesday, April 13, 2010 9:05 PM
    • Marked as answer by db042188 Wednesday, April 14, 2010 7:35 PM
    Tuesday, April 13, 2010 1:37 PM
  • thanks Russel.  I tried executing sp_start_job from msdb on the 2000 server (specifying target server name and job name) and got an error saying that the job didnt exist.    This was before following your instructions (just in case we already had the infrastructure).

    So that I understand, we arent adding a new windows user, just allowing an existing one to use a LOGIN object on the target to run the job?

    Also, isnt there a down side to creating linked servers in sql?

    • Edited by db042188 Wednesday, April 14, 2010 4:19 PM having trouble with msdn today
    Wednesday, April 14, 2010 4:01 PM
  • Correct, the approach I outlined maps the appropriate domain login to a new SQL Server login on the target machine.   They way I defined it, the domain login is the only one that can use the new SQL Server login through the linked server.

    The new SQL Server login is put in the SQLAgentOperatorRole so that it can do job operations for any job on that server, without being made an administrator of the server.  

    If the remote job is owned by the new SQL Server login (RemoteJobStart in my example) then the login only needs to be in SQLAgentUserRole.  Members of the SQLAgentUserRole role only can do things to jobs that the member owns.

    Linked servers are the way to communicate server to server, although there can be issues depending on how they are used.  In this case, however, all you are doing is running a remote stored procedure to start a job.  As soon as the job starts, the stored procedure is finished.  It does not wait for the job to complete.  So there should be no problem.

    RLF

    PS - I am having trouble with msdn today also.

    Wednesday, April 14, 2010 5:09 PM
  • Thanks Russel.  I mean in general, arent some dbas opposed to linked servers because of some more general reason?  A reason that would bite us outside of this exercise?  After all, the linked server stays around after I create it, thus exposing us to whatever it is that some dbas dont like about them. 
    Wednesday, April 14, 2010 5:29 PM
  • Some people, DBAs and others, do not like them.  I think twice before using them, but if that is the best answer, I will go ahead.  If you are not able to use a linked server and the restricted connection to the SQL login on the target server, then another way is to create a credential and proxy on the source server:

    USE [master]
    CREATE CREDENTIAL RemoteJobCred WITH IDENTITY = N'domain\login', 
          SECRET = N'domain\login''s password'
    GO
    USE [msdb]
    EXEC msdb.dbo.sp_add_proxy @proxy_name=N'RemoteJobProxy',
    	@credential_name=N'RemoteJobCred', @enabled=1
    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'RemoteJobProxy', 
    	@subsystem_id=3 -- Operating System commands
    GO

    Then on your SQL Agent job create a final Operating System step that runs something like this as a trusted connection.  In this case, the 'domain\login' needs rights to start the job:

    SQLCMD  -Q "exec msdb.dbo.sp_start_job @job_name='Name of the 2008 job'" -S RemoteServer -E

    This execution of SQLCMD will start the job on the other server by connecting as the identity logon of the credential used by the proxy.  :-)

    HTH,
    RLF

    Wednesday, April 14, 2010 6:58 PM
  • Some people, DBAs and others, do not like them.  I think twice before using them, but if that is the best answer, I will go ahead.  If you are not able to use a linked server and the restricted connection to the SQL login on the target server, then another way is to create a credential and proxy on the source server:

    USE [master]
    CREATE CREDENTIAL RemoteJobCred WITH IDENTITY = N'domain\login', 
          SECRET = N'domain\login''s password'
    GO
    USE [msdb]
    EXEC msdb.dbo.sp_add_proxy @proxy_name=N'RemoteJobProxy',
    	@credential_name=N'RemoteJobCred', @enabled=1
    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'RemoteJobProxy', 
    	@subsystem_id=3 -- Operating System commands
    GO

    Then on your SQL Agent job create a final Operating System step that runs something like this as a trusted connection.  In this case, the 'domain\login' needs rights to start the job:

    SQLCMD  -Q "exec msdb.dbo.sp_start_job @job_name='Name of the 2008 job'" -S RemoteServer -E

    This execution of SQLCMD will start the job on the other server by connecting as the identity logon of the credential used by the proxy.  :-)

    HTH,
    RLF

    • Marked as answer by db042188 Wednesday, April 14, 2010 7:34 PM
    Wednesday, April 14, 2010 6:59 PM
  • thanks, and I suppose if we're really desparate we can kick off ssis somehow to do something on one server based on things happening on another.  I'll mark your posts as answers.
    Wednesday, April 14, 2010 7:33 PM