SQL 复制设置 - 几乎完成
我正在尝试在两个 SQL Server 之间设置复制，我几乎已经完成了，但遇到了权限错误（看起来）。 服务器通过 VPN 连接。
发布者/分发者 = W2003 + SQL2005（域控制器）
订阅者 = W2008 + SQL2008（不在域上的独立服务器）
我已将其设置为拉取而不是推送，因为这是我解决登录问题的方法。
当前状态是两个代理都在运行并且快照已完成，但尚未开始复制。 下面出现错误，似乎表明订阅者没有快照文件夹的权限，但我已设置“每个人”（完全控制）只是为了尝试使其正常工作。
我在想是不是信任什么的有什么问题。 我可以从订阅服务器上调出发布服务器上的快照共享并访问文件，而不会出现任何权限问题。 但 SQL 似乎有某种问题。
对下一步排除故障有什么想法吗？ 谢谢。
这是日志的结尾：
2009-07-22 23:34:47.838 Initializing
2009-07-22 23:34:49.263 将从备用文件夹“\[MachineName][share]\unc\SYDNEY_MIRRORMIRROR_MIRRORMIRRORPRODUCT\20090722085146\”应用快照 2009-07-22 23:34:50.809 代理消息代码 20143。由于操作系统错误 5，进程无法读取文件 '\[MachineName][share]\unc\SYDNEY_MIRRORMIRROR_MIRRORMIRRORPRODUCT\20090722085146\TRProductImages_8.pre'。 2009-07-22 23:34:51.524 类别：操作系统 来源：
数量：5 消息：访问被拒绝。
I'm trying to setup replication between two SQL Servers and I'm almost there but stuck on a permission error (it appears). The servers are connected via VPN.
Publisher/Distributer = W2003 + SQL2005 (Domain Controller)
Subscriber = W2008 + SQL2008 (Stand Along Server not on a domain)
I have set it up to Pull rather than push only because that's the way I got past the logins issue.
Current status is that both agents are running and the snapshot has completed but it doesn't start replicating. There error is below which seems to indicate that the Subscriber does not have permissions to the Snapshot folder but I have set Everyone (Full control) just to try and get it working.
I'm thinking is there some issue with trust or something. I can bring up the snapshot share on the Publisher from Subscriber and access files without any permission issues. But SQL seems to be having some kind of issue.
Any thoughts on the next steps here to trouble shoot? Thanks.
This is the end of the log:
2009-07-22 23:34:47.838 Initializing
2009-07-22 23:34:49.263
Snapshot will be applied from the alternate folder '\[MachineName][share]\unc\SYDNEY_MIRRORMIRROR_MIRRORMIRRORPRODUCT\20090722085146\'
2009-07-22 23:34:50.809 Agent message code 20143. The process could not read file '\[MachineName][share]\unc\SYDNEY_MIRRORMIRROR_MIRRORMIRRORPRODUCT\20090722085146\TRProductImages_8.pre' due to OS error 5.
2009-07-22 23:34:51.524 Category:OS
Source:
Number: 5
Message: Access is denied.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，在设置 SQL Server 复制时解决权限问题可能是一件苦差事。 从域外的计算机访问域控制器上的共享...嗯。 您是否尝试过在订阅者计算机上设置共享？ 这不是最佳选择，因为它使发布者在创建快照时受到网络 I/O 的支配...但是...
您提到将复制设置为拉式而不是推送式，因为它让您解决了登录问题。 您是否尝试过使用 SQL Server 权限而不是 Windows？
我想我还应该提到，如果在这种情况下没有域控制器/非域计算机组合，我会首先询问/确认两台计算机上运行 SQL Server 服务的登录名是否相同...事实上，我不确定这是否重要。
Yeah, getting permissions issues worked out when setting up SQL Server replication can be a chore. Accessing a share on the domain controller from a machine outside the domain... hmm. Have you tried setting up the share on the subscriber machine? Not optimal as it puts the publisher at the mercy of network i/o when creating the snapshot... but...
You mention having set up the replication as a pull rather than push because it got you past login issues. Are/have you tried using SQL Server permissions rather than windows?
I guess I should also mention that if it weren't for the domain controller/non-domain machine combination in this situation I would have started by asking/confirming that the logins under which the SQL Server services were running on both machines were the same... as it is I'm not sure it would matter.
您在订阅者订阅属性上的安全设置是什么 --> 分销商和出版商的联系？
另外，在出版物属性下 --> 出版物访问列表； 您是否仔细检查过您的订阅者是否在其中？
What are your setting's on the Subscriber Subscription Properties for Security --> Distributer and Publisher Connection?
Also, under the Publication Properties --> Publication Access List; Have you double checked that your Subscriber is in there?
好吧，事实证明错误消息是正确的。 但不是以我想的方式。
我正在通过 VPN 设置复制。 我怀疑大多数防火墙的基本起点是您可以在大多数端口上发送但不能接收。
我能够启动拉复制但不能推送并且尝试读取快照时发生错误。 令人困惑的是，我可以从订阅者计算机上打开共享，但为什么 SQL Server 不能提取快照？
最后发生的事情是 UNC/文件共享 137,138,139 和 445 的端口没有向订阅者开放。
我以为订阅者会从分发者那里获取快照，但我认为它必须请求它们，但分发者仍然将它们推送给订阅者并在进入的过程中被阻止。
我证实了我的怀疑，因为我可以从订阅者打开共享分销商，但不是从分销商到订户。 我也无法使用分发服务器计算机上的 SQLMgmtSutdio 连接到订阅服务器，所有证据都证明防火墙仅在这一方向上阻止了它。
因此，即使端口被阻止，SQL 仍然能够设置发布者、订阅者和分发者，但在我打开它们之前无法启动该过程。
现在正在运行。
希望这个冗长的解释可以帮助其他正在努力解决这个问题的人。
Ok well as it turns out the Error Message was correct. But not in the way i thought.
I'm setting up replication over a VPN. The basic starting point of most firewalls I suspect is you can send out on most ports but not receive in.
I was able to get pull replication started but not push and the error occurred trying to read the snapshot. The thing that was confusing was that I could open the share from the subscriber machine so why couldn't SQL Server pull the snapshot?
In the end what was happening was that the ports for UNC/File Sharing 137,138,139 and 445 were not open 'into' the subscriber.
I was thinking the Subscriber would pull the snapshot off the distributor but I think it must request them but the Distributor was still pushing them to the subscriber and being blocked on the way in.
I confirmed my suspicions because I could open a share from Subscriber to Distributor but not from distributor to subscriber. Nor could I connect to the Subscriber server using SQLMgmtSutdio from the Distributor machine, all proof the firewall was stopping it in just that one direction.
So somehow even though the ports were blocked SQL was still able to set up the publisher, subscriber and distributor but not start the process until I got them opened.
Its running now.
Hope that long explanation helps someone else struggling with getting this going.