Can you set up the sql server agent on both boxes to use the same domain account and add it to the local administrators group on each box then see if this helps. Cheers, Paul Ibison [quoted text, click to view] <war_wheelan@yahoo.com> wrote in message
news:1127221547.595236.97980@f14g2000cwb.googlegroups.com...
>I have been trying to get replication working for a month now with no
> luck. I have tried all of the suggestions to fix 'ERROR 18456' and
> none have worked.
>
> Most of the suggestions talk about enabling mixed mode authentication.
> The server has always been in mixed mode and I can login to query
> analyzer with either the Windows or SQL accounts. I have also tried
> adding the login to the db_Creators role for the db and changing the
> account which runs the distribution agent amongst other things.
>
> BACKGROUND
>
> Replication was working correctly until I detached and copied the db to
> another server so that they would be identical. The other server is
> the destination of replication. I GENERATED A SQL SCRIPT OF THE
> REPLICATION SETUP BEFORE DETACHING THE DB. After syncing up the two
> dbs, I re-enabled replication. It worked replication was re-enabled
> with all of the appropriate tables, but the distribution agent couldn't
> login in to the subscribers (There are two). The account listed in the
> error has system admin rights to the server and db. I can login
> manually to either subscriber with my SQL or Windows accounts.
>
> At this point, I disabled replication and decided to set it up from
> scratch. I received the same error this time while setting up
> replication so it failed with the now infamous ERROR 18456.
>
> CAN SOMEONE PLEASE HELP ME UNDERSTAND THIS BETTER. I know that agents
> control replication and I have tried to change the accounts which these
> run under.
>
> LASTLY, WHEN I SETUP REPLICATION FROM SCRATCH I RECEIVE THE ERROR 18456
> FOR AN ACCOUNT THAT I WASN'T LOGGED IN UNDER. THE ACCOUNT IN QUESTION
> IS A MEMBER OF THE DB_OWNER ROLE FOR THE DB AS IS MINE.
>
> CAN SOMEONE PROVIDE ME WITH A TROUBLESHOOTING METHODOLOGY TO ATTEMPT TO
> RESOLVE THIS PROBLEM?
>
I have been trying to get replication working for a month now with no
luck. I have tried all of the suggestions to fix 'ERROR 18456' and
none have worked.
Most of the suggestions talk about enabling mixed mode authentication.
The server has always been in mixed mode and I can login to query
analyzer with either the Windows or SQL accounts. I have also tried
adding the login to the db_Creators role for the db and changing the
account which runs the distribution agent amongst other things.
BACKGROUND
Replication was working correctly until I detached and copied the db to
another server so that they would be identical. The other server is
the destination of replication. I GENERATED A SQL SCRIPT OF THE
REPLICATION SETUP BEFORE DETACHING THE DB. After syncing up the two
dbs, I re-enabled replication. It worked replication was re-enabled
with all of the appropriate tables, but the distribution agent couldn't
login in to the subscribers (There are two). The account listed in the
error has system admin rights to the server and db. I can login
manually to either subscriber with my SQL or Windows accounts.
At this point, I disabled replication and decided to set it up from
scratch. I received the same error this time while setting up
replication so it failed with the now infamous ERROR 18456.
CAN SOMEONE PLEASE HELP ME UNDERSTAND THIS BETTER. I know that agents
control replication and I have tried to change the accounts which these
run under.
LASTLY, WHEN I SETUP REPLICATION FROM SCRATCH I RECEIVE THE ERROR 18456
FOR AN ACCOUNT THAT I WASN'T LOGGED IN UNDER. THE ACCOUNT IN QUESTION
IS A MEMBER OF THE DB_OWNER ROLE FOR THE DB AS IS MINE.
CAN SOMEONE PROVIDE ME WITH A TROUBLESHOOTING METHODOLOGY TO ATTEMPT TO
RESOLVE THIS PROBLEM?
Paul we are not using a domain model instead we use independent
servers, but I tried to do what you suggested. I verified that the
same account name runs the SQL Agent on each server and reset their
passwords to the same value. I then restarted the SQL Agent(s). I
also reset the password for the SQL login 'mac', on all three servers,
to the same value.
I then tried setting up replication and received the same ERROR 18456
(login failed for user mac). I still don't understand why the login
fails for user 'mac' when I am configuring replication with my login
'war'. 'mac' is a SQL login as is 'war'. ANY IDEAS?
Paul - It took a while, but I think that I found it (Impersonate the
SQL Server Agent account on 'ServerName' (trusted connection). On the
bottom of this page there is a check box which says (Administrative
link to the Distributor (This Publisher requires a password to
establish a link to the Distributor)) Since the Publisher and the
Distributor are on the same server can I uncheck this box?
I tried unchecking the box and them it talked about (Error 15185: There
is no remote user 'distributor_admin' mapped to the local user
'distributor_admin' from the remote server 'innywpp01\production'.
These messages confuse me because the Publisher and Distributor are on
the same box.
I just mentioned this, but my real focus is my original problem which
is getting replication to work. All suggestions are welcome.
Lastly, if I use the old 'replication script.sql' replication gets
re-enabled. The only problem becomes connecting to the subscribers
(Error 18456). Remember setting it up from scratch doesn't let me
setup replication at all.
Please click on the distributor properties elipsis buttons - are you using
trusted authentication or sql authentication?
Cheers,
Paul Ibison
All
When I first followed the suggestions in this posting, it didn't
correct the problem that I was having. I continued troubleshooting
trying everything that I could think of or that was suggested to me.
It still didn't work. I finally went back to the beginning and
followed the suggestions in this posting and it corrected my problem
and I got replication working again.
I don't know which of the suggestions worked. I will attribute it to
all of them .
Thanks.
Don't see what you're looking for? Try a search.
| 