Sunday, February 5, 2017

Replication Setup -FIX- Unable to list directory contents

You're trying to setup SQL Server Replication on a server, and it fails. Looking thru the error message you find this:


        An exception occurred while executing a Transact-SQL statement or batch.
        (Microsoft.SQLServer.ConnectionInfo)

        Destination path .............is not valid. Unable to list directory contents. Specify a valid

            destination path.
            Changed database context to 'master'. (Microsoft SQL Server, Error: 14430)

Unable to list directory contents. Specify a valid destination path.

We were setting up an existing SQL Server 2014 Standard edition to run as a combined Distributor / Publisher, and ran into this problem. Looking over the blogs, the one thing that became apparent was that there was quite a bit of confusion for the problem. Below is how we worked out the steps and configuration issues that ultimately led to success.

Unable to list directory contents


Ultimately, our focus was on permissions. And, in general, the consensus was to run the Configure Replication Wizard as SA. So, this means that any file/folder permissions fall to the account running SQL Server.  For our example, that would be:  MYDOMAIN\sqlserviceacct

We'll admit that after several failed setup attempts, some of the following may be excessive. But they finally worked.

Preparation

  1. SQL Server service running a domain account:  such as  MYDOMAIN\sqlserviceacct
  2. Replication Snapshot Share
    1. Verify that the account "MYDOMAIN\sqlserviceacct" can both
      1. read the folder
      2. read the UNC share
    2. In addition to the other accounts that will need access to support replication!
  3. Folder(s) to hold the Distribution Database. For example, your distribution database will be placed in the folder E:\SQLsvr\Data\Distribution
    1. Verify that the account "MYDOMAIN\sqlserviceacct" can READ the top level folder
    2. Verify that the account "MYDOMAIN\sqlserviceacct" can READ the next level folder.
    3. Verify that the account "MYDOMAIN\sqlserviceacct" has control over the FINAL folder. In this case E:\SQLsvr\Distribution. 
  4. EXEC sp_configure 'xp_cmdshell', 1;
    1. with a newly setup SQL Server, you may not have disabled xp_cmdshell. But, this server was already in production, and since it did not need xp_cmdshell, it was disabled (a best practice).
    2. Before running the Wizard, we temporarily enabled xp_cmdshell. Once the install was completed, we promptly disabled xp_cmdshell.
  5. Consider running:  sp_removedbreplication.
    1. One thread suggested that you run sp_removedbreplication. It was listed under the premise that you had an earlier replication installation. We did not, but we still ran the procedure to clear out any partial replication objects that may have been left from our earlier failed install. 

sp_configure 'xp_cmdshell', 1;

We are sure you know how to do this, but just incase, see: xp_cmdshell Server Configuration Option.
Basically (from MSDN):

       
-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO         
 

Running the Wizard - Configure Replication

  1. Using SSMS, connect to SQL Server as SA.
    Looking over many of the postings, it was generally agreed to run the wizard as SA. 
  2. SnapShot folder
    1. UNC required, such as \\myserver\myreplicationshare
    2. Verify that your UNC is exactly the one you setup.
  3. Database folder
    1. Verify that your folder is exactly the one you plan to use, with the permissions set as listed above.
  4. Script - yes you want a copy of the script.
That should lead to success.  If not, go back and verify that your SQL Server service account really does have access to the folders. And then make sure you have them spelled correctly.

No comments: