Server 'repl_distributor' is not configured for DATA ACCESS

Question

I have enabled my repl_distributor linked server for data access in both publisher_server and distributor_server.

Both servers are also configured to allow remote connections

On both servers I have the same output and error messages:

Server 'repl_distributor' is not configured for DATA ACCESS.

USE [master]
GO
EXEC sp_configure 'remote access', 1; -- UI checkbox checked
reconfigure
EXEC master.dbo.sp_serveroption @server=N'repl_distributor', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_checkLinkedServer @server_name=N'repl_distributor'

 SELECT 
   name,
   is_data_access_enabled 
 FROM sys.servers
 where name = 'repl_distributor'

I have no problems with the replication I just wanted to use this repl_distributor as a linked server in a query.

The question is:

Is there a way to enable repl_distributor as a good linked server?

---

the query where I need to use a linked server to the distributor server is obviously when the distributor_server is a different server to the publisher_server.

I want to find the name of the distributor job.

A - when the distribution database is on the same server - no problem:

   use publisher_database
   go
select s.srvid
      ,s.artid
      ,[is article marked for reinitialization]= CASE WHEN s.queued_reinit = 1 THEN 'Yes' ELSE 'No ' END
      ,s.dest_db
      ,s.status
      ,s.distribution_jobid
      ,[distribution job name]=j.name
      ,[is job enabled]= CASE WHEN j.enabled = 1 THEN 'Yes' ELSE 'No ' END
      ,[Subscription Server Name]=s.srvname
from syssubscriptions s 
INNER JOIN msdb.dbo.sysjobs j
        ON job_id = s.distribution_jobid

B. when instead the distribution database is on the distributor server then I need to use a linked server:

   use publisher_database
   go
select s.srvid
      ,s.artid
      ,[is article marked for reinitialization]= CASE WHEN s.queued_reinit = 1 THEN 'Yes' ELSE 'No ' END
      ,s.dest_db
      ,s.status
      ,s.distribution_jobid
      ,[distribution job name]=j.name
      ,[is job enabled]= CASE WHEN j.enabled = 1 THEN 'Yes' ELSE 'No ' END
      ,[Subscription Server Name]=s.srvname
from syssubscriptions s 
INNER JOIN [my distributor server].msdb.dbo.sysjobs j
        ON job_id = s.distribution_jobid

Answer 1

try to use this to check first

 SELECT 
   name,
   is_data_access_enabled 
 FROM sys.servers;

then if value is 0 run:

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