question

chrisrdba avatar image
0 Votes"
chrisrdba asked Cathyji-msft commented

DATA ACCESS not configured for repl_distributor.

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 @server=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?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft commented

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, it shows enabled, and replication is fine.

What originally prompted this post was the job failure. However, it's ran every night w success since then, even though I'm still unable to do a Test Connection on the linked server itself.

Really weird.

0 Votes 0 ·

Hi @chrisrdba,

So everything works well, no issue is occurred now?

If the issue occurred again, please feel free to let us know.

0 Votes 0 ·