Link Server connections

Question

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

Answer 1

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:

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"

Answer 2

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

Answer 3

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.

Answer 4

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.