Link Server connections

ACDBA 416 Reputation points
2022-11-18T17:13:19.937+00:00

Hi All,

I would like to disable remote access server configuration across all SQL servers which is not using linked server incoming connections. Is there a way we can confirm whether sql instances are getting incoming linked server connections if we dont have linked server names? sys.dm_exec_requests or any dmv's which has a flag what says the connected host is a linked server?

Regards,
Arun

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,333 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. PandaPan-MSFT 1,911 Reputation points
    2022-11-21T06:31:14.337+00:00

    Hi @ACDBA ,

    if we dont have linked server names?

    If you don't know the server names, I don't think it is possible to make the connection.
    You can check the linked server you had made connection in this place:
    262389-image.png

    If you are worried about privacy, you can try the following solution:
    Create an account in A that can only select view _ A. Then use the account A in B to establish the connection of the linked server.(At the same time, select the account of the manufacturer in the security to correspond to the account of A) Then B won't have the right to check some tables in A.

    linked server properties---security tab---Created using the current security context of the login

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'serverB',@useself=N'False',@locallogin=N'userB',@rmtuser=N'userA',@rmtpassword='########  
    

    Is it your demand? If I misunderstand, pls let me know.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

    0 comments No comments

  2. ACDBA 416 Reputation points
    2022-11-22T15:46:44.217+00:00

    Hi @PandaPan-MSFT ,

    Thank you so much for your kind reply. My requirement is different.

    There are 26 servers,Server A,Server B....Server Z.

    I want to know if anyone is connecting to server A using a linked server.

    I have whoisactive capture in server A.. But that cannot distinguish whether the hostname coulmn is a linked server or app server or ..

    Thanks,
    ACDBA

    0 comments No comments

  3. Tom Phillips 17,721 Reputation points
    2022-11-22T18:54:28.887+00:00

    There is no way to "detect" if someone is connecting to the target using a linked server. It is just a connection. There is no other information send to the target server.

    0 comments No comments

  4. PandaPan-MSFT 1,911 Reputation points
    2022-11-23T01:25:32.323+00:00

    Hi @ACDBA

    But that cannot distinguish whether the hostname coulmn is a linked server or app server or..

    WOW, that's a very good idea to distinguish........but just to SQL it's impossible.......Unless you use some third-parts(we are not allowed to recommed third-part programs).
    I just can say wish you good luck.
    And if you think my answer is helpful for you, you can choose to accept answer, so that others who have the same problem can see the soultion.
    Thanks for your contribution.

    0 comments No comments