none
Transactional Pull Replication Generates a "FTP Fails to Connect" (message code 20032) RRS feed

  • Question

  • I think I have tried the obvious tricks, and there are a few, so I'll detail them here.

    Environments
    -----------------

    SQL2008 behind firewall 1 - PUBLISHER
    SQL2014 behind firewall 2 - SUBSCRIBER

    Set-up
    --------
    Hosts file aliases are set up as you would expect. The aliasing has been verified by logging on to the servers with SQL Server Management Studio.

    I have set up IIS and FTP on the PUBLISHER server. Because of the current problems, I have opened FTP up to allow read/write access to the FTP folders for (1) all accounts, (2) anonymous, and (3) the specific matching (PUBLISHER and SUBSCRIBER) machine accounts.

    Replication
    --------------

    I have set up the PUBLISHER to be transactional replication. I have added FTP specifying a folder and {series of} accounts.

    Generating the PUBLISHER with FTP appears to be fine. The Replication Monitor shows no problems.

    When the subscription is created, it is created successfully and it appears to be working.

    In viewing the "Job History" of the subscription I can see that:
    1) The subscription is created without error(and I get data).
    2) However, each subsequent job fails with the following error:

                  2020-06-11 01:02:18.597 Initializing
                  2020-06-11 01:02:18.879 Snapshot files will be downloaded via ftp
                  2020-06-11 01:02:18.941 Connecting to ftp site 'PUBLISHER'
                  2020-06-11 01:02:19.300 Agent message code 20032. The process could not connect to FTP site 'PUBLISHER' using port 21.
                  2020-06-11 01:02:19.550 Category:OS
                  Number:  12003
                  Message: 550 The system cannot find the file specified.
                          (I assume the 550 error is because the FTP connection failed.)

    When I go back to the PUBLISHER and mark the publication for re-initialization, that job succeeds without error. Then, subsequent jobs fail exactly as before.

    I have great confidence that the firewalls are not the problem. From the SUBSCRIBER I can open up any browser and enter "FTP://Publisher". I can see and navigate to sub-folders and copy files.

    I am pretty sure that folder security is OK since I can copy a file with an anonymous FTP connection and the initializations work.

    Additionally, using the replication functions I have gone back to the PUBLISHER and tried solving this by updating the FTP account and password to Null, Publisher\Machine_Account as well as just Machine_Account. Just to be sure, I dropped and recreated the subscription each time, but I got the error each time.

    I know 2008 is really old and unsupported but for the moment we're stuck. Therefore the obvious solution of upgrading to 2014 is off the table ... plus this should work, right???

    Any thoughts would be appreciated.


    Thursday, June 11, 2020 2:19 AM

Answers

  • Hi Eric Moothart,

    Please check the value specified for Path from the FTP root folder on the Publication Properties -> FTP Snapshot page is incorrect. 
    Please read  this document to found if you missed something.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Eric Moothart Friday, June 12, 2020 5:28 PM
    Friday, June 12, 2020 8:01 AM
  • The simplest way to resolve this, is to use an FTP program to connect to your FTP server using the same user name and password and see what you see.

    There are many free ftp clients.  You can even use Windows, however the debugging is more difficult.

    https://www.a2hosting.com/blog/free-ftp-clients/

    • Marked as answer by Eric Moothart Friday, June 12, 2020 5:28 PM
    Friday, June 12, 2020 11:30 AM

All replies

  • This looks like a path issue on your ftp server. You will need to look at your ftp logs to see where SQL Server is trying to access on your ftp server and then adjust your snapshot folder for this path.
    Thursday, June 11, 2020 6:36 PM
    Moderator
  • Error 550 indicates it was able to connect and failed to find the directory and/or does not have permission to the directory.

    It is making the connection fine.

    Thursday, June 11, 2020 7:55 PM
  • Thanks for the comments. I have just changed security on the FTP folder to everyone R/W. Just for the moment to test. This didn't change anything. I'm still seeing the same error.

    I did check the logs, thanks for the reminder. The logs show the account and password being processed without an error. However it's odd the users is as expected "anonymous" but a password being provided. The password is the local machine account.

    Also, the 550 error as "CWD /ftp 550 2 3 {guid}". What is odd to me is the slash is forward like a internet link not backwards like a directory. 

    Thursday, June 11, 2020 9:02 PM
  • Because of the password in the log. I delete the publisher/subscriber, restarted SQL and rebuilt from scratch. 

    I also added Anonymous User as R/W to the FTP folders. 

    I'm still getting the same error(s) in "View History".

    In looking at the FTP log, the are the same except the Password is now blank (dash).


    Thursday, June 11, 2020 10:48 PM
  • I'm still getting the same error(s) in "View History".


    It is looking for a subfolder named FTP. 

    I set my FTP root folder to C:\Temp\FtpRoot. Any CD command looks for a subfolder to that root folder. 

    Friday, June 12, 2020 1:59 AM
  • Hi Eric Moothart,

    Please check the value specified for Path from the FTP root folder on the Publication Properties -> FTP Snapshot page is incorrect. 
    Please read  this document to found if you missed something.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Eric Moothart Friday, June 12, 2020 5:28 PM
    Friday, June 12, 2020 8:01 AM
  • The simplest way to resolve this, is to use an FTP program to connect to your FTP server using the same user name and password and see what you see.

    There are many free ftp clients.  You can even use Windows, however the debugging is more difficult.

    https://www.a2hosting.com/blog/free-ftp-clients/

    • Marked as answer by Eric Moothart Friday, June 12, 2020 5:28 PM
    Friday, June 12, 2020 11:30 AM
  • Thanks to Tom and Cathy, it was the wrong FTP subfolder. I used a browser to connect but the principle is the same.

    For me the right answer was a blank sub folder.


    Friday, June 12, 2020 5:28 PM