SQL Server linked server's DATA ACCESS property changes to false

Question

I have a SQL Server instance that includes an agent job that accesses a second instance via a linked server. Occasionally, and seemingly randomly, the DATA ACCESS property of the linked server changes from true to false (and the agent job begins to fail).

That agent job runs every 10 minutes between 5am and 8pm, and the change to the linked server always happens sometime overnight, though I haven't yet been able to determine when exactly. I do have other jobs--some vendor supplied, and others such as backups, index and statistics maintenance, etc. (Ola Hallengren's scripts)--but nothing else that makes use of that linked server, and nothing that would explicitly touch it.

What would cause linked server DATA ACCESS to change? What troubleshooting or tracing can I do to determine what is causing this?

Answer 1

The only way to change the linked server properties I know of is via system procedure sp_serveroption.

This procedure is called in the background even if you change the properties via GUI.

So you can create an Extended Event session to track the execution of this proc.

CREATE EVENT SESSION [LinkedServerChange] ON SERVER 
ADD EVENT sqlserver.module_start
(
    SET collect_statement=(1)
    ACTION
    (
        sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.database_name
        ,sqlserver.server_principal_name
        ,sqlserver.tsql_stack
    )
    WHERE [object_name]=N'sp_serveroption'
)
ADD TARGET package0.event_file
(
    SET filename=N'LinkedServerChange'
    , max_file_size=(2)
    , max_rollover_files=(2)
)
GO
ALTER EVENT SESSION [LinkedServerChange] ON SERVER STATE = START

The result looks like this