Intermittent replication issue – Distribution agent failed to create temporary files
This will seem like a strange issue, because everything will work perfectly well for a period of time – but then it will all go wrong and you’ll get an error such as:
Error messages: The distribution agent failed to create temporary files in ‘C:\Program Files\Microsoft SQL Server\110\COM’ directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100) Get help: http://help/MSSQL_REPL21100
If you re-initialize the subscription, everything will work again and you’ll wonder what happened!
The fix for this is to grant write permissions to the folder to the distribution user. Things to bear in mind…
- You need to grant access on the machine acting as the distributor (often this is the publishing machine, but you can configure a different machine to perform distribution).
- You need to grant access to your replication distribution user (you are likely to have set up different users for distribution, log reader, merge, and snapshot tasks).
If you are scripting your permissions, here is the PowerShell…
# Special permission for repl_distribution_user $accessControlList = Get-Acl "C:\Program Files\Microsoft SQL Server\110\COM" $accessRule = New-Object system.security.accesscontrol.filesystemaccessrule("repl_distribution_user", "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow") $accessControlList.SetAccessRule($accessRule)