April 15, 2010 at 7:49 am
Hi,
I tried to confgure the replication sql 2008 between 2 systems (i.e 2 sql server instances on 2 different machines).
Machine-A is acting as both Distributor and Publisher
Machine-B is acting as subscriber.
After configuring everything and once i open the replication monitor, i found replication failed. Pl find below error.
Unable to start execution of step 2 (reason: Error authenticating proxy NT AUTHORITY\NETWORK SERVICE, system error : Logon failure : unknown user name or bad password). The step failed.
and in Error details pane
It is showing The job failed. The Jon was invoked by user sa.
But i currently logged in as "sa" user. is that any problem???
Can anyone suggest me on this on how to fix this problem and why it happened so.
or do i need to use explicit domain accnt while specifying SQL Agent Security during the setup.
Thanks in Advance,
April 15, 2010 at 8:07 am
SQL Service and Agent Service has to run under a domain account. Check if that is the case in your set up
-Roy
April 15, 2010 at 10:25 pm
currently, it is running under NT AUTHORITY\NETWORK SERVICE account.
What does the above account mean?
Is that mandatory to give the domain account only?
April 16, 2010 at 1:14 am
Observations.
1.SQL Server Agent Service is running under domain accnt. It is configured in SQL Server Configuration Manager.
2.Restarted the SQL Server Agent and started successfully.
3.Logged in as "sa" and configured the replication in sql 2008.
4.Both Publisher and Subscriber both are sql 2008 standard editions with sp1 service pack.
5.But when i tried to launch the Replication Monitor , the log reader has failed and show below error message.
The job failed. The Job was invoked by User sa. The last step to run was step 3 (Detect nonlogged agent shutdown).
Unable to start execution of step2 (reason: Error authenticating proxy DOMAIN\uname,system error:Logon failure: unknown username or bad password.
Can anyone suggest me what needs to be done?
why the job is getting failed? Does the process does not impersonate the sql agent credentials eventhough it has been started by "sa" login??
April 20, 2010 at 2:22 am
It appears that Domain/uname is not sysadmin. If that is the case then you need to create proxy account
Or Grant Domain/uname an Admin access and ensure same is there on Publisher and Subscriber
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 20, 2010 at 9:16 pm
Thanks Mohan!
You are talking about proxy which is new to me.
How do we create a proxy for domain accnt???
Does the proxy have the sysadmin privilege and is mapped to domain account??
Would like to know how the proxy will be working?
Thanks in Advance.
April 21, 2010 at 2:07 pm
Proxy accounts corresponds to a security credential that has admin rights. You can refer http://msdn.microsoft.com/en-us/library/ms190698.aspx for creating Proxy
Proxy will resolve issue. In fact you have two choice either create proxy or grant your service account an admin access
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 23, 2010 at 1:26 am
Thank you
May 15, 2010 at 8:56 pm
Hi,
I connected to ssms using "sa" and i configured transactional replication on my personal RDC machine. For SQL Agent and all other Agents, i have given the domain account and paswword while configured in GUI. Everything worked fine and replication is working. But, i want to replicate the same replication on DEV environment with only change of server names (same domain account).
What i tried was in ssms (SQL 2008) on the Replication folder and generated the script for Create and drop replication.
All i have done was replace the server names with new servers names. But while executing the scripts on targets /new servers it is throwing an error password cannot be Null, so, i updated the password for domain account by right clickin the replication and update password options. Then i tried to start the replication , by jobs where failing...
Inside the job history, the errors showing as follows
010-05-14 09:58:04.112 Login failed for user 'sa'.,00:13:05,0,0,,,,0
010-05-14 09:56:03.549 Login failed for user 'sa'.,00:11:04,0,0,,,,0
010-05-14 09:54:02.985 Login failed for user 'sa'.,00:09:04,0,0,,,,0
I Also, done the same exercise on my RDC machine (original one). i dropped the replication and execute the create repl script.
Again same password proble. Then i wnet into the script and i can find the password for for domain login account, the passwords are set to NULL!!!! Why so, while configuring we have supplied all the passwords and it was working fine but we generate the scripts all the passwords it is showing it as NULL!!!
I cannot keep on setting up the repl in GUI mode for all servers involved. Right, what i thought was configure in one env and and make it a generic script by replacing the server names.
What would be the workaround for this problem???? Did i missed out any??? How to overcome this problem ?
Thanks in Advance.
May 15, 2010 at 9:01 pm
Do we need to have the same "sa" password for both the machines while configuring the transactional replication ????
May 17, 2010 at 1:20 am
When i execute the create replication which was earlier generated one, this was the error in ssms
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpushsubscription_agent, Line 194
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".
May 17, 2010 at 7:28 am
In SQL 2008, it is recommended to have to have domain account than SA.
There are things that you will need to do using GUI. Use your SQL Server configuration manager to change your services to run under Domain Name.
-Roy
May 18, 2010 at 9:32 pm
Hey Thanks ! I reconfigured the replication with a domain acccount and password.
It is working fine.
But i need one clarification, i generate the script. it is showing NULL password in the generate output file. Do we need to manually supply the password everytime we again configure the replication??? that is what am doing now. otherwise the above NULL password error is coming.
May 19, 2010 at 7:07 am
I do not think it will create a password when scripting out. But I am not 100% sure regarding that.
-Roy
May 20, 2010 at 7:15 am
Pl find the generated script.
The script contains password as NULL and when i execute the same script to reconfigure , it is throwing me an error saying cannot be NULL password. When i supplied the password for all the @password = 'wdf' place holder, then the script is working fine!
exec [SecurityDB].sys.sp_addlogreader_agent @job_login = N'CSD\sql_service', @job_password = 'NULL', @publisher_security_mode = 1
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply