Tag: Distributor

Log reader fails with authentication error

On By Richard ImenesIn Error messagesLeave a comment

I ran into an issue with transactional replication setup that I thought I would mention here, as I spent quite some time to discover the cause.

In my setup the server SQL01 uses SQL02 as distributor. When I set up publication on a database in SQL01, the Log Reader Agent is created in the distributor instance. I used the same AD-account for the snapshot and connecting to the publisher.

  • Nothing fishy here.

The publication setup finished without a hitch, but I noticed that something was not quite working as intended.

Replication Monitor shows that Log Reader Agent is not happy.

Looking directly into the agent job logs I uncovered a surprising error:

Unable to start execution of step 2 (reason: Error authenticating proxy lab\repluser, system error: The user name or password is incorrect.)

Immediately I checked the password for the user, I changed the password to a simpler one, and triple checked that the account was valid. Then I checked usual Kerberos offenders like SPN and delegation. I tried to recreate the publication, and even re-establish the distribution setup. Nothing solved the issue.

The Event Viewer Security log did not make me much smarter either, but I was able to observe the error there as well.

  • “Unknown user name or bad password”… Not really helpful.

In the end I checked the service account for the Agent Service, and noticed something I had not seen before. The service user was defined as the full domain name “lab.int” rather than the short version “lab”.

The service will log in and work fine, but running jobs with different AD accounts seems to cause issues.
Change the domain for the service account, click “Apply” and restart the service as requested…

Fixing this small detail, made the Log Reader Agent much happier.

Order is restored in Replication Monitor land.

I was surprised to discover that the SQL Agent Service AD account actually was the culprit here, and since I spent a lot of time troubleshooting this odd issue, I thought it would be best to have it here as a reminder should I encounter this issue again. Below is the information from Event Viewer, so that it is searchable. 

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          13.04.2019 18.57.32
Event ID:      4625
Task Category: Logon
Level:         Information
Keywords:      Audit Failure
User:          N/A
Computer:      SQL02.lab.int
Description:
An account failed to log on.

Subject:
	Security ID:		lab\AgentSvc
	Account Name:		AgentSvc
	Account Domain:		lab
	Logon ID:		0xCA351F

Logon Type:			8

Account For Which Logon Failed:
	Security ID:		NULL SID
	Account Name:		repluser
	Account Domain:		lab

Failure Information:
	Failure Reason:		Unknown user name or bad password.
	Status:			0xC000006D
	Sub Status:		0xC000006A

Process Information:
	Caller Process ID:	0x3d8
	Caller Process Name:	C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Binn\SQLAGENT.EXE

Network Information:
	Workstation Name:	SQL02
	Source Network Address:	-
	Source Port:		-

Detailed Authentication Information:
	Logon Process:		Advapi  
	Authentication Package:	Negotiate
	Transited Services:	-
	Package Name (NTLM only):	-
	Key Length:		0

[truncated for brevity]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" />
    <EventID>4625</EventID>
    <Version>0</Version>
    <Level>0</Level>
    <Task>12544</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8010000000000000</Keywords>
    <TimeCreated SystemTime="2019-04-13T16:57:32.569470400Z" />
    <EventRecordID>3082</EventRecordID>
    <Correlation ActivityID="{668216C8-F1E0-0001-DA16-8266E0F1D401}" />
    <Execution ProcessID="556" ThreadID="6076" />
    <Channel>Security</Channel>
    <Computer>SQL02.lab.int</Computer>
    <Security />
  </System>
  <EventData>
    <Data Name="SubjectUserSid">S-1-5-21-1841389258-1596734532-2715390843-1106</Data>
    <Data Name="SubjectUserName">AgentSvc</Data>
    <Data Name="SubjectDomainName">lab</Data>
    <Data Name="SubjectLogonId">0xca351f</Data>
    <Data Name="TargetUserSid">S-1-0-0</Data>
    <Data Name="TargetUserName">repluser</Data>
    <Data Name="TargetDomainName">lab</Data>
    <Data Name="Status">0xc000006d</Data>
    <Data Name="FailureReason">%%2313</Data>
    <Data Name="SubStatus">0xc000006a</Data>
    <Data Name="LogonType">8</Data>
    <Data Name="LogonProcessName">Advapi  </Data>
    <Data Name="AuthenticationPackageName">Negotiate</Data>
    <Data Name="WorkstationName">SQL02</Data>
    <Data Name="TransmittedServices">-</Data>
    <Data Name="LmPackageName">-</Data>
    <Data Name="KeyLength">0</Data>
    <Data Name="ProcessId">0x3d8</Data>
    <Data Name="ProcessName">C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Binn\SQLAGENT.EXE</Data>
    <Data Name="IpAddress">-</Data>
    <Data Name="IpPort">-</Data>
  </EventData>
</Event>