none
The Schema script could not be propagated to the subscriber

    Question

  • I have been unsuccessful in getting merge replication running on a new test environment.  I am using SQL Server 2012 SP1 with a small merge publication. I am using an alternate folder and I would like to compress the snapshot but, one of the articles said to try and get it working without compression. It says the process could not read the file;  Login Failure.  I have set the agents to use the sa credentials.  We cannot use domain credentials because we are not using ADC.

    
    
    

    Steve

    Friday, April 11, 2014 9:33 PM

Answers

  • Got it. When you run it from the console it works as the security context that are you are running it from (the user account you are logged in under) has rights to the share and to list files and folders to the files and folders that lie underneath that share.

    Then when you have it run as a job or as a scheduled task it runs under the SQL Server Agent account (if you run it as a SQL Server Agent job) or if you run it as a scheduled task (using Windows Task Scheduler) it runs under whatever account you configure it to run under.

    So change the account SQL Server runs under, change the account the job runs under, or specify an account that task scheduler will run under and ensure this account has read permissions on the snapshot share on the publisher, and list files and folders to the files and folders that lie underneath that share. Ensure that this account permissions are propagated to all child objects on the unc share.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Sunday, April 13, 2014 9:32 PM

All replies

  • The replication agents each execute under a local windows or a domain account. They cannot execute under a SQL Login, as the agents are external programs that are not part of SQL Server. Only for the connection to (some of) the different SQL Server instances you can use SQL authentication.

    It looks like that you set up one of the agents to use a windows account that does not have the required permissions to the snapshot share.

    If you let us know which agent is throwing the error and post the exact error message, I can give you more detail on how to fix that. 

    You can also check out my Replication Stairway here:  http://www.sqlservercentral.com/Authors/Articles/Sebastian_Meine/667691/

     
    Saturday, April 12, 2014 6:51 PM
  • Date  4/12/2014 8:22:22 AM
    Log  Job History (<myserver>-<mydatabase>-SystemData-<mysubscriber>-<mydatabase>- 0)

    Step ID  1
    Server  <myserver>
    Job Name  <myserver>-<mydatabase.-SystemData-<mysubscriber>-<mydatabase>- 0
    Step Name  Run agent.
    Duration  00:00:44
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed 
    Operator Net sent 
    Operator Paged 
    Retries Attempted 0

    Message
    2014-04-12 13:22:22.142 Copyright (c) 2008 Microsoft Corporation
    2014-04-12 13:22:22.142 Microsoft SQL Server Replication Agent: replmerg
    2014-04-12 13:22:22.142
    2014-04-12 13:22:22.142 The timestamps prepended to the output lines are expressed in terms of UTC time.
    2014-04-12 13:22:22.142 User-specified agent parameter values:
       -Publisher <mypublisher>
       -PublisherDB <mypubdb>
       -Publication SystemData
       -Subscriber <mysub>
       -SubscriberDB <mysubdb>
       -SubscriptionType 1
       -SubscriberSecurityMode 1
       -Distributor <mydist>
       -XJOBID 0x981E29FB006431439D042DF035D74F20
       -XJOBNAME <myjobname>
       -XSTEPID 1
       -XSUBSYSTEM Merge
       -XSERVER <subscriber>
       -XCMDLINE 0
       -XCancelEventHandle 0000000000000768
       -XParentProcessHandle 0000000000000754
    2014-04-12 13:22:22.182 Connecting to Subscriber '<mysub>'
    2014-04-12 13:22:22.272 Connecting to Distributor '<mydist>'
    2014-04-12 13:22:24.913 Initializing
    2014-04-12 13:22:25.263 Validating publisher
    2014-04-12 13:22:25.363 Connecting to Publisher '<mypub>'
    2014-04-12 13:22:28.003 Retrieving publication information
    2014-04-12 13:22:28.073 Retrieving subscription information.
    2014-04-12 13:22:30.084 Applying the snapshot to the Subscriber
    2014-04-12 13:22:30.984 [3%] [0 sec remaining] Snapshot will be applied from the alternate folder '\\ATSRV-001\Replication\unc\ATSRV-001_ATPOS_SYSTEMDATA\20140411175126\'
    2014-04-12 13:23:01.063 The schema script 'AddressesTypes_2.sch' could not be propagated to the subscriber.
    2014-04-12 13:23:06.024 Category:NULL
    Source:  Merge Replication Provider
    Number:  -2147201001
    Message: The schema script 'AddressesTypes_2.sch' could not be propagated to the subscriber.
    2014-04-12 13:23:06.094 Category:AGENT
    Source:  PISCES
    Number:  20143
    Message: The process could not read file '\\ATSRV-001\Replication\unc\ATSRV-001_ATPOS_SYSTEMDATA\20140411175126\AddressesTypes_2.sch' due to OS error 1326.
    2014-04-12 13:23:06.174 Category:OS
    Source: 
    Number:  1326
    Message: Logon failure: unknown user name or bad password.


    Steve

    Saturday, April 12, 2014 7:42 PM
  • I am assuming you are doing a pull subscription.  What this message is saying is that the merge agent on the subscriber does not have rights to read that share. If you are suing SQL Authentication the agent will connect to your subscriber and publisher using the sql account specified but use the windows security context that the agent runs under.

    It is not clear to me how you are running your merge agent on the subscriber. The simplest way to solve these problems is to use a local machine account on the publisher and subscriber with the same name and password and ensure that this account has permission to read the snapshot share, and list files and folders under the folders which underlie the share.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Sunday, April 13, 2014 12:18 PM
  • I am on a domain we have at work. The Publisher is on our CoLo on a completely different domain.  I have admin rights on both domains but, I am connected to the CoLo via VPN. I have given "Everyone" (yuck - just a test) read / write permissions on the alternate folder and from my computer, I can go to run: \\Server\Share and get to the files no problem. I have even taken the script mentioned that it cannot run and run it manually without a problem.

    Steve

    Sunday, April 13, 2014 1:05 PM
  • Steve, enable auditing on your share/files and folders. You will be able to see what account is being used to attempt to download the files. Then you can only give this account read access to the share, and list files and folders access to the unc directory and all subfolders of it.

    There is no need to give this account write access.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Sunday, April 13, 2014 2:00 PM
  • Once I can get the replication job running, I will certainly do that. Even with the "Everyone" set to full access, I still cannot get the job to run.

    Steve

    Sunday, April 13, 2014 2:02 PM
  • OK, now I am confused. As it runs from the console we know there is not a problem with your script. Under what account does it fail? SQL Server Agent account on the subscriber? What account does SQL Server Agent on the subscriber run under? It can't be the local system account. It should be at least network service, and ideally a domain account.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Sunday, April 13, 2014 2:20 PM
  • Yeah, I'm sorry. It is confusing to me, too. There are so many places to add credentials, I am not sure which you are referring to. The Publisher does not have any problem putting the snapshot into the alternate shared folder. From the subscriber computer, I can connect to the shared folder and access all files from explorer. When I launch the subscription, it fails on the first script with a user name / password issue (mentioned above Message: The process could not read file '\\ATSRV-001\Replication\unc\ATSRV-001_ATPOS_SYSTEMDATA\20140411175126\AddressesTypes_2.sch' due to OS error 1326.
    2014-04-12 13:23:06.174 Category:OS
    Source: 
    Number:  1326
    Message: Logon failure: unknown user name or bad password.)

    Steve

    Sunday, April 13, 2014 2:29 PM
  • Got it. When you run it from the console it works as the security context that are you are running it from (the user account you are logged in under) has rights to the share and to list files and folders to the files and folders that lie underneath that share.

    Then when you have it run as a job or as a scheduled task it runs under the SQL Server Agent account (if you run it as a SQL Server Agent job) or if you run it as a scheduled task (using Windows Task Scheduler) it runs under whatever account you configure it to run under.

    So change the account SQL Server runs under, change the account the job runs under, or specify an account that task scheduler will run under and ensure this account has read permissions on the snapshot share on the publisher, and list files and folders to the files and folders that lie underneath that share. Ensure that this account permissions are propagated to all child objects on the unc share.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Sunday, April 13, 2014 9:32 PM