none
Error when adding distribution database to SQL 2014: using sp_adddistributiondb error: Destination path is not valid RRS feed

  • Question

  • I am trying to change the default mdf/ldf paths for a the distribution database when running sp_adddistributiondb and when I do so, I get the error "Destination path xxx is not valid. Unable to list directory contents. Specify a valid destination path." When I allow defaults by running exec sp_adddistributiondb @database = N'distribution', it works fine using the default sql install folder. The folder I am directing it to is the same folder other user databases are installed on and where I set the default user databases mdf/ldf log files to on install. This is standard edition, SQL 2014 a named instance. I did install replication components on install, the instance is on a fixed non-standard port.The sql server and agent service account has full rights on the folder. I compared the permissions with the default data folder that it will use when not specified and the only difference was the nt service\mssql$instance so I added that but still no luck. Any suggestions? Thank you
    • Edited by Abeljdang Thursday, April 30, 2020 9:16 PM details
    Thursday, April 30, 2020 9:06 PM

All replies

  • Hi Abeljdang,

    From MS document, only members of the sysadmin fixed server role can execute sp_adddistributiondb. Which account the SQL server service and Agent service running under? Please try to use Local System or domain admin account.

    Please try to get the T-SQL about creating distribute DB from Configure Distribution wizard as below screenshot, then change the default mdf/ldf paths for distribution database. Then run the T-SQL. Please refer to this blog to get more  detail information.


    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

    Friday, May 1, 2020 3:20 AM
  • Hello, the account running the sql service is a domain account, and it is in the sa role on this instance. I tried what you note above via gui and changing the path prior to posting this, I get the same error destination path not valid
    Friday, May 1, 2020 2:35 PM
  • Hi Abeljdang,

    This is a local file folder or shared folder? Did you check the path of the file? Is it spelled correct?

    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

    Monday, May 4, 2020 9:39 AM
  • Hello, yes it is a local to the server folder. I copy and past the path in explorer on the server and it opens up the folder. Its got me stumped.
    Monday, May 4, 2020 2:34 PM
  • Hi Abeljdang,

    Did the SQL agent service is running under your domain account? Please try to give the full control permission to Everyone on the folder. If it fix the issue, your issue is a permission issue. 

    If it is not work, please try to create a new folder, and give the full control permission to your domain account. Then using the new folder as distribution database mdf/ldf paths to see if it could solve the issue.

    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

    Tuesday, May 5, 2020 2:38 AM
  • Hello, I will try this when I have to drop and recreate the replication. I had to move ahead and get this done so I let it default to where the other system databases are located and it worked.
    Monday, May 11, 2020 2:27 PM
  • Hello, I will try this when I have to drop and recreate the replication. I had to move ahead and get this done so I let it default to where the other system databases are located and it worked.
    Hi Abeljdang,

    Ok.If you have any progress, please share us the result.

    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

    Thursday, May 14, 2020 8:22 AM
  • Yes I will do so Cathy
    Thursday, May 14, 2020 12:24 PM