DBA Configuring Replication Error help

Hello,

I have just started to configure my replication. My distributor and Publisher are the same machines. I have received an error after ensuring that the services were not using the local account, etc.

The error I'm receiving comes at the point when the wizard is trying to configure the distributor. The error is: SQL Server Enterprise Manager could not configure 'servername' as the distributor for 'servername'. Error 21007: cannot add the remote distributor. Make sure that the local server is configured as a publisher at the distributor.

I have search the archives and multiple search engines and followed all the tips. Nothing has worked yet...

Please HELP!
Brian

Hi Satya,<br /><br />Thanks for the reply (again!). I have gotten past that error and now am receiving the error 18456 Login failed for user 'sa'.<br /><br />I am at a complete loss - I know this is security related, but have done everything I've read and tried every combination you can imagine.<br /><br />Right now, there are 2 servers listed in the remote servers window and I have granted access to both my local server and the repl_distributor to map all remote logins to SA...<br /><br />You don't have a support number I could call do ya? <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />If you have any thoughts about this login failed issue, I'd sure appreciate them!<br /><br />Brian

That has been one sticking point for me with the network access stuff...

So I need to have a 'distributor_admin' group with admin rights? Not a user with admin rights?

Also, in SQL Server, I have a user called 'distributor_admin' with sysadmin role. This is correct?

Lastly, there is a remote computer created when I run the Replication Wizard called 'repl_distributor'. For this remote computer, the RPC is enabled and I have set all mappings to the sa account. Is this also correct?

Ok, think that's it for now! Thanks again for all your help! Hopefully others will find this info worthwhile...Isn't there a step-by-step guide available? Maybe someone should create one!!!

Cheers!
Brian

Hello,

Ok...I'm now past the login error as mentioned below and am now encountering a 20028 error. Details follow:

While using the Replication wizard, my first error encountered was the 18456
- the original error message was sa login failed...while messing with the
remote login mapping the error changed to distributor_admin login failed. I
resolved this by using the sp_changedistributor_password and this then
allowed me to run through the wizard to set up the distributor. At this
point, I did not set up the publisher or publications or subscribers.

Ok, so now I can go into the confiuration screen and set up the publisher to
use the non-trusted SQL Server authentication using distributor_admin (also
tried sa). When I specify a merge publication and click ok (only trying one
table at this point) I get the 20028 error saying that the distributor is not
configured correctly.

In reading through multiple forums and asking questions, I'm told to use the
sp_dropserver dropping the logins in the process of course and then the
sp_addserver with the local param.

This has not resolved the issue...There are 3 servers right now in the
sysservers table. S13 is the local server name id=0, DALSQL is the
distributor with id=2 and repl_distributor with id=3.

Any suggestions or thoughts would be greatly appreciated!

Thanks in advance,
Brian

PS: here's the output of the @@servername and the server name property along with the records in the sysservers table...

SELECT @@SERVERNAME
SELECT CONVERT(char(20), SERVERPROPERTY('servername'))
SELECT * FROM SYSSERVERS

S13
S13
01089S13SQL ServerSQLOLEDBS13NULLNULL2005-11-08 08:03:09.880NULLNULLNULLNULL00S13 010000100010NULL
11609repl_distributorSQL ServerSQLOLEDBS13NULLNULL2005-11-08 07:04:25.793NULLNULLNULLNULL00S13 010010100110NULL
21089ORDSQL01SQL ServerSQLOLEDBORDSQL01NULLNULL2005-11-08 05:26:16.060NULLNULLNULLNULL00ORDSQL01 010000100010NULL
31089DALSQL01.SPAREBACKUP.COMSQL ServerSQLOLEDBDALSQL01.SPAREBACKUP.COMNULLNULL2005-11-08 05:41:31.450NULLNULLNULLNULL00DALSQL01.SPAREBACKUP.COM 010000100010NULL

Here is the output from the sp_helpserver. You had asked me to post this if after getting the error that the distributor was not installed correctly and disabling and reenabling distributor/publisher. I still receive the same message. I also disabled again and genereated a script and ran that...still the same error.

01089S13SQL ServerSQLOLEDBS13NULLNULL2005-11-08 08:03:09.880NULLNULLNULLNULL00S13 010000100010NULL
11609repl_distributorSQL ServerSQLOLEDBS13NULLNULL2005-11-10 00:23:09.450NULLNULLNULLNULL00S13 010010100110NULL
21089ORDSQL01SQL ServerSQLOLEDBORDSQL01NULLNULL2005-11-08 05:26:16.060NULLNULLNULLNULL00ORDSQL01 010000100010NULL
31089DALSQL01.SPAREBACKUP.COMSQL ServerSQLOLEDBDALSQL01.SPAREBACKUP.COMNULLNULL2005-11-08 05:41:31.450NULLNULLNULLNULL00DALSQL01.SPAREBACKUP.COM 010000100010NULL


Thanks! Brian

Good day...

In regards to the naming/WINs; there doesn't appear to be any issues.

Is there a way for me to tell or test this?

Thanks,
Brian

Dear Satya,

I can't thank you enough for all the effort you have put into helping me. I have now configured the distributor/publisher and subscriber!

It all came down to the entries in the sysservers table. I had to update it with the appropriate server name for the dist/pub and also the subscriber server name. The whole time I was wondering if that was my problem.

Again, thank you for your help and patience! Now all I need to do is script my user defined data types, make sure I'm using full column lists and update a couple of identity fields.

By the way, do I need to set the NOT FOR REPLICATION on the identity fields if I'm going to use identity ranges? And if so, is there a property setting in SEM? I can't seem to find it. Also, where are the identity ranges set in replication?

Thanks again,
Brian