By: Alejandro Villarreal

Debugging replication-related problems can be extremely hard if you do not have access to an environment with the exact same replication architecture where the problem occurred in the first place. As consultants working for several clients, it is not feasible to have an exact replica of each of their environments, so a feasible solution is to set up a copy of the desired environment “on demand”, on virtual machines.

This is exactly what we did a couple of weeks back, and although we finally got replication to work, we had to deal with this error first:

Error 18483: Could not connect to server '<Server>\<Instance>' because '<login>' is not defined as a remote login at the server.

This happened while configuring the distributor, which was the same server as the publisher (local machine), so it didn’t make sense at all. Why was it attempting to connect to a “remote” server?

We finally figured out it was because when we installed SQL Server in the virtual machine, its NetBIOS name was not the one we later gave it (the one we wanted to use to access it). Thus, the @@Servername global variable had a value that we did not expect, and our replication script was inadvertently trying to connect to a server other than the local machine (run Select @@Servername to find out the name of the server under which SQL thinks it’s installed).

The fix is pretty simple. You just need to tell SQL Server to lose that reference, and create a new one with the appropriate NetBIOS name:

sp_dropserver @server='Incorrect-Server-Name'

sp_addserver @server='Correct-Server-Name', @local='local'

Once we did this, our replication scripts ran successfully and we could start working. Hopefully this will save someone else a bit of a headache while trying to figure out why their replication scripts won’t work in a virtualized environment. You can find more details regarding this problem in this Microsoft KB article: http://support.microsoft.com/kb/818334