How to list all machines currently connected to SQL Server
Mariano Gomez
view only tracks host names for processes that have been initiated by a
domain account (and please feel free to correct me if my assumption is
wrong). I would definitely like to find a better way of listing all machines
that currently have SQL Server active connections.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
Dan Guzman
> view only tracks host names for processes that have been initiated by a
> domain account (and please feel free to correct me if my assumption is
> wrong). I would definitely like to find a better way of listing all
> machines
> that currently have SQL Server active connections.
sys.sysprocesses will include both trusted and standard SQL connections.
However, consider using sys.dm_exec_sessions since sysprocesses is
deprecated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
Mariano Gomez
Much thanks for your assistance. It does seem that sys.dm_exec_sessions does
not return a host name [host_name] for an ODBC client interface
[client_interface_name]. Is there something I am missing?
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
Geoff Schaller
What are you trying to do here? I don't think there is a good answer for
what you want simply if for no other reason than this is rapidly
becoming a disconnected world. Let me give you two examples:
1. We have one application which is web service based and whilst the
user connects to and uses sql server, it does so through pooled
connections. It even disconnects and reconnects as needed. So at any
given moment you may not see a connection but micro seconds later, one
is made, used and disconnected again. So what does your list of
connected machines tell you about this user?
2. We have another application that is terminal services based and the
users basically leave the app running but disconnect the session. SQL
Server will show this as a live connection but basically it is not in
use and can be ignored.
So if you can explain the reasoning behind your need it may be easier to
offer specific advice.
Geoff Schaller
Software Objectives
"Mariano Gomez" <Marian...@discussions.microsoft.com> wrote in
message news:44DFB138-6C4E-48AA...@microsoft.com:
Dan Guzman
> does
> not return a host name [host_name] for an ODBC client interface
> [client_interface_name]. Is there something I am missing?
If a host name is provided in the application connection string, that will
override the default host name value. But I would still expect
client_interface_name to report "ODBC" for remote application connections
using ODBC. Both are reported correctly on my test system. Are the
session_ids in question application ones?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Mariano Gomez" <Marian...@discussions.microsoft.com> wrote in message
news:A59BB673-326B-4887...@microsoft.com...
Mariano Gomez
authentication via ODBC to get access to the database objects... if I am
reading correctly though, it seems this will not be possible to achieve.
Mariano Gomez
Thanks for your reply. Our ERP system uses SQL Server authentication via
ODBC to allow users to access the information hosted in the various modules
they have access to. A question that came up is, "how can we tell what
machine did a user login from?". The more I read through Dan's and your post,
it would seem that application connections are a bit trickier to deal with
(at least from a SQL Server perspective) -- we also have web services users.
Dan was explaining that the host name for ODBC connections is retrieved by
SQL Server from the connection string, which makes little to no sense to me
-- please don't get me wrong, I am not questioning Dan's explanation -- why
SQL Server cannot keep track of what host machine initiated a specific
request. I noticed that requests are well tracked when the authentication was
issued by a Windows domain account, but not when it's issued via ODBC.
If this is how it works, I guess I will have to live with it, but I guess
being the geek I am I had to ask.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
"Geoff Schaller" wrote:
> .
>
Geoff Schaller
It isn't for SQL Server to keep track of such things. It shouldn't and
doesn't care. It is that simple. It is you who cares so you have to take
the appropriate action. And basically this is why we have applications
and application developers. T-SQL can only take you so far.
Ourselves, we no longer use ODBC - it is old and clunky. Now we use ADO
and ADO.Net from our applications and yes, we store the machine name,
user name and domain name of the user who accesses a connection. But the
application detects and stores this, not the SQL engine. That is as it
should be. As you say, what is a web service connection to the SQL
engine? There are properties in the connection string that are optional
and user identity components are among them.
Cheers,
Geoff Schaller
Software Objectives
"Mariano Gomez" <Marian...@discussions.microsoft.com> wrote in
message news:26C73952-AC0E-4F01...@microsoft.com:
Mariano Gomez
response.
"Geoff Schaller" wrote:
> .
>