3

I have a database on-prem, and I have an Azure account.

I need to establish transactional replication from the on-prem to Azure.

First of all I created a publication and after that I made a subscription to it.

Then I went to the publication and started the job.

It gives me the following error.

Any solution for that?

enter image description here

2
  • Your service account doesn't have access to that location. As for the second error, I have no idea. Have you tried searching the net? Commented Feb 5, 2017 at 9:01
  • I have same issue here, although all (trivial) security configuration steps has been performed (and staring at it for more than 1 hour). Commented Jun 26, 2017 at 16:06

3 Answers 3

3

I had the same problem and solved with the following steps:

  1. Right click shared folder and click properties
  2. Go to Advanced Sharing > Permissions In dialog "permissions for "
  3. Add snapshot user (\repl_snapshot in my case)
  4. Grant "Full Control" All should be ok now, and to verify
  5. Go to "View Snapshot Agent status" and click start, you should now see something like "[100%] A snapshot of 1 article(s) was generated."
2
  • I have an Azure SQL Managed Instance, and I got the error like Access to the path '\unc\XXXXXXXX-MANAGEDINSTAxxxxxxx_XXXXXXX_PUBLICATION\20200708132018\' is denied. Could anyone let me know how to fix this in Azure Managed Instance ? Commented Jul 8, 2020 at 14:12
  • "Add snapshot user (\repl_snapshot in my case)" - where did that user come from? How was it set up? Commented Mar 17, 2023 at 8:44
3

Nice question, I had the same scenario: Transactional Replication from on-prem to Azure SQL Database.

My solution was: Replication > Local Publication > Right click on your publication > Properties > Agent Security > Security Settings... and here setup your local Windows account and impersonate the same

enter image description here

1
  • Yeah this one quite sucks... I can't say more because I come with SQL Server Linux, and this Windows User way of handling snapshot agent is a mess.. But thanks for reminding! Commented Jun 15, 2023 at 2:01
0

Regarding the first error: user who run SQL server service doesn't have permission on that directory. as for the second error: "an exception occurred while executing a transact-sql statement or batch" its seems this is a mapping issue Map To Credential. or if the snapshot agent exist make sure its running and refresh the GUI.

3
  • Thanks Ahmed, the first error i have make the folder to everyone with read and write.s
    – Loai
    Commented Feb 5, 2017 at 10:21
  • 1
    second error: what do you mean by Map to Creaditials, with knowing that i'm sure from the username/password. may be there are some steps i forget it in the replications ?
    – Loai
    Commented Feb 5, 2017 at 10:23
  • Regarding the first error: have you made the folder accessible from all the machines with the correct path, for example if the folder in the master node (publisher) is c:\sql, then you need to make sure the slave node Hi @Loai (distributor) has access to that folder (network access) and the path will be \\server1\sql, regarding the second error if you want to make the configuration using SQL login and that login need to have Windows login permission then you need to configure map to credential by right click that user and properties general tab. Commented Feb 5, 2017 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.