6

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?

6

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

4
  • 1
    is this SQL Server Agent(MSSQLSERVER)?, Log on as: built in account: Network Service – RagnaRock Nov 25 '11 at 10:11
  • 1
    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
4

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.

0

Use this query to find the job for distribution

SELECT a.publication, j.name
  FROM distribution.dbo.MSdistribution_agents a
       JOIN msdb.dbo.sysjobs j ON a.job_id = j.job_id

Check for the credential on which the job executes and then grant permission to the directory for that user account. In most cases it will be executed by the user account created for distribution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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