How can I setup replication with the DNS name instead of the Server Name

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

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:

• Remove everything to do with replication from the Publisher/Distributor, up to and including the distribution database.

• 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)

• Redeploy the replication setup.

• Change the EncryptionLevel setting to = 2 for all Distribution Agents.

• 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

StackExchange Database Administrators Q#187412, answer score: 2