Getting Connection Information

Question

I was wondering is their a way to get map the session_id's to the corresponding client machines....I tried sp_who2 and sys.dm_exec_connections to get the host names but as expected it shows the DB server name in place of  client host name.  I am referring to a web application with app and DB on different machines.

---

Cheers!!! SqlFrenzy

Answer 1

The host name should reflect the client host name by default.  However, the value can be overriden by the client connection string.  Check the string for keywords like WSID.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

You can get from the below query

select * from sys.sysprocesses where hostname in('yourhostname1','yourhostname2'..)

loginname

= 'loginname' and program_name like '%.Net%'

---

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.

Answer 3

sys.sysprocesses, sp_who2 and sys.dm_exec_connections gave similar outputs. There is one thing that I would like to mention...If a client machine A opens a web application hosted on an application server...then the hostname will always be off the application server because it is the application server which is connected to database server on behalf of the client machine....If I am correct then I think there is no way to get the host name through sql server.... 

---

Cheers!!! SqlFrenzy

Answer 4

In your description, SQL Server is connected to the web server.  That will always be true.  In addition, your connections should be pooled at the web servers, so you cannot say connection 1 is user 1.  They will get different connections based on their usage.  The only way to get what you are looking for is to look at the web server.

Answer 5

Yes, the client machine is the web server from SQL Server's perspective, not the client browser.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

In your description, SQL Server is connected to the web server.  That will always be true.  In addition, your connections should be pooled at the web servers, so you cannot say connection 1 is user 1.  They will get different connections based on their usage.  The only way to get what you are looking for is to look at the web server.

Ok....then is their a way to get the host name in this scenario.

---

Cheers!!! SqlFrenzy

Answer 7

You could get the client hostname/IP in the web application and include that info in the connection string using keywords for the work station id or applcation name.  However, that would effectively prevent connection pooling, which might be a ok if this is a small intranet application.

What is driving your requirement to do this?  Maybe there is a better solution.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 8

You could get the client hostname/IP in the web application and include that info in the connection string using keywords for the work station id or applcation name.  However, that would effectively prevent connection pooling, which might be a ok if this is a small intranet application.

What is driving your requirement to do this?  Maybe there is a better solution.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Thanks...few days back I was asked to get the query giving lock timeout....I filtered out the queries but It just came to my mind that how would I know that a query is being run by which user......I don't have any control over the application code....I think to conclude we can say that I can't get this from SQL SERVER...

---

Cheers!!! SqlFrenzy

Answer 9

SQL Server only sees connections from the host/process with the direct network connection.  It is problematic to identify the end-user identity in an n-iter topology (there count be any number of hpos in between) unless something is built into the application layer.

It is possible to pass user credentials over multiple hops using Kerberos, which would allow end-users to authenticate direcly to the database server using Windows Authentication.  The downside is that this is relatively complex to setup, requires solid knowledge of AD and delegation and doesn't scale in larger environments due to lack of connection pooling.

The fact that your were tasked with a lock timeout query leads me to believe LOCKTIMEOUT is used to avoid long-term blocking.  I don't know anything about the app but it might be possible to turn on the READ_COMMITTED_SNAPSHOT database option to avoid certain types of blocking.  See the Books Online for more information.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 10

The fact that your were tasked with a lock timeout query leads me to believe LOCKTIMEOUT is used to avoid long-term blocking.  I don't know anything about the app but it might be possible to turn on the READ_COMMITTED_SNAPSHOT database option to avoid certain types of blocking.  See the Books Online for more information.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

The problem was with a financial application. I suggested snapshot isolation levels long time back, but the product team says it is not good for a financial application...any suggestions...

---

Cheers!!! SqlFrenzy

Answer 11

Note that I suggested READ_COMMITTED_SNAPSHOT rather than SNAPSHOT_ISOLATION.  These are different beasts, although both use row versioning. 

The question is whether or not the application specifically relies on READ_COMMITTED locking behavior.  Do you have a case where you want a SELECT to wait until data modified by another session are committed instead if just returning the last committed value?  I don't think there is anything special about a financial application with regard to this behavior.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 12

OK...I don't have much knowledge about the financial domain. I suggested the snapshot isolation because of the enormous amount of select queries being time out...but the answer was that the time out is because of the improper indexes and badly written queries...so its better to concentrate on them rather than the isolation level...this may have been the cause.....but in my view snapshot isolation would have been an immediate solution...

---

Cheers!!! SqlFrenzy

Answer 13

I fully agree it is best to address the root cause.  There is a close relationship between performance and concurrency so index and query tuning may very well be the best initial course of action.  If you still have blocking problems afterward, then consider READ_COMMITTED_SNAPSHOT.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/