July 2, 2014 at 8:59 am
Hi
Publisher: SQL 2008
Distributor: upgraded from SQL 2012 to SQL 2014
Subscribers: SQL 2008 / 2012
Current error on transactional replication:
The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\120\COM' directory. System returned errorcode 5.
'C:\Program Files\Microsoft SQL Server\120\COM' does not exist on the subscriber, so I can not give the SQL server agent account permission to a folder that don't exist
Any ideas are welcome
July 10, 2014 at 3:00 pm
Google is your friend.
http://support.microsoft.com/kb/956032
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 9, 2014 at 4:56 am
Error Code 5 Signifies Access denied. Account which is being used to run SQL Server Agent does not have write permission on the COM folder.
Add Write permission on the COM folder for the Agent Account and check again.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
September 28, 2015 at 11:52 am
I just went through this annoying issue and figured I would share my findings as none of the top hits in google, including this thread, were entirely correct.
The error I encountered was identical to the one being reported by everyone else, however, giving the sql agent service account permissions was not the solution; The fact that it worked for many was a coincidence, imo.
If you read the error again, you'll notice it is complaining about the "distribution agent". This is a file called distrib.exe in the same folder the error is complaining about (C:\Program Files\Microsoft SQL Server\110\COM). I was not able to find a service in the services.msc snap-in running from that executable so I used process monitor to find that distrib.exe was actually being spawned under a different service account than sql server agent. This is likely unique to my company's setup, but potentially notable to those following similar practices.
Once I gave write/modify permissions to the account running distrib.exe for the folder in the error message, the subsequent attempt to initialize replication completed successfully.
February 22, 2016 at 4:03 pm
Hi,
I found the KB 956032 article, too. We just rebuilt our SQL Server instance from (SQL 2008 R2 on Win 2008 R2) to (SQL 2014 SP1 on Win 2012 R2). We dropped all replication and distribution from the 2008 R2 instance before shutting down the old server, and we brought replication up from scratch using our well established scripts on the new server. Notably, we did not have this problem when we rebuilt our Dev, Staging and QA servers. All the servers were all deployed from the same Prepared SQL virtual machine template.
KB 956032 mentions "... use the "Distribution Profile for OLEDB streaming" profile for the Distribution Agent..." We are not using that (I double checked).
Also, the "account that is running SQL Server Agent" seemed wrong to me, so I used the account specified for sp_addpushsubscription_agent.@job_login. That brought my replication back online.
Sincerely,
Daniel
March 1, 2016 at 7:32 am
kahmeal (9/28/2015)
I just went through this annoying issue and figured I would share my findings as none of the top hits in google, including this thread, were entirely correct.The error I encountered was identical to the one being reported by everyone else, however, giving the sql agent service account permissions was not the solution; The fact that it worked for many was a coincidence, imo.
If you read the error again, you'll notice it is complaining about the "distribution agent". This is a file called distrib.exe in the same folder the error is complaining about (C:\Program Files\Microsoft SQL Server\110\COM). I was not able to find a service in the services.msc snap-in running from that executable so I used process monitor to find that distrib.exe was actually being spawned under a different service account than sql server agent. This is likely unique to my company's setup, but potentially notable to those following similar practices.
Once I gave write/modify permissions to the account running distrib.exe for the folder in the error message, the subsequent attempt to initialize replication completed successfully.
I had the same problem and this helped me resolve it. The publication was using a different account than what was listed as the SQL Agent account on the distribution server. It was using the Log Reader agent account that was given during its creation. I would have been able to resolve this a lot sooner if SQL Server reported the correct account that was actually running the distribution process. I gave that account the same modify rights as the SQL Server Agent the error went away.
March 13, 2016 at 8:42 pm
kahmeal (9/28/2015)
I just went through this annoying issue and figured I would share my findings as none of the top hits in google, including this thread, were entirely correct.The error I encountered was identical to the one being reported by everyone else, however, giving the sql agent service account permissions was not the solution; The fact that it worked for many was a coincidence, imo.
If you read the error again, you'll notice it is complaining about the "distribution agent". This is a file called distrib.exe in the same folder the error is complaining about (C:\Program Files\Microsoft SQL Server\110\COM). I was not able to find a service in the services.msc snap-in running from that executable so I used process monitor to find that distrib.exe was actually being spawned under a different service account than sql server agent. This is likely unique to my company's setup, but potentially notable to those following similar practices.
Once I gave write/modify permissions to the account running distrib.exe for the folder in the error message, the subsequent attempt to initialize replication completed successfully.
I wanted to thank you for this post and the idea, kahmeal. I used Process Explorer (https://technet.microsoft.com/en-us/sysinternals/bb896653) [shout out to Mark Russinovich] to do much the same.
I read the error and came to the same conclusion as you. It was the Distributor Agent Account, but until I read this post all of the suggested methods for figuring out the "who" was not as simple.
I moved several Databases from 2008 R2 to 2012 and no matter what I did I could not get the error to stop for this one Publication. After some digging I come to find out that the other Publications that are working without the same level of permissions. I simply cannot explain that. Based on what I have been reading on this post as well as many others none of these publications should have been working.
To make this post easier to find, I am pasting the error message that I have been seeing over and over when troubleshooting this so that other can find this post.
"The distribution agent failed to create temporary files in C:\Program Files\Microsoft SQL Server\110\COM directory. System returned errorcode 5."
Keep in mind, boys and girls, all you need to do is set the security on the C:\Program Files\Microsoft SQL Server\110\COM for each Domain or Windows Security account that is running DISTRIB.exe for Full Access rights to this directory and all subdirectories. If you use either Process Monitor or Process Explorer to figure out which accounts need these rights you will be much happier.
Maybe the rights could be reduced somewhat, but when you are this granular it should be fine.
One more thing 😀 . If you have Anti-virus Software installed on your Database Server, be sure to exclude the C:\Program Files\Microsoft SQL Server\110\COM directory from active scans/scan on change type activities. You'll be glad that you did.
Regards, Irish
June 21, 2016 at 6:49 am
SQL Server 2012 SP2 CU9 also throws this error and it does not appear to have anything to do with permissions or with OLEDB streaming profiles. I am using the default distribution profile and I set the COM folder to EVERYONE:FULL for testing purposes and it still throws the error. A/V was not a factor, either.
In the end I had to drop and re-create the subscriptions using the same settings I used in the first place and the error went away. This is probably only temporary as it seems to crop up at least once a month, and so far, on a different publication each time.
I can't correlate the distribution agent failure with any events in the security event log.
I think that error handler is catching something that it is not intended to catch.
August 29, 2017 at 12:24 pm
I had the same problem with my production environment. Just as Irish experienced, some publications were working, but others were failing. That's the only part that doesn't make sense. Why did some of them succeed?
July 10, 2018 at 5:35 am
kahmeal - Monday, September 28, 2015 11:52 AMI just went through this annoying issue and figured I would share my findings as none of the top hits in google, including this thread, were entirely correct.The error I encountered was identical to the one being reported by everyone else, however, giving the sql agent service account permissions was not the solution; The fact that it worked for many was a coincidence, imo. If you read the error again, you'll notice it is complaining about the "distribution agent". This is a file called distrib.exe in the same folder the error is complaining about (C:\Program Files\Microsoft SQL Server\110\COM). I was not able to find a service in the services.msc snap-in running from that executable so I used process monitor to find that distrib.exe was actually being spawned under a different service account than sql server agent. This is likely unique to my company's setup, but potentially notable to those following similar practices. Once I gave write/modify permissions to the account running distrib.exe for the folder in the error message, the subsequent attempt to initialize replication completed successfully.
3 years later, this post saved my time. I was thinking why god it is not working, I gave every permission to SQL agent account:) Just opened the task manager, found the user who runs the process and nailed it.
Cheers
November 19, 2018 at 2:23 pm
I was able to find the account running the distribution as the error was reporting using the process explorer. I added full rights to the user account and the error was fixed.
But, what triggered this? I didn't install any update or even restarted the server...
January 20, 2020 at 3:46 pm
Thanks for this.
Adding just "write" permissions for the distributor agent on the folder mentioned in the error (on the distribution server) fixed this problem for me.
Stu
February 28, 2020 at 3:30 pm
Just to add, I had a case where a job step of the replication job was running with a different credential, not with the login that was running the process. So I checked the login behind that credential and granted permission to that login => problem solved.
August 3, 2020 at 8:29 pm
For "kahmeal"
perfect answer: Once I gave write/modify permissions to the account running distrib.exe for the folder in the error message, the subsequent attempt to initialize replication completed successfully.
Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply