Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm testing transactional replication between 4 databases, 2 in one machine(Virtual) and 2 in other(also virtual). None of my virtual machines is running an anti-virus.

It's all working as expected, unless I try to insert a blob of considerable size (65mb in this case) if I insert a blob of 3kb works just fine.

I'm having the following error "The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5".

This problem is documented here.And I already changed the settings of that folder so that is not read-only anymore (right click on the folder->properties and changed the read only-attribute) I'm not sure if this is the right way to grant permissions to do it.

The reason I say I have no anti-virus running is because I also read that was one source for this problem, as the antivirus might block the folder while inspecting it.

Also tried to change Distribution Profile for OLEDB streaming, that also didn't work..

What else can I do to solve this problem?

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

Error 5 is an NTFS permissions problem. What account is the SQL Agent service on the distributor running under?

share|improve this answer
    
is this SQL Server Agent(MSSQLSERVER)?, Log on as: built in account: Network Service –  RagnaRock Nov 25 '11 at 10:11
    
Changed it to "Local System", it seems to have solved the problem :D tks –  RagnaRock Nov 25 '11 at 10:40
    
The network service account wouldn't have write access to those folders by default. The Local System account does which is why it is working now. –  mrdenny Nov 25 '11 at 18:17
    
This is a HUGE help... I've setup p2p replication in Windows Azure (SQL 2012) - and my tests of large varchar(max) columns was failing... for this very reason :) –  Timothy Khouri Sep 15 '12 at 23:07
add comment

The error was caused because the SQL Server service could not write to C:\Program Files\Microsoft SQL Server\100\COM.

Changing the service account to Local System will solve this issue but also give too much un-needed rights to SQL Server, so you should set the service to run using a user account (either local or domain), put this account into the SQLServerMSSQLUser group, and give this group WRITE permissions on this folder (it is not the same as unticking "read-only" on folder properties).

There is no problem using Local System for development/tests environments, but please consider a more secure option on production servers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.