Problem running distribution agent impersonating a Windows user other than SQL Agent Exec.

• cbachmann

  Mr or Mrs. 500

  Points: 588

  More actions

  June 29, 2006 at 11:29 am

  #169446

  I'm having a problem adding a push subscription to a transactional publication on SQL2005, Win 2003 server. I can't run the distribution agent as a user other than the user defined as the SQL Server Agent logon account.

  Here is my general setup:

  - SQLServer 2005 developer edition, with Windows Security (SQL Server security not enabled).

  - Running SQL Server Agent using a workgroup user name SQLAgentExec, this user is only a member of the group SQLServer2005SQLAgentUser$GALILEO-TEST1$MSSSLSERVER.

  - I've added a second Windows user that I wish to use to run as the distribution agent called ISIS_REPLICATION. This login is added to the SQL Server, and the user has been added to the distribution database and the publishing database, and made a db_owner for each. ISIS_REPLICATION is also a member of the PAL but is not in any Windows groups.

  - I'm making all changes via replication stored procedures through SQL Management Studio, while logged onto Windows as an administrator (user name GALILEO-TEST1\Bachmann).

  - Replicating to a Windows XP machine running SQLExpress.

  I've found that I can add the subscription and replicate to the client with no problem provided I permit the distribution agent to run under the context of the SQL Server Agent (user SQLAgentExec). I do this by using the two stored procedures sp_addsubscription and sp_addpushsubscription_agent.

  I don't specify @job_login or @job_password, meaning the distribution agent is run under the context of SQLAgentExec. Here are those scripts:

  ============

  exec sp_addsubscription

  @publication = N'ISISRegister',

  @subscriber = N'ral-chabac-wb\sqlexpress',

  @destination_db = N'ISIS',

  @subscription_type = N'Push',

  @sync_type = N'automatic',

  @article = N'all',

  @update_mode = N'read only',

  @subscriber_type = 0

  exec sp_addpushsubscription_agent

  @publication = N'ISISRegister',

  @subscriber = N'ral-chabac-wb\sqlexpress',

  @subscriber_db = N'ISIS',

  @job_login = null,

  @job_password = null,

  @subscriber_security_mode = 1,

  @frequency_type = 64,

  @frequency_interval = 0,

  @frequency_relative_interval = 0,

  @frequency_recurrence_factor = 0,

  @frequency_subday = 0,

  @frequency_subday_interval = 0,

  @active_start_time_of_day = 0,

  @active_end_time_of_day = 235959,

  @active_start_date = 20060627,

  @active_end_date = 99991231,

  @enabled_for_syncmgr = N'False',

  @dts_package_location = N'Distributor'

  GO

  ===============

  But of course this is not a best practice. When I try to setup the subscription using the @job_login and @job_password set to the login of ISIS_REPLICATION, the process doesn't work. Here are those scripts:

  ===============

  use [ISIS]

  exec sp_addsubscription

  @publication = N'ISISRegister',

  @subscriber = N'ral-chabac-wb\sqlexpress',

  @destination_db = N'ISIS',

  @subscription_type = N'Push',

  @sync_type = N'automatic',

  @article = N'all',

  @update_mode = N'read only',

  @subscriber_type = 0

  exec sp_addpushsubscription_agent

  @publication = N'ISISRegister',

  @subscriber = N'ral-chabac-wb\sqlexpress',

  @subscriber_db = N'ISIS',

  @job_login = N'galileo-test1\ISIS_REPLICATION',

  @job_password = N'rep',

  @subscriber_security_mode = 1,

  @frequency_type = 64,

  @frequency_interval = 0,

  @frequency_relative_interval = 0,

  @frequency_recurrence_factor = 0,

  @frequency_subday = 0,

  @frequency_subday_interval = 0,

  @active_start_time_of_day = 0,

  @active_end_time_of_day = 235959,

  @active_start_date = 20060620,

  @active_end_date = 99991231,

  @enabled_for_syncmgr = N'False',

  @dts_package_location = N'Distributor'

  GO

  ===============

  When I run them, I get this set of messages:

  ===============

  Job 'GALILEO-TEST1-ISIS-ISISRegister-RAL-CHABAC-WB\SQLEXPR-8' started successfully.

  Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.

  'GALILEO-TEST1\bachmann' is a member of sysadmin server role and cannot be granted to or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

  Job 'GALILEO-TEST1-ISIS-ISISRegister-RAL-CHABAC-WB\SQLEXPR-8' started successfully.

  ===============

  When I view the job activity monitor, I see that the GALILEO-TEST1-ISIS-ISISRegister-RAL-CHABAC-WB\SQLEXPR-8 is failing on step 2 (and step 3), step 2 is which is the step to start the distribution exe. I see this error msg for step 2:

  ===============

  Message

  Unable to start execution of step 2 (reason: Error authenticating proxy galileo-test1\ISIS_REPLICATION, system error: Logon failure: unknown user name or bad password.). The step failed.

  ===============

  I orginally believed this was a permissions problem gaining access to the SQL Server, but now I suspect that the user SQLAgentExec can't launch a process under the context of ISIS_REPLICATION. I think this because I can run distrib.exe using the credentials of ISIS_REPLICATION and the distribution runs without a problem. I do this via runas.exe to start cmd, from the cmd window, I browse to the directory containing distrib.exe, and then launch it using the cmd line options specified in step 2.

  Again, I suspect the problem is that the SQL Server Agent can't impersonate ISIS_REPLICATION, but I don't know how to absolutely proved this or fix it. Does anyone have an idea of what might be the issue/remedy?