SQL Server Performance Forum – Threads Archive

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

Check whether any of the servername has been dropped and added previously. To resolve the problem the server name needs to be updated. Use the following: sp_addserver <real-server-name>, LOCAL if this gives an error complaining that the name already exists then use the following sequence: sp_dropserver <real-server-name>
go
sp_addserver <real-server-name>, LOCAL
go If instead the error reported is ‘There is already a local server.’ then use the following sequence: sp_dropserver <old-server-name>
go
sp_addserver <real-server-name>, LOCAL
go Stop and restart SQL Server.
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, Thanks for the reply (again!). I have gotten past that error and now am receiving the error 18456 Login failed for user ‘sa’. 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. 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… You don’t have a support number I could call do ya? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> If you have any thoughts about this login failed issue, I’d sure appreciate them! Brian

Check to make sure that the computer you are trying to access which has the group
‘distributor_admin’ actually has the administrative access it requires, both in the security file for the server and in the user security for the individual database. If you are on a network and the network administrator has not enabled the administrator rights for the ‘distributor_admin’ group, you may need to ask the administrator to grant the permission over the network. KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q225129 to troubleshoot 18456 error and linkhttp://vyaskn.tripod.com/repl_ans4.htm#linkpubl for information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

True, you need to workout with network admin to check on those privileges on OS. Bunch of articles on replication setup for your joy:
http://www.databasejournal.com/features/mssql/article.php/1438231
http://www.databasejournal.com/features/mssql/article.php/1438201
http://www.codeproject.com/database/MergeReplication.asp
http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000-Part-2/
http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm
http://ouriran.com/snapshot.cfm Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

BTW can you check whether any issues with WINS and naming resolution issues on network. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

All the information is correct from SQL Server perspective and I have run out of options to checklist the options. Are you using Firewall between the SQL servers, if so check KBAhttp://support.microsoft.com/kb/287932/EN-US/ to reconfigure the ports to allow the traffic. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

Article on WINShttp://www.windowsitpro.com/Windows/Article/ArticleID/111/111.html fyi. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

Glad to know … there is way out of the tunnel. About identity columns, refer to thishttp://www.databasejournal.com/features/mssql/article.php/3483421 link which explains the process clearly. Let me know the product launch results too. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>