none
The distribution agent failed to create temporary files

    Question

  • Hello room,

    I run into errors of "The distribution agent failed to create temporary files" on my SQL Server 2008 remote distribution.

    There is an article posted by Microsoft to fixed this issue.  http://support.microsoft.com/kb/956032

    I'm not sure how to assign or grant wirte permisson to the COM folder fro the account that is running the SQL Server Agent service.

    How do I grant the Distribution Agent to have an access COM folder?

    Please help and advice.

    Thanks
    Ediwn
    None
    Monday, May 11, 2009 2:45 PM

All replies

  • Perhaps you are thinking that this is something done from within SQL Server? It isn't. It is a security set at the file system level. All you need is the account that'll run the distribution agent and then in windows explorer right-click the folder, properties and security to enter the area where you add this particular user and grant him the write access to this directory.
    HTH,
    Paul Ibison

    Monday, May 11, 2009 3:57 PM
  • You also need to make sure that the replication agent has access to the REPLDATA directory where your snapshots are being writen.
    Douglas Taft DBA\DBD
    Monday, May 11, 2009 4:21 PM
  • Paul, Yes, I do know that I need to grant the remote distributor agent a permission to access the COM’s folder. I already assigned and granted Database Engine, SQL Server Agent, etc to have (Read & Execute) On COM folder but I am still getting those errors. How can I find out the remote distribution agent name in the RDN?
    None
    Monday, May 11, 2009 4:38 PM
  • OK - just need to check a few things first: is the subscriber running as a PULL or PUSH.
    Is the distribution agent set to use a proxy or set to use the SQL Agent account?
    Thanks,
    Paul
    Tuesday, May 12, 2009 8:39 AM
  • Paul,

    Thanks in advance for your help and advice.    It's transaction pull replication. I'm not sure the distribution agent.  

    How can I find out the distribution agent  run on SQL Agent credential?

    GBU
    Edwin
    None
    Tuesday, May 12, 2009 1:26 PM
  • If you right-click the Local Subscription (pull) and select properties, you'll get a form with all the data. In the security section there are settings for the Agent process account and the Distributor connection. These can be set either to a windows account or to "Impersonate agent process account (Windows Authentication)". If the account is explicit then fine. If it is set to "Impersonate agent process account (Windows Authentication)" then you'll need (on the subscriber) to go to the control panel and have a look at the SQL Server Agent service and get the account from there. Once you have the account, then to check that we are on the right track, add it to the local admins group on the subscriber server and see if it works. if it does, then you can grant the windows rights explicitly and take it out of the Admins group.

    The article doesn't mention PUSH or PULL so I'm assuming it is the same process regardless, just done on the relevant server - the subscriber in your case as you are doing pull.

    HTH,

    Paul Ibison
    Tuesday, May 12, 2009 3:14 PM
  • The Agent Process account was set on SQL Server Agent account. 
    -- Account name is SQL Server Agent account and password parameter is blank.

    Distribution connection security mode is running on SQL Server Authentication (XLoginId and P@$$w0rd).

    Secutiry Mode is Database Authentication
    Login : XLoginId
    Password: P@$$w0rd

    Should I perform the following steps?
    If it is set to "Impersonate agent process account (Windows Authentication)" then you'll need (on the subscriber) to go to the control panel and have a look at the SQL Server Agent service and get the account from there. Once you have the account, then to check that we are on the right track, add it to the local admins group on the subscriber server and see if it works. if it does, then you can grant the windows rights explicitly and take it out of the Admins group.





    None
    Tuesday, May 12, 2009 3:51 PM
  • Yes - I'd set it to use an explicit windows account and grant that account the required privileges (local admin for the first shot as a trial).
    HTH,
    Paul Ibison
    Wednesday, May 13, 2009 8:23 AM
  • The replication was setup in non-trusted environment.   If I try to use window account, it will fail.


    None
    Wednesday, May 13, 2009 3:13 PM