?
Solved

How to setup replication using MS SQL 2005

Posted on 2009-04-15
17
Medium Priority
?
529 Views
Last Modified: 2012-06-21
I am currently trying to replicate from an internal server to web server sitting in the dmz of a Cisco firewall. The publication is setup and appears to work fine. I setup the subscriber on the web server and everything looks fine.

When viewing the synchronization status, there is an error "The process could not connect to the distributor 'Server Name' "

When I click on View Job History I get "The execute permission was denied on the object 'sp_help_job', database 'msdb', schema 'dbo'.
0
Comment
Question by:bmaitdepartment
  • 10
  • 7
17 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24151257
grant the account that runs the sql agent jobs permissions to that proc in msdb

use msdg
grant exec on sp_help_job to accountrunningsqlajgent
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24152041
Still getting the same error
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24157462
I am still at a loss on this one, any one have any more ideas?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24157478
whoops...I think it is because you dont' have permissions to view the history.  Do you have sa access?  If not, have someone give your username the permissions I discussed above.
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24160952
in the job history it now says login failed for 'username', where does this username have to be in order to work, i have it everywhere i can think of
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 400 total points
ID: 24160983
make sure the accounts running sql agent have the necessary permissions.
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24161060
which permissions would that be?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161089
right click my computer, then go to manage.  Find services, and then the SQLAgent service.  Make sure the account that runs that service is able to log into both machines...should be a domain account.
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24161173
i have 2000 and 2005 installed on that server, i only see the agent for 2000 running, is that an issue or just pay attention to the 2000 account?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161187
pay attention to everything that is using replication.
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24161224
the account running sqlagent can't log on to the server with the subscriber, they are on different domains
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161252
Can both machines get to the distributor machine?
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24161982
The publisher is the distributor. I can connect from the subscriber to the distributor/publisher.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161995
is there a trust between the two domains?
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24162016
In response to your post about having the necessary permissions on the SQL server Agent:

"right click my computer, then go to manage.  Find services, and then the SQLAgent service.  Make sure the account that runs that service is able to log into both machines...should be a domain account."

I can't make the account have access to both machines if they are not in the same domain. I have the publisher/distributor server inside the domain and the other is in the dmz (outside the domain).
0
 
LVL 1

Author Comment

by:bmaitdepartment
ID: 24162021
The DMZ server is not in a domain. It is a stand alone server.
0
 
LVL 1

Accepted Solution

by:
bmaitdepartment earned 0 total points
ID: 24198396
There were more ports that needed to be opened in the DMZ. Problem Solved.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question