Adding a Remote Distributor for a Transactional Replication
You may encounter the following messages when creating a remote Distributor :
TITLE: Configure Distribution Wizard
------------------------------
SQL Server could not connect to the Distributor using the specified password.
------------------------------
ADDITIONAL INFORMATION:
An error occurred during decryption.
There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server '759D11CB-6B1A-4AAD-9D8E-5A89B2DAA151'. (Microsoft SQL Server, Error: 15466)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=15466&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
When you would try to create a Linked Server, it would fail with the below message :
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for LinkedServer 'XX\XX. (SqlManagerUI)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
'(null)' is an invalid product name. (Microsoft SQL Server, Error: 15429)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=15429&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
1. Please check if you are logged in with the service account to the machine.
2. Check if all the servers & agent ( publisher, distributor) are running with the same service account.
3. Check if there is no issues with SMK ( service master key).
4. Check if the service account is added to the logins on the SQL side.
5. Reboot the machine after adding the service account to the logins, if it was not added so that the SMK can be regenerated.
6. Check if there are any security related errors using :
SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type ='RING_BUFFER_SECURITY_ERROR'
7. Check if the SPN's are fine for the SQL instance as SPN's are used for Kerberos authentication.
8. If it's a cluster check if we have the following key : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Security.
9. If entropy key is missing, then we have to regenerate the SMK ( service master key).
10. Check if we are able to access the "AppData" folder with the SQL agent account.
For login failure of distributor_admin we can follow : https://blogs.msdn.com/b/sqlserverfaq/archive/2009/12/21/error-messages-and-the-solutions-related-to-distributor-admin-login.aspx