What is the program_name in sys.dm_exec_sessions?

4

January 6, 2020 by Kenneth Fisher

If you’ve ever looked at the output for sys.dm_exec_sessions you may have noticed the column program_name. Frequently you’ll see entries like these:

  • .Net SqlClient Data Provider
  • Microsoft SQL Server Management Studio
  • Microsoft SQL Server Management Studio – Query
  • Microsoft SQL Server Management Studio – Transact-SQL IntelliSense
  • Microsoft® Windows® Operating System

 
Basically it’s the program that opened the session. This can be really useful at times. For example making sure that people aren’t logging in using SSMS, or at least finding out if a given session is coming from someone running ad hoc queries through SSMS or running an application.

But the question is, how accurate is this information? Well, for the most part it’s pretty good. The problem is that it’s at once really easy to spoof (change) and on the other hand very rarely changed from the default.

In SSMS it’s as simple as using the Additional Connect Parameters option of the connection screen. Very view people even know how to do this and even fewer are going to bother. However if someone really wants to fool you it isn’t that hard.

To change the application name through a connection string you can simply include “Application Name=MyAppName”. If the developer of an application doesn’t do this you get something generic like .Net SqlClient Data Provider, which just tells you that this is a connection from a .Net provider. Sadly outside of larger software companies I haven’t noticed this used all that often.

So in general the column is nice in that it gives you a general idea of what’s going on, and 95% (IMO) of the time it’s going to at least be fairly accurate. But I wouldn’t rely on it too much.

4 thoughts on “What is the program_name in sys.dm_exec_sessions?

  1. Lydia says:

    Hi Kenneth, thank you for your great explanation that is very useful. I tried to set “Application Name” in connect_args of sqlalchemy in Python but I didn’t observe any change in program_name in sys.dm_exec_sessions. Same thing for “application_name”, “program_name”, “app_name”. Do you have any idea how to solve this ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,740 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: