replication security issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

replication security issue

I am testing a push transactional replication from SQL 2k5 to SQL 2K. The publishing db and distribution db are on the same computer which has SQL 2k5 installed. The subscriber (SQL 2K) is on a different server. I created both publisher and subscriber successfully, the publication and articles are also created successfully. During the publisher and subscriber setup, I use a windows account (as recommended) to connect to the snapshot agent and logreader agent, and publishing/subscrbing db , I make sure the domain account is the dbo for both the publishing db, distribution db and subscriber db, and it belongs to an administrator group so it can write to the local snapshot folder. When I start the snapshot agent, it failed and tell me the following: "Unable to start execution of step 2 (reason: Error authenticating proxy domainAsqlservice, system error: Logon failure: unknown user name or bad password.). The step failed." If I use ‘run under the SQL server agent service account’ for the authentication instead of windows authentication during the above mentioned setup, the snapshot will start successfully but this setup is recommended based on the security best practice. What do I miss while setting up the windows authentication for snapshot agent and logreader agent? wingman
Is there any proxy account setting up for replication agent use?
Check the following tables for details… select * from sys.credentials
select * from msdb..sysproxies
select * from msdb..sysproxylogin
select * from msdb..sysproxysubsystem
MohammedU.
Moderator
SQL-Server-Performance.com
I didn’t set up any proxy account for it. Is it necessary? I run your query and found no records on each of the select statements. Wingman
quote:Originally posted by MohammedU Is there any proxy account setting up for replication agent use?
Check the following tables for details… select * from sys.credentials
select * from msdb..sysproxies
select * from msdb..sysproxylogin
select * from msdb..sysproxysubsystem
MohammedU.
Moderator
SQL-Server-Performance.com

If you want to use proxy account then you need to setup I believe… Replication Proxies, Credentials and Subsystems
http://www.replicationanswers.com/Security2005.asp
MohammedU.
Moderator
SQL-Server-Performance.com
I don’t want to use a proxy account if not necessary. Is it required for the setup I mentioned in my original email?
quote:Originally posted by MohammedU If you want to use proxy account then you need to setup I believe… Replication Proxies, Credentials and Subsystems
http://www.replicationanswers.com/Security2005.asp
MohammedU.
Moderator
SQL-Server-Performance.com

]]>