I’m trying to set up a transactional replication of database inside AlwaysOn using remote Distributor. After the Publisher, Distributor and Subscriber (all SQL 2016 Enterprise) are configured, publication created, I’ve created linked server connections on all servers and redirected original publisher to listener.
All jobs on Distributor are configured to use “Domain\Replication_acc” account which has sufficient access rights on all servers.
When I start the Snapshot agent job and get an error:
Validation failed for the publisher ‘TESTSQL’ with error 21878 severity 16 message ‘Unable to create a linked server to use in contacting the remote publisher for original publisher ‘TESTSQL’, publisher database ‘TESTSQL-1’, and redirected publisher ‘MyDatabase’. The command ‘sys.sp_addlinkedserver’ failed with Error ‘15247’, Error Message ‘Error 15247, Level 16, State 1, Message: User does not have permission to perform this action.’.’.
Values in this error are switched so I thought I made some mistake, so I dropped and recreated whole configuration but still got the same error.
Next thing I tried was granting “Domain\Replication_acc” sysadmin role on all servers – same error.
Next thing I tried was not using this account, but to use Distributor’s default SQL agent account “Domain\DistributorSQL_acc”:
and granted him db_owner role on replicated database “MyDatabase” on TESTSQL-1 server (same as originally had Replication_acc account) and job completed successfully and replication was working.
Therefore I believe that linked servers configured by Security Account Delegation and using @useself=N’True’ are not the problem. I’ve checked error log and error message is:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Could not find a login matching the name provided.
so I checked sys.dm_exec_connections and the “Domain\Replication_acc” account was not using Kerberos, but the NTLM auth_scheme, which is clearly wrong.
Both TESTSQL-1 and TESTSQL-2 servers are running under “Domain\TESTSQL_acc” account which has both servers registered in SPN. Distributor server is running under “Domain\DistributorSQL_acc” and also has his server registered in SPN.
Replication_acc is not set to be sensitive, so it can be delegated. Also it’s not disabled or anything like that. Only difference I was able to found was in Active directory, where only DistributorSQL_acc and TESTSQL_acc had possibility to set delegation, while Replication_acc didn’t give me such possibility.
Any ideas what else can I check/fix to be able to use delegated Replication_acc account for this replication? Without using local SQL logins and mapping Replication_acc to them, or such workarounds?