Replication procs missing!

This mystery often starts with the land of Replication Montor being very unhappy, providing a lot of red errors.

Unhappy-land in Replication Monitor can in this instance be relatively easily fixed.

If you manage to keep your cool and look deeper into the errors, you would probably see something like this:

Could not find stored procedure 'sp_MSdel_SalesCustomerTransactions'. (Source: MSSQLServer, Error number: 2812)

Something has happened with the procedures in the replicated database. It is not unlikely that someone with a bit too much access has done some tidying, or maybe a deployment has cleaned out the procedures needed.

Luckily it is not too hard to fix once you know what to do:

USE WideWorldImporters
GO
EXEC sp_scriptpublicationcustomprocs @publication='Replication'
GO

The procedure sp_scriptpublicationcustomprocs should be run in context of the database you have issues with (so not the replicated database) and you need to specify the publication name.

You will then be presented with the wonderful list of all the procedures that should exist in your replica database. If you know which are missing, you can go ahead and execute those, and if you don’t have a clue, you could run the whole script. Note that if you ever made any changes to the procedures, you need to remember what those changes was and apply those again, as the procedure will create the default replication procedures.

How does the sp_scriptpublicationcustomprocs work? In case you are curious, you can look at the blog from Percy Reyes to find more information (linked below).

It reads the replication metadata from your publication and executes inside another replication stored procedures such as sp_scriptinsproc, sp_scriptdelproc (or sp_scriptxdelproc) and sp_scriptupdproc (or sp_scriptxupdproc) for each article in order to generate the INSERT, UPDATE and DELETE procedures.

Percy Reyes

Sources

Log reader fails with authentication error

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.

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.

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>

Disagreeable WMI Provider

Lets start with a simple issue that I have struggled with a few times. I try to start SQL Server Configuration Manager and is met with this puzzling message:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]
SQLServer-WMIError
Who cannot appreciate a error message blaming someone else for their mess?

The Internet list many culprits and most agree that WMI is not to blame this time. Exactly what causes the error seems to be somewhat random. Someone blames 32 vs. 64-bit mixed installations. I have experienced it after patching or installing multiple different instances on the same server, or even just patching an instance. Lets all agree it is caused by change of some kind.

Solution

Usually this issue is rather easy to resolve. Open CMD as administrator and type (copy) the following (take care to replace 000 with your SQL Server version code.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\000\Shared\sqlmgmproviderxpsp2up.mof"

If you have the simple problem, then this will resolve your issue, and it might look something like the screenshot below. Your luck might vary. If the the easy to remember file name sqlmgmproviderxpsp2up.mof is not in the catalog, you might need to search a bit on your server to see if you have it lying around somewhere else.

SQLServer-WMIError-solve
If you do not recall version number, there is a list below. However you are always welcome to do as me and impress someone looking over your shoulder by trying and failing a few times.

SQL Server list of versions

200080
200590
2008100
2008R2105
2012110
2014120
2016130
2017140
2019150

Sources