Merge w/ 2000 user (null) error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Merge w/ 2000 user (null) error

We have a customer that has 2 sights, both running SQL 2000 using merge replication. Everything was working fine until the subscriber had internet issues. The server was then moved to a different office and connected – but couldn’t connect to the main server due to firewall issues. Now, the subscriber is back at the original office and when the replication is started on the distributor, the error: Login failed for user ‘(null)’. Reason: not associated with a trusted
SQL Server connection. I want to change the connection to the other server to be using SQL security and not windows security. I need to change this without effecting the current data. They have been working without the systems replicating for a few weeks, so all of the data needs to be replicated, so I need to make it change the security without having to re-setup replication. Please help me – this is quite important as it has drug on for so long. Thanks Darin
Confirm that the account that you are logging into the publisher is in the Publication Access List and that the account you use to login to the distributor is in the dbo_role in the distribution database. KBAhttp://support.microsoft.com/?id=327825 for informatin.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Currently, the connection is using Windows Authentication, so it is administrator which does exist. But, since it isn’t working (I can’t connect from the distributors Query Analyzer to the subscriber server using windows, but I can using SQL Security), that is why I want to change. The KB you sent was: New resolution for problems with Kerberos authentication when users belong to many groups Is that what you wanted to send? Darin
Yes this is related (to some extent) for the login failures when multiple servers & replication is involved. You said they had problems and revoked the server from there to another place, ensure the network settings including DNS, routers & hubs have not changed to the previous place. If the changes are many then you can take help database backup to update the sites, or if few tables involved in the replication then DTS for data transfer. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
so you are saying there is no way after initial push setup of the merge replication subscriber to change the connection from windows security to SQL security? Darin
satya, I can connect to the subscriber using query analyzer when using the SQL security, but not when using windows security, so I just want to change the existing replication to connect using SQL sercurity and not windows security. THanks Darin
You can easily via EM… Connect to server/Tools/Replication/Configure Publishing, Subscriber and Distributor/
Then Click Subscriber tab/You see subscribers list/click "…" against the subscriber you want to change the authentication then modify the authentication… I believe it can be done using tsql….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

I believe they are not documented and you might get information by running PROFILER at the same time when using via Enterprise Manager.
http://technet.microsoft.com/en-us/library/ms147827(SQL.90).aspx in SQL 2005 (Don’t have any reference for 2000 though) &http://www.codeproject.com/database/MergeReplication.asp talks step by step details. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Microsoft uses sp_MSupdate_subscriber_info procedure to change the password…
It updates MSsubscriber_info table in distribution db… The following script can be used… I have tested on my test server… declare @password sysname, @publisher sysname, @subscriber sysname, @security_mode int, @login sysname
select @subscriber = ‘Sub_mohammedu’, @publisher = ‘Pub_mohammedu’,
@login = ‘test’, @password = ‘testpwd’, @security_mode = 1 — select * from MSsubscriber_info EXEC master.dbo.xp_repl_encrypt @password OUTPUT update MSsubscriber_info
set login = @login ,
password = @password ,
security_mode = @security_mode
where UPPER(publisher) = UPPER(@publisher)
and UPPER(subscriber) = UPPER(@subscriber)
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

]]>