Where are the connections coming from?

Question

We have a few applications on different servers. Over time this hasnt been maintained properly so i dont know which server has an app (whether it be a website or an application) that is connecting to a SQL database on a single server with multiple instances.

Is there any easy way to determine what servers are making calls to each SQL instance and if possible maybe something that could determine which application/website called for that SQL data?

Answer 1

Hi @Mr Edge ,

You can try system stored procedure sp_who2 on the SQL Server.

• The HostName column will tell what computer/server is connected to the SQL Server.
• The DBName column will reveal what database they are using.
• The ProgramName column may tell you more about a connected application.

Answer 2

Hi @Mr Edge ,

Welcome to Microsoft Q&A!
Please try to run the following query to get the application name using the program_name column of sys.dm_exec_sessions:

 SELECT      c.session_id, s.program_name, s.login_name, s.status, st.text  
 FROM        sys.dm_exec_connections c  
 INNER JOIN  sys.dm_exec_sessions s ON c.session_id = s.session_id  
 CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st  
 ORDER BY    c.session_id  

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

You may want to do an xevents trace for logins over a period of time to see who or what is logging in. I just googled and found this website with some decent instructions for setting this up:

https://voiceofthedba.com/2015/12/16/tracking-logins-with-extended-events/

Answer 4

Just a word of caution here: both the hostname and the application name can be set in the connection string.

With most client APIs, the hostname will default to the actual machine name if not set.

On the other hand, the program name may only be accurate if the application actually sets in the connection string. This is different for different APIs. But with a .NET application, all you may see is .NET SqliClient Provider, or something like that.

A different approach would be to examine the applications and their connection strings.