SQL Server Replication – How to Setup Replication with DNS Name

replicationsql serverssl

We are using SSL certificates from a public CA on each SQL Server in our (push) transactional replication topology, and wish to have the EncryptionLevel = 2 for our Distributor Agents. Currently EncryptionLevel is set to 1, and works fine. (The difference between 1 and 2 is that 1 tells the agent to just to use encryption, and 2 tells it to also validate the certification chain of the SSL certificates)

The SSL certs are from a public CA. The Subject Name is the full Windows FQDN of the server (SERVER1.mywindowsdomain.com) and the Subject Alternative Names list contains the full DNS FQDN (MYAPP.MYDOMAIN.COM).

I can confirm that using SSMS I can correctly connect to each SQL Server without ticking 'Trust Server certificate' in the connection dialog, meaning that the certificate chain is being properly validated successfully.

When I change the EncryptionLevel to = 2, our Distribution Agents can no longer connect to the Publisher/Distributor running on the same server. This would make sense because they are trying to connect to SERVER1\DEV, whereas the SSL certificate is for a public DNS name – MYAPP.MYDOMAIN.COM, and therefore chain validation fails.

I am therefore looking for a way to setup replication from scratch, but using the DNS name (MYAPP.MYDOMAIN.COM) instead of the machine name (SERVER1).

However, when I execute the following command:

exec sp_adddistributor @distributor = N'MYAPP.MYDOMAIN.COM\DEV', @password = N'12345'

Then I get the following error message back:

Msg 18483, Level 14, State 1, Line 48 Could not connect to server
'SERVER1\DEV' because 'distributor_admin' is not defined as a
remote login at the server. Verify that you have specified the correct
login name. . Msg 14114, Level 16, State 1, Procedure
sp_adddistributiondb, Line 169 [Batch Start Line 65] The server
'SERVER1\DEV' is not configured as a Distributor.

Note that this command worked fine in the past when it was this:

exec sp_adddistributor @distributor = N'SERVER1\DEV', @password = N'12345'

After much googling, I have tried many variations on this. I have tried:

  • Using Client Aliases (32+64 bit) on the Publisher
  • Varying how my SSMS session is connected to the server, i.e. the connection dialog server name. I've tried using SERVER1\DEV, MYAPP.MYDOMAIN.COM\DEV, and also SERVER1.mywindowsdomain.com\DEV.

Any suggestions on what to try next would be appreciated. I'd be happy to explore either a) getting the Distribution Agents to be able to validate the certificate of the local server [but keeping in mind I cannot modify the SSL certs], or b) re-creating replication entirely from scratch so that it uses the DNS FQDN's, ie. MYAPP.MYDOMAIN.COM\DEV.

Note: Just to head off anyone that googles that error message and suggests that our SQL server might have been renamed, that is definitely not the case. The issue is the machine name vs the DNS name.

Best Answer

I was able to get this working, but with a fairly unsatisfying workaround - so if anyone else can contribute a better solution, please do. I am concerned that this solution will have unknown side-effects.

The broad steps are:

  1. Remove everything to do with replication from the Publisher/Distributor, up to and including the distribution database.
  2. Rename the SQL instance using the code sample pasted below. The new name should be the Machine FQDN (not the DNS FQDN). This is important so that it matches the name of the SSL certificate. Reboot the server (apparently just restarting the SQL instance isn't enough)
  3. Redeploy the replication setup.
  4. Change the EncryptionLevel setting to = 2 for all Distribution Agents.
  5. Check all Distribution Agents are working fine.

Now, when I get to the sp_adddistributor command, it works fine if I use the Machine FQDN, for example:

exec sp_adddistributor @distributor = N'SERVER1.mywindowsdomain.com\DEV', @password = N'12345'

And indeed, EncryptionLevel can be set to 2 successfully for the Distribution Agents.

Known side-effects:

  • Replication Monitor no longer connects automatically to the Publisher when it is launched from SSMS. I should note that it does work fine when being launched on the Publisher/Distributor server using Windows Auth, but does not connect when I launch it from our Jump Host and try to connect as 'sa'. To make it connect in this situation I have to add the Publisher, and then subsequently edit the connection properties. I suspect that this issue may be somehow complicated by the fact our servers are all Case-Sensitive, but I have no evidence to support this suspicion.
  • When using Distributor View in Replication Monitor, the right-panel list of Publications is always blank. The Publications do however show up in the tree view on the left. There is no problem using the Publishers View.

Code to rename server:

sp_dropserver 'SERVER1\DEV'
GO
sp_addserver 'SERVER1.mywindowsdomain.com\DEV', local
GO
Related Question