DATA ACCESS not configured for repl_distributor.

Question

After replication running for a year, the nightly expired subscription cleanup job suddenly failed. Tracking everything down, the repl_distributor linked server fails connection attempts with the following:

Server 'repl_distributor' is not configured for DATA ACCESS. (.Net SqlClient Data Provider)

However, I script out the linked server and it shows this:

EXEC master.dbo.sp_serveroption @Testta =N'repl_distributor', @optname=N'data access', @optvalue=N'true'
GO

Ironically replication is still working just fine, as verified through tracer tokens, etc.

Note that I did reboot this server last night before the issues started, so I'm sure it's related.

Any ideas?

Answer 1

Hi @chrisrdba ,

SQL server replication works well now? Expired subscription clean up job still failed now?

Please using below T-SQL to check the Data Access Setting.

SELECT   
  name,  
  is_data_access_enabled   
FROM sys.servers;  

If the value for repl_distributor is 0, please run below T-SQL;

EXEC sp_serveroption  
  @server = 'repl_distributor',  
  @optname = 'DATA ACCESS',  
  @optvalue = 'TRUE';  

Then check the Data Access Setting again.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.