SQL Server - linked server disappears

Question

For 6 months I had linked servers working properly in SQL Server. Everything worked fine, but something strange started to happen. A week ago I was notified that synchronization was not working. I discovered that the linked servers have disappeared. I added them again but after 3 days again but after 3 days they disappeared again.

I didn't delete them manually.

Is it possible that something removed them?

How to protect against automatic removal?

Windows Server 2019, SQL Server 2017

Some*one* or some*thing* (an automated process, perhaps), will be removing them; Linked Server don't just remove themselves. I would suggest setting up some logging, via triggers or Extended Events, to keep an eye out and see when it happens next. Then you can investigate the route cause. — Thom A, Nov 28 '19 at 12:31
Something, because only me and my colleague have access to these servers. I trust him :) Thanks for suggestion. — bozydarlelutko, Nov 28 '19 at 12:41
Call be a sceptic, but Trust is hard to earn, easy to lose, and even harder to regain. :) — Thom A, Nov 28 '19 at 12:42

EzLo · Answer 1 · 2019-11-28T13:03:54.873

Create a table that will hold tracked operations, something like:

CREATE TABLE EventLogTable (
    EventLogTableID INT IDENTITY PRIMARY KEY,
    EventType NVARCHAR(100),
    PostTime DATETIME,
    SPID INT,
    ServerName NVARCHAR(100),
    LoginName NVARCHAR(100),
    ObjectName NVARCHAR(100),
    ObjectType NVARCHAR(100),
    [Parameters] NVARCHAR(1000),
    TargetObjectName NVARCHAR(100),
    TargetObjectType NVARCHAR(100),
    TSQLCommand NVARCHAR(1000))

Then create a DDL server trigger that monitors linked server events, through the EVENTDATA() function:

CREATE TRIGGER utrLogLinkedServerOperations ON ALL SERVER FOR 
    CREATE_LINKED_SERVER, 
    DROP_LINKED_SERVER,
    ALTER_LINKED_SERVER
AS
BEGIN

    DECLARE @EventXML XML = EVENTDATA()  

    INSERT INTO EventLogTable (
        EventType,
        PostTime,
        SPID,
        ServerName,
        LoginName,
        ObjectName,
        ObjectType,
        [Parameters],
        TargetObjectName,
        TargetObjectType,
        TSQLCommand)
    SELECT
        EventType = @EventXML.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
        PostTime = @EventXML.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)'),
        SPID = @EventXML.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)'),
        ServerName = @EventXML.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)'),
        LoginName = @EventXML.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
        ObjectName = @EventXML.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
        ObjectType = @EventXML.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'),
        [Parameters] = @EventXML.value('(/EVENT_INSTANCE/Parameters)[1]', 'NVARCHAR(1000)'),
        TargetObjectName = @EventXML.value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(100)'),
        TargetObjectType = @EventXML.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(100)'),
        TSQLCommand = @EventXML.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(1000)')

END

You can find the XML schema in here. Make sure to enable the trigger:

ENABLE TRIGGER utrLogLinkedServerOperations ON ALL SERVER

Now try creating, modifying and dropping a few linked servers to check that the trigger is correctly creating the log in the table. Then wait for the ninja to attack.

You could also rollback the operation inside the trigger but beware, you might end up not allowing even desired processes to manipulate linked servers.

score 0 · Answer 2 · answered Nov 28 '19 at 13:38

Dropping linked server requires ALTER ANY LINKED SERVER permission.

To find out who could do it you should find who has this permission at the server level (my second query that searches in sys.server_permissions). It's rare that someone has this permission granted explicitly, so you should search among sysadmins and setupadmins too.

I also include principals that have control server permission to my search.

Note that even if you can set up DDL-trigger someone with control server or just sysadmin can easily disable it when he wants to drop linked servers and not be captured.

select sp.name as RoleName,   
       member.name as MemberName  
from sys.server_role_members  rm
    join sys.server_principals as sp  
        on rm.role_principal_id = sp.principal_id  
    join sys.server_principals as member  
        on rm.member_principal_id = member.principal_id
where sp.name in ('sysadmin', 'setupadmin');

select  suser_name(grantee_principal_id), permission_name
from sys.server_permissions
where permission_name in ('ALTER ANY LINKED SERVER', 'CONTROL SERVER');

SQL Server - linked server disappears

2 Answers2