Re: SQL Server 2000 Replication Agents Credentials

Hi Hilary,

Thanks for your prompt response.

I investigated it further, and ran a trace on the three servers
(publisher, distributor and subscriber) to find out which agent is
using which login.

Apparently, the snapshot agent IS using sql login to connect to the
publisher and to the distributor although it is not specified in its
command line.

The pull distribution agent (running on the subscriber) IS using sql
login to connect both to the distributor and to the subscriber
although only the subscriber sql login is specified in the command
line.

Finally, the log reader agent ISN'T using sql login to connect to the
publisher, but rather the SQL Agent service account. In order to
override it, I change the command line parameters in the job step.

Do you (or anyone else) know where SQL Server 2000 saves the sql
logins used by the replication agents?

Also, why isn't the log reader using sql login like the rest of the
agents?
I know I don't directly tell it to use sql login. Is there any way to
manipulate the log reader agent using stored procedures? For instance,
how do I tell it to use a schedule other than the default (Start with
sql server)? Now I simple change the job schedule and remove the "-
Continuous" flag in the command line parameters.

Thanks in advance,
R. Green

On Feb 6, 1:57 pm, "Hilary Cotter" wrote:
You can run the binaries from the command line, or write a program using dmo
or the replication activex controls and set the accounts here.

--
Hilary Cotter

Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com




Hi,

I have a replication topology as follows: sql server 2000 sp4
publisher on one server, sql server 2000 sp4 distributor on another
server and sql server 2000 sp4 subscriber on another server.

I execute sp_addpullsubscription_agent (on subscriber) and set all the
security_mode parameters to 0 (zero) and provide logins and passwords
to distributor and subscriber.

Then in the distribution agent job, the command line parameters are as
follows:
-Publisher pubsrv -PublisherDB [mydb] -Publication [mydb subsrv] -
Distributor [distsrv] -SubscriptionType 1 -Subscriber [subsrv] -
SubscriberSecurityMode 0 -SubscriberLogin [sa] -
SubscriberEncryptedPassword [xxxx] -SubscriberDB [mydb_sub]

Using profiler, I see that it connects to the distributor using the
windows credentials the SQL Server agent on the subscriber is using.

Is there any way to change the way the distribution agent connects to
the distributor other than changing it in the job step?

Also, is there any way to even set the credentials for the snapshot
and the log reader agents other than in the job steps?

Thanks in advance,
R. Green

.

Relevant Pages

  • Re: SQL Server and Agent Accounts
    ... authority\Network service" isn't a member of SQL server sysadmin. ... Distributor, the user need to be a member of the sysadmin fixed server ... Replication implements login security by requiring a user to have a valid ... On the Microsoft Windows 98 operating system, SQL Server Agent and the ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL User SID format?
    ... When you move databases to another server, you can remap users to logins ... capability to remap a user to a login with new ALTER USER syntax - I ... The new ALTER USER syntax works for remapping both SQL and Windows ... this means you already have access to a SID from the syslogins catalog; ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server on XP Home Network
    ... The sa account is the system administrator "God" account witihin SQL ... This account is a SQL-Server login. ... You might be able to use Enterprise Manager to go in and create a new SQL ... Select the "SQL Server Authentication" ...
    (microsoft.public.sqlserver.setup)
  • RE: Replication Error
    ... account that the SQL Agent runs under. ... When I setup replication two jobs are setup within sql server server. ... step of each job (Snapshot Agent startup message, ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL Server and Agent Accounts
    ... I suggest that you refer to the information in SQL ... The SQL Server Agent service at the client should not use ... SQLAgent account is the security context under which the Snapshot Agent, ...
    (microsoft.public.sqlserver.replication)