4

Is there a way to know if a specific database on my SQL Server instance is being queried over linked server?

2 Answers 2

10

No. A linked server is just some other SQL Server making a connection to your SQL Server. There's no way to reliably identify these connections.

2
  • 1
    I thought that perhaps investigating query text on sys.dm_exec_query_stats because they would appear as linkedservername.databasename.schemaname.tablename
    – Leonardo
    Nov 5, 2021 at 17:34
  • 5
    That would appear on the server containing the linked server destination and processing the query. On the target of the linked server you would not see the linkedservernname. And not all linked server access uses 4-part names anyway. Nov 5, 2021 at 18:00
10

There is one possible way, it is to look at sessions/connections, and guess by session's properties that this session is coming from unusual source:

select * from sys.dm_exec_sessions s
    join sys.dm_exec_connections c on 
    s.session_id = c.session_id
  • host_name = this is server name, you can find out if there's a SQL Server installed on it
  • client_interface_name = 'OLEDB'
  • program_name = 'Microsoft SQL Server'
  • client_net_address = this is IP address, will hint you to a server which might have SQL Server installed

Especially when you know very well, from which IP addresses and hosts usual connections / sessions (your apps or users) are coming from, you will be able to distinct anything that is suspicious

Then join the sys.dm_exec_requests and sys.dm_exec_sql_text to get the query that session is executing. The query can be like

SELECT ... FROM "DB"."dbo"."Table" "Tbl1002"

This will hint you to a database.

Again, I am not saying this will help in 100% cases, but might help you to identify what you are looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.